Query DB Laravel-CodePudding

I'm trying to count the post count of each tag, then orderBy by the post count. But there was a problem when counting the number of posts: tags with no posts were not displayed. I want if any tag has no post, it will still be printed. What should I do.

    $query = DB::table('tags');
    $query -> join('users', 'users.id', '=', 'tags.user_creative_id' );
    $query -> join('tag_post', 'tag_post.tag_id', '=', 'tags.id' )
           ->select('tags.*', 
                     DB::raw('(SELECT count(*) FROM tag_post WHERE tags.id = tag_post.tag_id )  as count_posts')    
            )
           ->orderBy('count_posts','desc')
           ->groupBy('tags.id');

CodePudding user response：

you can use LeftJoin to show all rows from left table that doing join:

 $query = DB::table('tags');
    $query -> join('users', 'users.id', '=', 'tags.user_creative_id' );
    $query -> leftJoin('tag_post', 'tag_post.tag_id', '=', 'tags.id' )
           ->select('tags.*', 
                     DB::raw('(SELECT count(*) FROM tag_post WHERE tags.id = tag_post.tag_id )  as count_posts')    
            )
           ->orderBy('count_posts','desc')
           ->groupBy('tags.id');