I tried doing this but it didn't work

 $ideas=IdeaUser::join('ideas as idea', 'idea.fk_user_id', '=', 'idea_users.user_id','comments.fk_idea_id','=','ideas.idea_id')->orderBy('ideaid','DESC')
        ->count('comments.fk_idea_id')
        ->groupBy(['ideas.idea_id'])
        ->get(['idea.idea_id as ideaid','idea.idea','idea_users.first_name','idea_users.last_name']);

Gave me a error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.fk_idea_id join ideas as idea on idea.fk_user_id = ? group by ideaid ' at line 1 (SQL: select idea.idea_id as ideaid, idea.idea, idea_users.first_name, idea_users.last_name from idea_users comments.fk_idea_id join ideas as idea on idea.fk_user_id = idea_users.user_id group by ideaid order by ideaid desc)

Here is the raw sql. But trying to do it in Laravel so it will output JSON

SELECT idea_users.first_name, idea_users.last_name, ideas.idea, ideas.cost, IFNULL(COUNT(comments.fk_idea_id), 0) as cnt FROM ideas INNER JOIN idea_users ON ideas.fk_user_id = idea_users.user_id LEFT JOIN comments ON ideas.idea_id = comments.fk_idea_id group by ideas.idea_id

Any ideas struggling with this one.

So I solved it like this, But it would be nice to see a Eloquent version of this

 $ideas=DB::table('ideas')
        ->join('idea_users', 'ideas.fk_user_id', '=', 'idea_users.user_id')
        ->leftJoin('comments','ideas.idea_id','=','comments.fk_idea_id')
        ->select(DB::raw('IFNULL(COUNT(comments.fk_idea_id), 0) as cnt'),'ideas.idea_id as ideaid','ideas.idea','idea_users.first_name','idea_users.last_name')->groupBy('ideas.idea_id')->get();
        return $ideas;

When table ideas has a table prefix,error tips comments.fk_idea_id not exists.

kyoukhana said:

So I solved it like this, But it would be nice to see a Eloquent version of this

$ideas=DB::table('ideas')
       ->join('idea_users', 'ideas.fk_user_id', '=', 'idea_users.user_id')
       ->leftJoin('comments','ideas.idea_id','=','comments.fk_idea_id')
       ->select(DB::raw('IFNULL(COUNT(comments.fk_idea_id), 0) as cnt'),'ideas.idea_id as ideaid','ideas.idea','idea_users.first_name','idea_users.last_name')->groupBy('ideas.idea_id')->get();
       return $ideas;