Question 15514 in Laravel Question posted in
The official Laravel Documentation can be found here.

Generate "WITH AS" via eloquent using an inner query builder – Laravel

How can I generate the equivalent of this (postgresql) query in eloquent

WITH result as (
select *, order_id as alias_id from orders
)
SELECT *
FROM result;

I should be able to pass the inner query as eloquent builder query.

Tags:

2

Answers


  1. 0

    As stated in the comment, Laravel does not support WITH clauses (common table expressions) in its query builder. You can however, pass it in raw.

    $query = 'WITH results AS (
    SELECT *, order_id AS alias_id
FROM orders
)
SELECT *
FROM results';

$results = DB::select($query);

    I mentioned a package in the comment. One I’ve found that works well enough is staudenmeir/laravel-cte.
    Depending on your laravel version, you might need to install a specific version with composer.

    With that package installed, you can formulate your query like this so it looks the most as the original SQL:

    $results = DB::query()
    ->withExpression('results', function ($query) { // WITH results AS (
        $query->select('*', 'order_id AS alias_id') //   SELECT *, order_id AS alias_id
            ->from('orders');                       //   FROM orders
    })                                              // )
    ->select('*')                                   // SELECT *
    ->from('results')                               // FROM results
    ->get();
    Login or Signup to reply.
  2. 0

    You do not need the CTE (with expression). CTEs are an extension to sub-query concept. Initially so that that it could be reused instead of written for each use. But since you are using it just once a sub-query would be just fine. Your query can be rewritten as:

    select result.*
  from ( select *, order_id as alias_id 
           from orders
       ) result;

    From your description I assume sub-query above is the inner query as eloquent builder query. Otherwise it reduces to select *, order_id as alias_id from orders.
    I do not know Laravel/Eloquent Query Builder but assume it should easily handle a simple sub-query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search