10

Please I am trying to run a query that looks like this in raw sql

SELECT COUNT(cntr) count, address,
description FROM resti GROUP BY cntr = HAVING count > 1

in laravel.

I have tried this

 DB::table("resti")
                 ->select(DB::raw("COUNT(cntr) count, address, description"))
                 ->groupBy("cntr")
                 ->havingRaw("count > 1")
                 ->get();

But it gives of some aggregate error.

user2094178
  • 9,204
  • 10
  • 41
  • 70
Cozzbie
  • 1,014
  • 2
  • 12
  • 26
  • 3
    Try `toSql()` to see the final query. – Cheery Nov 05 '14 at 00:38
  • Thanks. Its actually outputting the correct sql. Not sure where the issue might be from. But thanks alot. – Cozzbie Nov 05 '14 at 00:45
  • What error message do you have? And where exactly? – Cheery Nov 05 '14 at 00:47
  • I added a count to the end of the query builder after removing the get() i.e I added ->count() and it prints **select count(*) as aggregate from `resti` group by `cntr` having count > 1** which throws an unknown column error. – Cozzbie Nov 05 '14 at 00:57
  • 1
    of course, `count > 1` is unknown column. – Cheery Nov 05 '14 at 00:58
  • Exactly, thats because the same query which outputs properly with ->get() is somehow transformed when replaced with ->count() – Cozzbie Nov 05 '14 at 01:00
  • This code is OK. Show errors you get or describe what is not working as expected. – Jarek Tkaczyk Nov 05 '14 at 08:34
  • The code is ok. Apparently PHPMYADMIN was outputting a different result from Laravel. That was what led to me believe that there was an issue with sql query output of Laravel, but alas, that wasnt the case. – Cozzbie Nov 05 '14 at 13:47
  • Shouldn't it be `SELECT COUNT(cntr) AS count, ...` ?? – bgallagh3r Nov 05 '14 at 01:00

1 Answers1

19

Your SQL query should be like this

SELECT COUNT(cntr) count, address, description 
FROM resti 
GROUP BY cntr  
HAVING COUNT(cntr) > 1

In Laravel your code should be like this

DB::table("resti")
->select(DB::raw("COUNT(cntr) count, address, description"))
->groupBy("cntr")
->havingRaw("COUNT(cntr) > 1")
->get();
Belal mazlom
  • 1,790
  • 20
  • 25