stack stack - 1 month ago 10
SQL Question

How can I make a virtual column?

Here is my query:

$first = DB::table('news')
->select(['id', 'title', 'description', 'imgPath'])
->where(function($query) use ($q) {
$query->where('title', 'like', "$q%")
->orWhere('description', 'like', "$q%");
});

$result = DB::table('productions')
->select(['id', 'title', 'description', 'imgPath'])
->where(function($query) use ($q) {
$query->where('title', 'like', "$q%")
->orWhere('description', 'like', "$q%");
})
->unionAll($first)
->get();


As you see I've used
union all
which merges the results of those two different queries. Ok well, now I need to know, each row (of the result table) belongs to which table. Because of that I need to add one more column to the
select
part and set it a default value. Then use that value to detect row's table.

In pure SQL it can be something like this:

SELECT 'news' as tableName, col1, col2 FROM news WHERE ...
UNION ALL
SELECT 'productions' as tableName, col1, col2 FROM productions WHERE ...


Then in PHP:

if ( $result['tableName'] == 'news' ) {
// this row belongs to "news" table
} elseif( $result['tableName'] == 'productions' ) {
// this row belongs to "productions" table
}


How can I do that in Laravel?

Answer

You could use the selectRaw() method instead of select():

$first = DB::table('news')
    ->selectRaw('"news" as tableName, id, title, description, imgPath')
    ->where(function($query) use ($q) {
        $query->where('title', 'like', "$q%")
            ->orWhere('description', 'like', "$q%");
    });

$result = DB::table('productions')
    ->selectRaw('"productions" as tableName, id, title, description, imgPath')
    ->where(function($query) use ($q) {
        $query->where('title', 'like', "$q%")
            ->orWhere('description', 'like', "$q%");
    })
    ->unionAll($first)
    ->get();