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
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;
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community