CODEWITHSUNDEEP
laravelpostgresqleloquentcommon-table-expressionquery-builder
Aleksey Oshmarin
Aleksey Oshmarin

Reputation: 53

How to use WITH clause in Laravel Query Builder

I have SQL query (see example). But I can't find a way how I can write it in Query Builder. Do you have any ideas how is it possible?

WITH main AS (
    SELECT id FROM table1
)
SELECT * FROM table2
WHERE 
  table2.id IN (SELECT * FROM main)

I want to get format like:

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

but for WITH

Upvotes: 5

Views: 7010

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Laravel has no native support for common table expressions.

I've created a package for it: https://github.com/staudenmeir/laravel-cte

You can use it like this:

$query = DB::table('table1')->select('id');

$result = DB::table('table2')
    ->withExpression('main', $query)
    ->whereIn('table2.id', DB::table('main')->select('id'))
    ->get();

Upvotes: 6

Jeemusu
Jeemusu

Reputation: 10533

Query builder has to be compatible with multiple database engines (mysql, postgresql, sql lite, sql server) and as such only offers support for common functionality.

Assuming your query returns data, you may be able to use the DB:select() method to execute a raw query.

$data = DB::select('WITH main AS (SELECT id FROM table1), SELECT * FROM table2 WHERE table2.id IN (SELECT * FROM main)');

The DB:select method also accepts a second parameter for using named bindings.

Alternatively there are packages available such as laravel-cte that will add the functionality to Eloquent/ Query Builder.

Upvotes: 1

Related Questions