Laravel Query Builder: adding another join causes the wrong count result-CodePudding

I have a query which returns the right results when I don't use a specific join. But when I add another join, the count shows completely different results. I don't understand why this is happening.

This is my query:

 $query = DB::table('contentable_tips')
            ->join('content', 'contentable_tips.content_id', '=', 'content.id')
            ->join('users', 'content.author_id', '=', 'users.id')
            ->join('contentable_subcategories', 'content.parent_id', '=', 'contentable_subcategories.content_id')
            ->join('content as subcategory', 'contentable_subcategories.content_id', '=', 'subcategory.id')
            ->join('content as category', 'subcategory.parent_id', '=', 'category.id')
            ->join('content as comment_content', 'contentable_tips.content_id', '=', 'comment_content.parent_id')
            ->join('contentable_comments', 'comment_content.id', '=', 'contentable_comments.content_id')
            ->groupBy('contentable_tips.content_id');

 $query = $query->select(
            'category.slug as parent_slug',
            'content.id',
            'contentable_tips.title',
            'contentable_tips.name',
            'contentable_tips.likes_count',
            'contentable_tips.content',
            'contentable_subcategories.content_id',
            'contentable_subcategories.title as subject',
            'content.author_id',
            'users.public as author_public',
            'users.name as author_name',
            DB::raw('count(contentable_comments.id) as comment_count'),
        )
            ->groupBy('contentable_subcategories.id')
            ->orderBy('likes_count', 'desc')
            ->limit($limit)
            ->get();

But when I add this join:

            ->join('content_likes', function ($join) {
                $join->on('content_likes.content_id', '=', 'contentable_tips.content_id')
                ->where('content_likes.created_at', '>=', now()->subMonth());
            })

the comment count changes for example from 20 to 800. This obviously isn't correct, I also checked this in my database. I want to add the likes_count based upon the likes given in the past month. I also removed the select on contentable_tips.likes_count as it is not needed anymore when I get the like count from the content_likes table. That is why I added this specific join.

I already tried joining the content table before the content_likes but that didn't work. I just don't understand why adding all these joins wasn't a problem, but when I add this join it suddenly shows wrong results.

If any more information is needed, I will provide it.

CodePudding user response：

I managed to find a solution. I added a raw query where I count the likes based on the created_at date:

->leftJoin(DB::raw('(SELECT content_id, count(id) as likes FROM content_likes WHERE created_at >= now() - interval 1 month group by content_id) as content_likes'),'content_likes.content_id', '=', 'contentable_tips.content_id')