CODEWITHSUNDEEP
phpmysqllaravelgroup-bysql-order-by
domiQ
domiQ

Reputation: 51

Laravel / Mysql groupby ordering

I have the command 

 SELECT * FROM 
    (SELECT * FROM episodes ORDER BY created_at DESC) and t1 
 GROUP BY serial_id 
 ORDER BY created_at DESC

which I will return series where the last episode added by created_at. But the problem is that when I had it on hosting everything worked but now I put it on the VPS server and there I'm always lists the first episode. If i add desc after group by serial_id it's work but it is not possible on laravel (-> groupBy ('serial_id'))

Shared hosting:

+---------+-------------+--------+
| id      | serial_id   | part   |
+---------+-------------+--------+
|    8124 | 12          | s02e10 |
|     362 | 8           | s09e12 |
|    4673 | 9           | s01e12 |
|     871 | 4           | s03e24 |
+---------+-------------+--------+

My VPS:

+---------+-------------+--------+
| id      | serial_id   | part   |
+---------+-------------+--------+
|    8124 | 12          | s01e01 |
|     362 | 8           | s01e01 |
|    4673 | 9           | s01e01 |
|     871 | 4           | s01e01 |
+---------+-------------+--------+

Thank you in advance for your help

Upvotes: 1

Views: 118

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

Seems you have a lot of errors in your query.

missing a tablename between FROM and ORDER BY

SELECT * FROM (SELECT * FROM 
--                         ^^ where is the tablename ?

ORDER BY episodes created_at DESC) and t1 GROUP BY ORDER BY serial_id created_at DESC
--               ^^ missing comma,  ^^ this 'and' seems in the wrong place and you havent't column for group by and in last another missing comma

try something like this:

SELECT * FROM ( SELECT * 
            FROM  my_table  ORDER BY episodes,  created_at DESC)  t1 
GROUP BY my_column_for_group_by  
ORDER BY serial_id, created_at DESC

In laravel you could assign the order direction using:

 ->orderBy('serial_id')
 ->orderBy('created_at',  'DESC')

Upvotes: 1

Manish
Manish

Reputation: 3633

Probably you will have to use RAW in this case: groupBy(DB::raw('serial_id desc'))

like below

DB::table( DB::raw("(SELECT * FROM episodes ORDER BY created_at DESC) and t1") )->groupBy(DB::raw('serial_id desc'))->orderBy('created_at', 'desc')->get();

Upvotes: 0

Related Questions