Server-side processing multi-tables (not join query)

AlexandreDelachapelle

Hi everybody,

I have a question and I didn't find the answer on the forum. Thanks in advance for your help !

I have 20 tables which have exactly the same columns (not the same data inside, but the columns). All tables have 10 columns : id, first_name, last_name, birthday_date, etc...
I know this is really strange and one big table would have been better, but I don't write the code at the beginning of the project and I can't modify it.
So, I have 20 tables which have the same column names and same type of data inside, and my question is how can I write a query to display all the datas in one big datable ?

Thanks !

ignignokt

You could easily put all the data into one table but if more data are always being added then it may be best to just leave it like that. Anyway, you'd want to use a union like so:

(SELECT
    first_name,
    last_name,
    birthday_date
FROM
    table1
)
UNION
(SELECT
    first_name,
    last_name,
    birthday_date
FROM
    table2
)
UNION
(SELECT
    first_name,
    last_name,
    birthday_date
FROM
    table3
)
UNION
(SELECT
    first_name,
    last_name,
    birthday_date
FROM
    table4
)

AlexandreDelachapelle

Yes of course !!!
I forgot the UNION !
Thank you so much !

I also replace the SQL_CALC_FOUND_ROWS ".implode(", ", self::pluck($columns, 'db'))." by a * and it works.
Now I will see what is exactly the SQL_CALC_FOUND_ROWS ".implode(", ", self::pluck($columns, 'db'))." to be sure I can replace it by a *

ignignokt

The select * should work fine, but I think the $columns variable is used for more than the initial load... perhaps sorting the column and the search/filter input. You can always change how those work. I made modifications to the server-side as well if you want to take a look.

You shouldn't have an issue with this particular query if you just used the regular $columns variable example though since you have no duplicate column names in the query.