2

I'm creating a rest api with Lumen. I have pictures & picture_likes tables & models and I am trying to get most liked pictures.

My Pictures table looks like..

  photo_id | path | owner_id | created_at

My PictureLikes table looks like...

  id  | user_id who liked 
   0          1
   1          2
   2          4
   3          3
   4          5
   etc...

My first question is how to get most liked pictures. I think I can achieve most liked photo by grouping similar photo_ids and ordering them by their counts. However, I couldn't figure out how to order by count.

And then, I can go even further and get most liked photos of last 7 days (which will move relative to today). My second question is, how can I filter the most liked pictures, only to display most liked photos of last X days?

Update: I succeed on my first question with this: 

  $photos = Photo::all(); // returns all photos
  $photoLikes = PhotoLikes::all(); // returns all photoLikes

  $photoLikes = PhotoLike::select('photo_id', \DB::raw('count(*) as total'))
                                    ->groupBy('photo_id')
                                    ->orderBy('total', 'desc')
                                    ->get()->toArray();

The result looks:

array:10 [
  0 => array:2 [
    "photo_id" => 1
    "total" => 5
  ]
  1 => array:2 [
    "photo_id" => 10
    "total" => 5
  ]
  ..etc

However, how can I use this variable to get the photos from just last X days?

senty
  • 12,385
  • 28
  • 130
  • 260
  • Have you attempted anything? Could you describe what you have attempted so far? – Ross Dec 03 '15 at 03:04
  • @Ross I updated the code. I succeed on my first question, but got lost in second question... – senty Dec 03 '15 at 03:52
  • To get the photos from the last X days, wouldn't you just add a where() clause to your query to get the items where the created_at timestamp is newer than X days ago? – Brynn Bateman Dec 03 '15 at 04:04
  • Yes but, how can I say X days for it to change dynamically? So e.g 3 days always get last 3 days – senty Dec 03 '15 at 04:08
  • Also, I used `->get()` so $photoLikes is a collection. What is the best approach to use (as I think for loop is not a good option), to be able to use $photoLikes collection to get actual videos (and filtered by last X days)? – senty Dec 03 '15 at 04:23
  • Laravel uses Carbon, so you can use `Carbon::now()->subDays(3)` and compare that to the created_at attribute, which also uses Carbon by default. – Brynn Bateman Dec 03 '15 at 04:32
  • I am not sure how videos are related to your $photoLikes. If they have some sort of Eloquent relationship, you can use [eager loading](http://laravel.com/docs/5.1/eloquent-relationships#eager-loading) in your queries to include them when querying photos/photoLikes – Brynn Bateman Dec 03 '15 at 04:34
  • I think this thread can help you : http://stackoverflow.com/questions/24824624/laravel-q-where-between-dates – Md Aman Ullah Dec 03 '15 at 04:41

1 Answers1

2

Before I begin, I find it a little bit weird that your table name for your videos is called pictures. Haha, also, you called your model both Picture and Photo so between pictures, videos, and photos, it's a bit confusing as to what you actually named things.

Anyway, in order to achieve what you want, you can use joins. For example:

$pictures = Picture::selectRaw('pictures.*, count(picture_likes.photo_id) as total_likes')
    ->join('picture_likes', 'picture_likes.photo_id', '=', 'pictures.photo_id')
    ->groupBy('pictures.photo_id')
    ->orderBy('total_likes', 'DESC')
    ->get();

This will join the pictures and picture_likes tables. It'll group by the photo_id. It selects all of the columns in the pictures table and counts the instances of photo_id from the picture_likes table. It aliases that count as total_likes. That way, you can order by total_likes.

With this, you can loop through it and do whatever you want. For example, this will echo out photo_id and total_likes.

foreach ($pictures as $picture)
{
    echo $picture->photo_id . ': ' . $picture->total_likes . '<br>';
}

Now, there's a problem with getting the most liked photos in the past 7 days because your picture_likes table does not have a created_at column indicating when the picture was liked. Instead, you can only get the most liked photos that were created in the past 7 days. The two mean fundamentally different things. The first is impossible with the current table structure. The second is doable. Just add a whereDate method to the example above like this:

$pictures = Picture::selectRaw('pictures.*, count(picture_likes.photo_id) as total_likes')
    ->whereDate('pictures.created_at', '>=', Carbon::now()->subWeek())
    ->leftJoin('picture_likes', 'picture_likes.photo_id', '=', 'pictures.photo_id')
    ->groupBy('pictures.photo_id')
    ->orderBy('total_likes', 'DESC')
    ->get();

Once again, this will get the most liked pictures that were created within the past 7 days. I used Carbon to get the current date/time and subtracted a week.

Thomas Kim
  • 15,326
  • 2
  • 52
  • 42
  • Wow! Ace man! Thanks a lot!! What I wanted was the one you showed (not most fav. photo of the week) :) Cheers! – senty Dec 03 '15 at 04:53
  • I tried your example, but the collection I am receiving is empty :/ What am I doing wrong? This line is the problem `->groupBy('photos.id')` – senty Dec 03 '15 at 05:06
  • And commenting out `->groupBy('pictures.photo_id')` and `->orderBy('total_likes', 'DESC')` lines, and adding `->toArray()` after `->get()`, I am receiving `array:1 [ 0 => array:7 [ "id" => null "owner_id" => null "path" => null "created_at" => null "updated_at" => null "total_likes" => 0 ] ]` – senty Dec 03 '15 at 05:11
  • @senty There is no `photos.id`. In my example, I put `pictures.photo_id`. – Thomas Kim Dec 03 '15 at 05:14
  • I mistakenly typed `photos.id` in comment. The syntax is okay, I fixed it following your approach. I also noticed that I mistakenly wrote photo_id in the OP. I use `id` instead of `picture_id`. Just to confirm, it looks: `$pictures = Picture::selectRaw('pictures.*, count(picture_likes.picture_id) as total_likes') ->whereDate('pictures.created_at', '>=', Carbon::now()->subWeek()) ->leftJoin('picture_likes', 'picture_likes.picture_id', '=', 'picture.id') ->groupBy('picture.id') ->orderBy('total_likes', 'DESC') ->get()->toArray();` What do you think? – senty Dec 03 '15 at 05:21
  • I found the problem.. This line is the one emptying everything. `->whereDate('pictures.created_at', '>=', Carbon::now()->subWeek())`. When I use everything else except this line, it returns data... – senty Dec 03 '15 at 05:26
  • Did you alias the Carbon class at the top? Add `use Carbon\Carbon;` to your file at the very top if you didn't. :) Also, it seems like in your question, `picture_likes` table no longer has a `photo_id`? Kind of confusing cause your table structure keeps changing... – Thomas Kim Dec 03 '15 at 05:27
  • Yes, I did. It throws error unless I add it :) Sorry that I made things complicated above (haven't slept for ages). I'm happy that we're on the same page now. What do you think may be the problem with this line? – senty Dec 03 '15 at 05:30
  • One issue I see is that in the code you posted, you are putting `picture.id`. Shouldn't that be plural like this `pictures.id`? If the table name is singular, then you should adjust the `selectRaw` statement and the `whereDate` statement to reflect that. – Thomas Kim Dec 03 '15 at 05:33
  • It's `pictures` table and `picture_likes` table. – senty Dec 03 '15 at 05:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96848/discussion-between-thomas-kim-and-senty). – Thomas Kim Dec 03 '15 at 05:35