orderBy attributes of another table in Laravel

2
votes

On my website I allow users to upload images/like images/favorite images/etc.

Therefore, I've got a table for images, likes, favorites, etc.

I can get and display these images just fine, and sort them as I like

$images = Images::orderBy('id', 'desc')->Paginate(50);

I can also display how much likes/favorites an image has.

$favCount = Favorite::where('image_id', $image->id)->count();

However, what would I do to sort images by, say, how many favorites it they have? I have a favorite model and an image model but I'm not sure how I would go about it.

EDIT:

Current query for the answer given:

$images = Images::join('favorites', 'favorites.image_id', '=', 'images.id')
->select('images.link', DB::raw('count(favorites.id) as favs'))
->orderBy('favs', 'desc')
->groupBy('images.link')
->take(10)
->get();

And the error is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'images' in 'field list' (SQL: select images, count(favorites.id) as favs from images inner join favorites on favorites.image_id = images.id group by images.link order by favs desc limit 10)

2
phplaravel
Did you try it with DB as I did? I think the error comes from Images::raw(). Try replacing Images::raw by DB::raw(). Or do all the query with DB as I did. And see the results. Please. – EddyTheDove
Ah, you were right. Sorry, this is my first time using raw expressions. Still got an error, though. The new query and error will be in the main post. Thanks for helping me out, though! – Felix Maxime
In your table images if you have a property link or name. Try select('link', DB::raw('count(favorites.id) as favs')). It seems you don't have property images in your table images. Can you try it all with DB as I did and see if it works ? – EddyTheDove
Ok I tried switching out images.link with images.title, and I'm able to print out a list of image titles with {{ $image->title }} in my foreach loop in my blade template, however I can't print any other attribute, such as {{ $image->description }}. Ideally I'd like to be able to get all attributes of the table. – Felix Maxime

2 Answers

3
votes

You can user query builder like this:

$images = DB::table('images')
->join('favorites', 'favorites.image_id', '=', 'images.id')
->select('images.link', DB::raw('count(favourites.id) as favs'))
->orderBy('favs', 'desc') //order in descending order
->groupBy('images.link')
->take(10) //limit the images to Top 10 favorite images.
->get();

The same could be accomplished with Eloquent.

2
votes

Your query should be.

$images = Images::join('favorites', 'favorites.image_id', '=', 'images.id')
->orderBy('favs', 'desc')
->groupBy('images.link')
->take(10)
->get(['images.link', DB::raw('count(favorites.id) as favs')]);