There are 3 different database tables articles
, reviews
, posts
and all of them have these columns: 'id', 'title', 'user_id', 'created_at', 'body'
.
I am using Laravel 5.6 and yajra/laravel-datatables package, so I need to "union" these three tables and put that into jQuery DataTables.
To do that I'm using Laravel's union
query builder method:
$fields = [
'id',
'title',
'user_id',
'created_at',
'body'
];
$articles = DB::table('articles')->select($fields);
$reviews = DB::table('reviews')->select($fields);
$posts = DB::table('posts')->select($fields);
$union = $articles->union($reviews)->union($posts)->get();
dd($union);
... and this works fine, the result looks like this:
+----+-------------+---------+------------+------+
| id | title | user_id | created_at | body |
+----+-------------+---------+------------+------+
| 1 | Some title | 1 | ... | ... |
| 1 | Lorem ipsum | 2 | ... | ... |
| 1 | Test | 1 | ... | ... |
+----+-------------+---------+------------+------+
The problem is that I need to know from which table is each record (row). Is it possible to add a custom column (for example "source") which would contain the DB table name? (using Query Builder)
+----+-------------+---------+------------+------+----------+
| id | title | user_id | created_at | body | source |
+----+-------------+---------+------------+------+----------+
| 1 | Some title | 1 | ... | ... | articles |
| 1 | Lorem ipsum | 2 | ... | ... | reviews |
| 1 | Test | 1 | ... | ... | posts |
+----+-------------+---------+------------+------+----------+
Add DB::raw
custom field to your fields
array within select
like:
$articles = DB::table('articles')->select(array_merge($fields, [DB::raw('"articles" as source')]));
$reviews = DB::table('reviews')->select(array_merge($fields, [DB::raw('"reviews" as source')]));
$posts = DB::table('posts')->select(array_merge($fields, [DB::raw('"posts" as source')]));
this should add source
field to your result set