Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

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)

Last updated 2 years ago.
0

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

Last updated 2 years ago.
0

Any ideas struggling with this one.

Last updated 2 years ago.
0

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;
Last updated 2 years ago.
0

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;
0

Sign in to participate in this thread!

Eventy

Your banner here too?

kyoukhana kyoukhana Joined 28 Feb 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.