How to Union/Merge three database tables with exact schema but different data while taking care of eloquent relationships?

Question

In my Laravel application, I have three database tables with exact same structure/schema and then there is a 'users' table containing all users.

All three tables have the following structure...

table1
id    user_id    description    updated_at   created_at
1     1             This is nice   xxxxxxxxxx     xxxxxxxxx
2     2             yes right it is   xxxxxxxxxx     xxxxxxxxx

table2
id    user_id    description    updated_at   created_at
1     3             Another text   xxxxxxxxxx     xxxxxxxxx
2     4             And yet more   xxxxxxxxxx     xxxxxxxxx

table3
id    user_id    description    updated_at   created_at
1     5              More nice   xxxxxxxxxx     xxxxxxxxx
2     6             okay done   xxxxxxxxxx     xxxxxxxxx

Now I really want to keep data in these separate tables. However, there is one odd times where I would need to show all these entries from these three tables in a same view, preferably orderedBy created_at field.

I used the following code to union these tables:

   $table2 = DB::table('table2');
   $table3 = DB::table('table3');
   $query = DB::table('table1')
        ->union($table1)
        ->union($table3)
        ->get();

The problem that arises is that the eloquent relationship doesn't work and this statement in Blade breaks. {{$comment->user->name}}.

I just want to be able to union/merge all these three tables and preferably be able to have the relationships or find some other way so that I could get the name of the user who owns a particular entry in the union/merged result. And also have the union/merged result orderedBy created_at column.

Any help would be greatly appreciated.

Thanks

Answer 1

Concatenate

(here's how to do it your way)

To it in the collections. Here I'm eager loading user, but you don't have to do that. It will drastically improve performance though. Note, this is 3 different queries each with a join being executed because of your design choice).

$table1 = Table1::with('user')->all();
$table2 = Table2::with('user')->all();
$table3 = Table3::with('user')->all();
$tables = $table1->merge($table2)->merge($table3);
$tables = $tables->sortBy('create_at');

Database and OOP Design

(here's how you should do it)

Why do you want them in seperate tables? It's generally considered by most to bad design to not normalize. If two objects have the same fields/members, they are the same KIND of object.

If objects have the same fields, they should be the same KIND of object, and you should add a flag for type of that object to get. So instead of having three tables with

• id
• user_id
• description
• updated_at
• created_at

You'd have one table with the fields:

• id
• user_id
• description
• type
• updated_at
• created_at

Making sure to put an index on type, so there is no performance hit to sticking them all in the same table. Then to get them all sorted by created_at (note one query, one join):

$tables = Table::->orderBy('created_at')->with('user')->get();

To get one broken out:

$table1 = Table::where('type','=',1)->with('user')->get();
$table2 = Table::where('type','=',2)->with('user')->get();
$table3 = Table::where('type','=',3)->with('user')->get();

To get them all, broken out:

$tables = Table::->orderBy('created_at')->with('user')->get()->groupBy('type');

If one (or more) of the "types" of objects has new fields, then you begin down the road of inheritance.