如何在Laravel中的大多数重复项中查询我的查询？

Question

On my website, users can post images.

Images can have tags.

There's 4 tables for this, the images table, the images_tag pivot table, the tag table, and of course the users table.

A user can have multiple images with the same tag(s).

I can pull up the tags a user has used across all his images with this query:

$userTags = Tag::whereHas('images', function($q) use($user) {
    $q->where('created_by', $user->id);
})->get();

However, I want to make it so that I can order these tags based on how frequently a user uses them. In other words, I want to order by duplicates. Is this possible?

Answer 1

To achieve this, you're going to need to join the images_tags and images tables, count the number of tags, and order by those tags.

$tags = Tag::selectRaw('tags.*, COUNT(images.id) AS total')
    ->join('images_tags', 'tags.id', '=', 'images_tags.tag_id')
    ->join('images', 'images.id', '=', 'images_tags.image_id')
    ->where('images.created_by', $user->id)
    ->groupBy('tags.id')
    ->orderBy('total', 'desc')
    ->get();

The above query will only work in MySQL if the only_full_group_by option is disabled. Otherwise, you're going to need to either rewrite this to use a sub query, or do the ordering in the returned Laravel Collection. For example:

$tags = Tag::selectRaw('tags.*, COUNT(images.id) AS total')
    ->join('images_tags', 'tags.id', '=', 'images_tags.tag_id')
    ->join('images', 'images.id', '=', 'images_tags.image_id')
    ->where('images.created_by', $user->id)
    ->groupBy('tags.id')
    ->get();

$tags = $tags->sortByDesc(function ($tag) {
    return $tag->total; 
});

If you want to add this to your user model, per your comment, create a function similar to the following:

public function getMostUsedTags($limit = 3)
{
    return Tag::selectRaw('tags.*, COUNT(images.id) AS total')
        ->join('images_tags', 'tags.id', '=', 'images_tags.tag_id')
        ->join('images', 'images.id', '=', 'images_tags.image_id')
        ->where('images.created_by', $this->id)
        ->groupBy('tags.id')
        ->orderBy('total', 'desc')
        ->limit($limit)
        ->get();   
}