CODEWITHSUNDEEP
phpmysqllaravel
lesssugar
lesssugar

Reputation: 16181

Laravel: Multiple SELECTs using UNION - as one query

I have the following query which uses UNION and priority alias column:

SELECT id, name, 4 as priority FROM `topics`
WHERE id IN(17, 18, 19, 20, 21) AND name LIKE 'ge'

UNION

SELECT id, name, 3 as priority FROM `topics`
WHERE id IN(17, 18, 19, 20, 21) AND name LIKE '%ge%'

UNION

SELECT id, name, 2 as priority FROM `topics`
WHERE name LIKE 'ge%'

UNION

SELECT id, name, 1 as priority FROM `topics`
WHERE name LIKE '%ge%'

ORDER BY priority DESC

Is it possible to "translate it" to Laravel's Query Builder, so it remains one query?

I know I could put all the SELECTs in separate variables and then chain them with ->union() but I'm afraid this will perform 4 queries first, one by one, and finally just combine the results.

Upvotes: 1

Views: 5252

Answers (3)

David Barker
David Barker

Reputation: 14620

Using union in Laravel's query builder does not execute the query. The union() method expects a query builder instance to be passed to it or an instance of Closure. This is stored in a union array within the builder instance and only when the query is executed does it combine all the component parts.

For example this will only run one query when get() is called:

DB::table('topics')
    ->whereIn('id', [17,18,19,20,21])
    ->where('name', 'LIKE', 'ge')
    ->union(DB::table('topics')
        ->whereIn('id', [17,18,19,20,21])
        ->where('name', 'LIKE', 'ge%')
    )->get();

Upvotes: 1

Santiago Mendoza Ramirez
Santiago Mendoza Ramirez

Reputation: 1657

In Laravel Documentation:

The query builder also provides a quick way to "union" two queries together:

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get(); The unionAll method is also available, and has the same method signature as union.

I think, for now, is the only way to unions in Laravel. I think the first query doesn't get the data, because don't add the ->get() chain.

Upvotes: 3

silkfire
silkfire

Reputation: 25935

Try running DB::getQueryLog() to see what the query actually was. I would like to believe Laravel doesn't execute the query until you call ->get() or ->first() upon it.

Upvotes: 0

Related Questions