douya2006
2017-09-25 22:53
浏览 100
已采纳

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

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?

图片转代码服务由CSDN问答提供 功能建议

在我的网站上,用户可以发布图像。

图像可以有标签 。

这里有4个表, images 表, images_tag 数据透视表,标记表 ,当然还有用户表。

用户可以拥有多个具有相同标签的图像。

我可以使用此查询提取用户在其所有图像中使用的标记:

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

但是,我想这样做,以便我可以根据用户使用它们的频率来订购这些标签。 换句话说,我想按重复顺序排序。 这可能吗?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dpgua04022 2017-09-25 23:16
    已采纳

    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();   
    }
    
    点赞 打赏 评论

相关推荐 更多相似问题