How to get top 5 tags of Category from posts Laravel-CodePudding

Hi I've 3 Modeles Post, Tag, and category , post belongs to many categories as well as tags. I want to fetch the top 5 tags of the posts for the given categories but there is no direct relationship between tags and categories. Following is my eloquent relationship

// Post model
    public function categories()
    {
        return $this->belongsToMany(Category::class, 'post_categories', 'post_id', 'category_id')->withTimestamps();
    }


    public function tags()
    {
        return $this->belongsToMany(Tag::class, 'post_tags', 'post_id', 'tag_id')->withTimestamps();
    }

// Category Model
    public function posts()
    {
        return $this->belongsToMany(Post::class, 'post_categories', 'category_id', 'post_id')->withTimestamps();
    }

// Tag Model
    public function posts()
    {
        return $this->belongsToMany(Post::class, 'post_tags', 'tag_id', 'post_id')->withTimestamps();
    }

I have category $category = Category::find($id) for this $category I'm trying to get top 5 tags based on the posts related to this category

Please guide, Thanks in advance

CodePudding user response：

$tags = Tag::whereHas('posts' , function (Builder $query) {
    $query->whereHas('categories', function ($query,$categoryId) {
        $query->where('id', $categoryId);
    });
)})
->latest()
->limit(5)
->get();

CodePudding user response：

Alright, I need up using joins

$tags = DB::table('tags')
                    ->select('tags.id', DB::raw('COUNT(tags.id) as tags_count'), DB::raw('FIRST(tag.name)'))
                    ->leftJoin('post_tags', 'tags.id', '=', 'post_tags.tag_id')
                    ->leftJoin('posts', 'posts.id', '=', 'post_tags.post_id')
                    ->leftJoin('post_categories', 'posts.id', '=', 'post_categories.post_id')
                    ->leftJoin('categories', 'categories.id', '=', ''post_categories.category_id')
                    ->where('categories,id', '=', $categoryId)
                    ->groupBy('tags.id')
                    ->orderBy('tags_count', 'DESC')
                    ->limit(5)
                    ->get();