Postgres function WITH in Laravel QueryBuilder

Question

please help me.

I've got the raw SQL

WITH collections AS (
    SELECT 
        ...
    FROM
        ...
)
SELECT
    ...
FROM collections c1
LEFT JOIN collections c2
    ...

How I can write it using Laravel QueryBuilder?

Laravel QueryBuilder does not support WITH syntax. You have 2 options: rewrite query without 'WITH' or use DB::select() which accepts raw sql queries. — pumbo
– pumbo, Commented Nov 15, 2016 at 6:07

Rav · Accepted Answer · 2019-06-06 06:49:20Z

1

You may use the RAW method of Laravel's Query Builder (https://laravel.com/docs/5.3/queries#raw-expressions). Just be careful with potential SQL injection loopholes.

edited Jun 6, 2019 at 6:49

answered Nov 15, 2016 at 5:42

Rav

1,3833 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ilya Poluhin Over a year ago

Now I'm doing this: $queryResult = DB::select(" with collections AS ( SELECT .....))";

Rav Over a year ago

Can you post the entire query statement that you made?

Ilya Poluhin Over a year ago

$queryResult = DB::select(" with collections AS (   SELECT tcm.shop_id, ...   FROM template_collection_menu AS tcm   LEFT JOIN template_collection AS tc ON tc.id =   tcm.template_collection_id   WHERE tcm.shop_id IN (:mainShopId, :currentShopId)   ORDER BY tcm.position ASC ) SELECT   COALESCE(c2.shop_id, c1.shop_id) as shop_id, ...   FROM collections c1   LEFT JOIN collections c2 ON c2.template_collection_id = c1.template_collection_id AND c1.shop_id = :mainShopId AND c2.shop_id = :currentShopId   WHERE c1.shop_id = :mainShopId         ", ['mainShopId' => $mainShopId,...]);

Rav Over a year ago

Have you checked the URL that I gave? You have to use DB::raw instead of DB::select.

Rav Over a year ago

@IlyaPoluhin, you can use the DB::raw like this DB::raw('SELECT * FROM table...)

Collectives™ on Stack Overflow

Postgres function WITH in Laravel QueryBuilder

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related