mercredi 21 décembre 2016

Using CTE (WITH queries) in Laravel query builder

In a project I'm using a Common Table Expression (CTE) in Postgres for a recursive expression on a table. The recursive expression figures out all child rows under a parent.

I'm using the Laravel framework with query builder to write this query. I like to avoid raw queries and looking for a way to chain the CTE in the query builder syntax, but I end up with a raw query (DB::select('raw query here')).

Is there a way that I can chain the CTE part? Like below in pseudocode:

DB::statement('WITH RECURSIVE included_parts(sub_part, part, quantity) AS (  
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
 UNION ALL
  SELECT p.sub_part, p.part, p.quantity
  FROM included_parts pr, parts p
  WHERE p.part = pr.sub_part')
->select('subpart')
->table('included_parts')
->join('bar', 'foo.id', '=', 'bar.foo_id')
->etc etc more query expressions



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire