Convert raw SQL query to Laravel DB builder query

Question

I need help converting the following raw query into a Laravel query using the DB query builder. The query must be built using the DB (facade) query builder and not Eloquent.

SELECT distinct p.client_id
  FROM people p
JOIN (SELECT client_id, COUNT(*)
  FROM people
GROUP BY client_id
HAVING count(client_id) > 1 ) b
ON p.client_id = b.client_id
ORDER BY p.client_id')

This is my solution so far:

$subQuery = DB::table('people')
            ->select('client_id', DB::raw('count(*)'))
            ->groupBy('client_id')
            ->havingRaw('count(client_id) > ?', [1]);

$mainQuery = DB::table('people')
            ->distinct('client_id')
            ->joinSub($subQuery, 'b', function ($join) {
                $join->on('people.client_id', '=', 'b.client_id');
            })
            ->orderBy('client_id')
            ->get();

You're expected to show us an attempt to solve the problem yourself before asking for help. A question like this comes across a request for code rather than a request for help. — apokryfos, Jun 29 '20 at 08:03
I'm sorry you have interpreted my comment as negative. I will rephrase it. Please show us your attempt to solve the problem in order to allow us to better help you. This question at its current form is not appropriate for SO because it's too broad. — apokryfos, Jun 29 '20 at 08:33

score 2 · Answer 1 · answered Jun 29 '20 at 09:14

I found the solution I was looking for:

$subQuery = DB::table('people')
   ->select('client_id', DB::raw('count(*)'))
   ->groupBy('client_id')
   ->havingRaw('count(client_id) > ?', [1]);

$peopleWithSameClient = DB::table('people')
   ->distinct('people.client_id')
   ->joinSub($subQuery, 'b', function ($join) {
          $join->on('people.client_id', '=', 'b.client_id');
   })
   ->orderBy('people.client_id')
   ->get();

score 1 · Answer 2 · answered Jun 29 '20 at 08:03

1

Try DB::raw() like:

DB::select(DB::raw("Your query here"));

DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder.

answered Jun 29 '20 at 08:03

Mayank Pandeyz

25,704
4
40
59

This is a very sparse answer, there is a DB::table query approach for this question, i just don't have the time to write the answer before tonight. – mrhn Jun 29 '20 at 08:06

Convert raw SQL query to Laravel DB builder query

2 Answers2