how can convert this sql statment to laravel orm?

Question

SELECT book_id, COUNT(*)
FROM book
GROUP BY book_id
ORDER BY COUNT(*) DESC

how can convert this sql statment to Laravel ORM ?

Editing :

this sql statement work in MYSQL but not int laravel

Antonio Carlos Ribeiro · Accepted Answer · 2014-03-30T00:30:32.073

0

This might do the job:

$result = Book::select(array('book_id', DB::raw('COUNT(*) as count')))
            ->groupBy('book_id')
            ->orderBy(DB::raw('COUNT(*)'), 'desc')
            ->get();

To test/debug your query, add this before it:

DB::listen(function($sql, $bindings, $time) {
    var_dump($sql);
    var_dump($bindings);
});

Or send it to log:

DB::listen(function($sql, $bindings, $time) {
    var_dump($sql);
    var_dump($bindings);
});

This code will plot the builded query into your browser page, like this:

DB::listen(function($sql, $bindings, $time) {
    Log::info($sql);
    Log::info($bindings);
});

And to look at your log:

php artisan tail

edited Mar 30 '14 at 00:30

answered Mar 30 '14 at 00:01

Antonio Carlos Ribeiro

86,191
22
213
204

can this converted to ORM ? – user1397 Mar 30 '14 at 00:04
This is Eloquent ORM. – Antonio Carlos Ribeiro Mar 30 '14 at 00:04
i try now to use this, but show me this error "Column not found: 1054 Unknown column 'COUNT(*)' " – user1397 Mar 30 '14 at 00:05
I just edited it, for Laravel 4 and added a way for you to debug it. I also tested that query here in my end using the users table and it worked fine. – Antonio Carlos Ribeiro Mar 30 '14 at 00:14
The problem is, can your database server order by `count(*)`? Did you ran that query in your database server before? This error `Column not found: 1054 Unknown column 'COUNT(*)'` looks like your databas server not understanting it as a column. – Antonio Carlos Ribeiro Mar 30 '14 at 00:17
issue in count, "Column not found: 1054 Unknown column 'count' " – user1397 Mar 30 '14 at 00:17
i run this sql statment in my mysql server and work perfect but in a=laravel show me this error ! – user1397 Mar 30 '14 at 00:18
Did you used DB::listen() to get the raw query? – Antonio Carlos Ribeiro Mar 30 '14 at 00:20
And what was the outcome? – Antonio Carlos Ribeiro Mar 30 '14 at 00:24
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count(*)' – user1397 Mar 30 '14 at 00:27
you can sir try to use count in you laravel for any table, this issue will also showed – user1397 Mar 30 '14 at 00:28
sorry for my English language – user1397 Mar 30 '14 at 00:30
DB::listen() will not give you an error, before this error you should see something else. Just edited with a way to send the raw query to the log. – Antonio Carlos Ribeiro Mar 30 '14 at 00:31
Antonio Carlos Ribeiro, can you use this code in your laravel with mysql using count ? and you will see same error – user1397 Mar 30 '14 at 00:44
This is my test code: http://puu.sh/7ONL5.png. This is the result I see in browser: http://puu.sh/7ONN4.png. No error. I'm using postgresql database. – Antonio Carlos Ribeiro Mar 30 '14 at 00:49
According to this answer: http://stackoverflow.com/questions/2283305/mysql-order-by-count. It should work for you too. But I asked many times for you to get the raw query, you MUST get the query Laravel generates for you and test in your database. This is done with DB::listen() and I have you two options to use it. Have you at least tried get the the generated query? – Antonio Carlos Ribeiro Mar 30 '14 at 00:51
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count(*)' in 'order clause' (SQL: select * from `books` group by `book_id` order by `count(*)` desc) – user1397 Mar 30 '14 at 00:58
now this work, i don't why didn't work before but now working. thanks you very mush – user1397 Mar 30 '14 at 04:45

how can convert this sql statment to laravel orm?

1 Answers1