Dynamic Tables from Filters

[cesarMtorres]

hi there, i need change the table name dynamically using date time filter, is posible with a filter change the builder query?

example.

return DateTimeFilter::make(__('report.startDate'), 'start-date-filter')
            ->filter(function (Builder $builder, string $value) {

                // catch de year and month 
                $year       = Carbon::parse($value)->format('Y');  // 2019
                $month      = Carbon::parse($value)->format('m'); // 09
                 
                 // get the name
                $tableName = "player_cashflow_history_{$year}_{$month}";

                /** table name`s at DB
                 * 
                 *  player_cashflow_history_2019_01
                 *  player_cashflow_history_2020_03
                 *  player_cashflow_history_2021_04
                 *  player_cashflow_history_2022_05
                 * 
                 */

                // change Data Base Name and table name

                if (Schema::connection('mysql_archive')->hasTable($tableName)) {

                    $this->setConnection($tableName);
                   $query = self::getConnection();

                    $query->where('');
                }
                // if not exists refresh datatable

            }); 

[lrljoe]

Hmmm, I'd need to have a tinker on this. Your best bet would be to set a variable in the filter and reuse that in a builder() method.

But, this isn't best practice, so that I can understand, what's your thinking behind the archiving on separate connections out of interest?

You could definitely do it by having separate models for the archived records, which would be cleaner too, and you could then just swap in relations more smoothly.

If you have proper indexes in place, then it's almost certainly easier to just have it in one table, and use fields to reference which year/month it belongs to.

Well structured indexes would help here more than separate tables/databases, which (no offence) is a tad backwards with the modern capabilities of database indexing