CODEWITHSUNDEEP
phppostgresqllaraveleloquentcommon-table-expression
Hamed Kamrava
Hamed Kamrava

Reputation: 12847

How to return Query Builder with a PostgreSQL CTE query

I have a query which I used PostgreSQL CTE(Common Table Expressions) feature :

WITH tmp_users AS (
  SELECT users.id, users.name, users.email, users.created_at,sum(transactions.amount) as credit
  FROM users INNER JOIN transactions 
    ON users.id = transactions.user_id 
GROUP BY users.id,users.name,users.email, users.created_at, transactions.user_id
)
SELECT * FROM tmp_users WHERE credit > 100;

I can achieve the results with this :

$result = DB::select("WITH tmp_users AS (
  SELECT users.id, users.name, users.email, users.created_at,sum(transactions.amount) as credit
  FROM users INNER JOIN transactions 
    ON users.id = transactions.user_id 
GROUP BY users.id,users.name,users.email, users.created_at, transactions.user_id
) SELECT * FROM tmp_users WHERE credit > 100");

But I need the Query Builder not only the results.

Any help would be greatly appreciated.

P.S:

Why I used postgres CTE feature?

Because in PostgreSQL if I want to do any statements on alias fields, I need to use CTE feature(Read more).

Upvotes: 1

Views: 2588

Answers (2)

Hamed Kamrava
Hamed Kamrava

Reputation: 12847

Thank to @wildplasser

$query = DB::table('users')
        ->select('*')
        ->from(DB::raw(
        '(SELECT u.id, u.name, u.created_at, sum(transactions.amount) as credit 
        FROM users as u INNER JOIN transactions as t ON u.id = t.user_id 
        GROUP BY u.id,u.name,u.created_at,t.user_id) as tmp_users'
    ));

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

SELECT * FROM 
(
  SELECT u.id, u.name, u.email, u.created_at
   ,sum(t.amount) as credit
   FROM users u
   INNER JOIN transactions t ON u.id = t.user_id 
   GROUP BY u.id, u.name, u.email , u.created_at -- , t.user_id
) tmp_users
WHERE credit > 100;

Upvotes: 1

Related Questions