phpmysqllaravelunionlaravel-query-builder

Laravel Query Builder Unions: Add 'table name' column


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    |
+----+-------------+---------+------------+------+----------+

Solution

  • 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