dynamically join multiple tables in laravel-CodePudding

So i have a table like this

as you can see, the existing tables have almost similar names and each table has the same column structure and each table only have data for one month, example air_message2021_01 only have data from January, air_message2021_02 only have data from February etc.

on my project it is required to search data by JOIN some existing tables but dynamically like this.

in the picture you can see there is aircraft id field, year, from date containt month and date field, to date containt month and date field, from time field and to time field.

because each table only has data for 1 month and the user can search for data with a time span of 6 months, I have to dynamically merge the tables because the system will not know which time range the user will be looking for.

Example

User1 search data aircraft ID BTK7369 from 2021-01-12 to 2021-04-20

it mean i have to join air_message2021_01, air_message2021_02, air_message2021_03 and air_message2021_04

User2 search data aircarft ID BTK7552 from 2021-05-27 to 2021-08-09

it mean i have to join air_message2021_05, air_message2021_06, air_message2021_07 and air_message2021_08

How to solve it?

Thanks

CodePudding user response：

Model of air_message* tables you can declare same as :

class AirMessageRead extends Model
{
    /**
     * Name base table
     *
     * @var string
     */
    protected $baseTable = 'air_message';

    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = '';

    /**
     * For Read.
     *
     * @var string
     */
    public $exists = true;

    /**
     * AirMessageRead constructor.
     *
     * @param int $time
     * @return void
     */
    public function __construct($time)
    {
        $this->table = $this->getTableName($time);
    }

    /**
     * Get table name
     *
     * @param int $time
     * @return string
     */
    public function getTableName($time)
    {
        return $this->baseTable . $time;
    }
}

On service you convert range date to array () and onion :

use Carbon\CarbonPeriod;

private function getTimeRange($start, $end)
{
    foreach (CarbonPeriod::create($start, '1 month', $end) as $month) {
        $months[] = $month->format('Y_m');
    }
    return $months;
}

private function getTablesForQuery($start, $end)
{
    $months = $this->getTimeRange($start, $end);
    $mergeTables = array_map(fn($month) => new AirMessageRead($month), $months);
    foreach ($mergeTables as $table) {
        if (isset($query)) {
            $query = $query->union($table);
        } else {
            $query = $table;
        }
    }
    return $query
}

and get onion tables :

// $query is model for you
$model = $this->getTablesForQuery('2021-01-12', '2021-04-20');

You can add function for checking exist table on Model