Using CTE (WITH queries) in Laravel query builder

Question

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 would like to avoid raw queries. I am 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 pseudo code:

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

Answer 1

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

$query = 'SELECT sub_part, part, quantity FROM parts [...]';

DB::table('included_parts')
    ->withRecursiveExpression('included_parts', $query, ['sub_part', 'part', 'quantity'])
    ->select('subpart')
    ->join('bar', 'foo.id', '=', 'bar.foo_id')
    ->[...]

You can also provide a query builder instance:

$query = DB::table('parts')
    ->where('part', 'our_product')
    ->unionAll(
        DB::query()->[...]
    )

DB::table('included_parts')
    ->withRecursiveExpression('included_parts', $query, ['sub_part', 'part', 'quantity'])
    ->select('subpart')
    ->join('bar', 'foo.id', '=', 'bar.foo_id')
    ->[...]