CODEWITHSUNDEEP
phpmysqlsqllaravel
Felix
Felix

Reputation: 2661

Laravel/SQL sort by popularity/trending

I have a website with images, and images can have tags. Many images can also have the same tag. So 2 images can have the tag "painting".

I have an images table, a tags table, and a an images_tag pivot table.

The images_tag table has the columns: id, images_id, tag_id, and created_at.

What I want is to be able to essentially to find which tags are trending, within, say, the last 72 hours.

This means I want to have a query that takes all the tags used in the past 72 hours

$trendingTags = ImagesTag::where('created_at', '>=', Carbon::now()->subHours(72))

and takes, say, the top 3 most used tags. So in this case

$trendingTags = ImagesTag::where('created_at', '>=', Carbon::now()->subHours(72))
->orderBy('tag_id most frequently used', 'desc')
->take(3);

How could I make this work?

Edit:

In response to fubar's answer, I tried the following:

Controller:

$trendingTags = Tag::selectRaw('tags.*, COUNT(image_tag.id) AS count')
->join('image_tag', 'tags.id', '=', 'image_tag.tag_id')
->where('created_at', '>=', Carbon::now()->subHours(72))
->orderBy('count', 'desc')
->take(3);

return view('home')
>with('trendingTags', $trendingTags)

This gave me the error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'commendme.image_tag' doesn't exist (SQL: select count(*) as aggregate from tags inner join image_tag on tags.id = image_tag.tag_id where created_at >= 2017-06-16 02:44:02 order by count desc limit 3)

So I tried switching the code to reflect the images_tag table (rather than image_tag, which I'm guessing is a typo on fubar's part), and I got another error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count' in 'order clause' (SQL: select count(*) as aggregate from tags inner join images_tag on tags.id = images_tag.tag_id where created_at >= 2017-06-16 02:45:15 order by count desc limit 3) (View: C:\xampp\htdocs\series\commend-me\CommendMe\resources\views\home.blade.php)

EDIT 2:

New error after trying out fubar's last edit:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'image_count' in 'order clause' (SQL: select count(*) as aggregate from tags inner join images_tag on tags.id = images_tag.tag_id where created_at >= 2017-06-16 02:55:17 group by tags.id order by image_count desc limit 3) (View: C:\xampp\htdocs\series\commend-me\CommendMe\resources\views\home.blade.php)

Upvotes: 0

Views: 1753

Answers (1)

fubar
fubar

Reputation: 17388

I'm guessing from your explanation that you actually want the Tag models themselves, rather than the pivot data.

If so, then you need to join the tags table to the image_tag table, and then count the number relationships between the images and tags tables. Once you have this value, simply order by it.

$trendingTags = Tag::selectRaw('tags.*, COUNT(images_tag.id) AS image_count')
    ->join('images_tag', 'tags.id', '=', 'images_tag.tag_id')
    ->where('images_tag.created_at', '>=', Carbon::now()->subHours(72))
    ->groupBy('tags.id')
    ->orderBy('image_count', 'desc')
    ->take(3)
    ->get();

Edit

In response to the errors posted by OP.

I noticed that the join table I referenced is actually called images_tag, not image_tag. OP, it's a Laravel standard to use singular names of the relations in alphabetical order when naming pivot tables. I have however modified my answer.

I also noticed that I had missed the important groupBy statement, which is required by SQL aggregator functions.

Finally, I have renamed count to image_count, in case MySQL considers this a reserved word.

Edit #2

I've just tested this in one of my projects, and it worked as expected.

$categories = FaqCategory::selectRaw('faq_categories.*, COUNT(faq_faq_category.id) AS faq_count')
    ->join('faq_faq_category', 'faq_categories.id', '=', 'faq_faq_category.faq_category_id')
    ->groupBy('faq_categories.id')
    ->orderBy('faq_count', 'desc')
    ->take(3)
    ->get();

Upvotes: 4

Related Questions