Saturday, March 4, 2017

Equivalent to a qualify statement in mysql/laravel?

I have a database that looks like this:

ID |   facebook_id  | likes | created_at
--------------------------------------------------
 1 |      x         | 100   | 2017-01-01 12:10:15
 2 |      x         | 110   | 2017-01-02 12:10:15
 3 |      y         | 115   | 2017-01-01 12:10:15
 4 |      y         | 120   | 2017-01-02 12:10:15
 5 |      z         | 200   | 2017-01-01 12:10:15
 6 |      z         | 201   | 2017-01-01 12:10:15
 7 |      z         | 200   | 2017-02-02 12:10:15
 8 |      z         | 205   | 2017-02-03 12:10:15
 9 |      z         | 250   | 2017-03-04 12:10:15

I want to get the latest record for each of the unique facebook_id's in the table then order it by likes in descending order. I am using laravel 5.4 and mysql for the database. In teradata I would do something like

QUALIFY ROW_NUMBER() OVER(PARTITION BY facebook_id ORDER BY created_at DESC) = 1

to order the results by facebook_id and get the latest row by unique facebook_id. But I'm not sure how to do this in laravel/mysql.

I have tried

Database::select('facebook_id', 'likes', 'created_at')
          ->orderBy('likes', 'desc')
          ->distinct('facebook_id')
          ->latest('created_at')
          ->limit(20)
          ->get();

But it just orders it by likes and doesnt get me unique facebook_id's

My results are:

Top Profile ID: z Likes:250
Top Profile ID: z Likes:205
Top Profile ID: z Likes:201
Top Profile ID: z Likes:200

What I want to get is:

Top Profile ID: z Likes:250
Top Profile ID: y Likes:120
Top Profile ID: x Likes:110



via kevinabraham

Advertisement