Laravel Eloquent WHERE field=(...) subquery

Question

I have three tables: (Many-To-Many relation between users and wordlists, and userlistrels is connector)

users:
id
name

wordlists:
id
name
creator_id

userlistrels:
id
user_id
wordlist_id

I want to execute following query:

SELECT * FROM users WHERE id=( SELECT wordlists.creator_id FROM userlistrels JOIN wordlists ON wordlists.id=userlistrels.wordlist_id WHERE userlistrels.user_id=$curr_user )

How to write this type of subqueries (WHERE foo= ( subquery )) in Laravel interface?
Documentation covers SELECT * FROM (subquery) WHERE contition,
SELECT * FROM table WHERE (subquery LIMIT 1) = 'foo',
but not SELECT * FROM table WHERE field=(subquery)

I've tried many approaches, and this is the only one that doesn't throw exception, however it doesn't work:

$curr_user = // id of my user
$users = User::where('id', function($query) use($curr_user){
        $query->select('wordlists.creator_id')
            ->from('userlistrels')
            ->join('wordlists', 'wordlists.id', '=', 'userlistrels.wordlist_id')
            ->where('userlistrels.user_id', $curr_user);
    })->get();

Could anyone please tell me what's wrong with this code, or how to make this type of subquery?

EDIT: My SQL query was wrong. Correct query for what i tried to do is: SELECT * FROM users JOIN (SELECT wordlists.creator_id FROM userlistrels JOIN wordlists ON wordlists.id=userlistrels.wordlist_id WHERE userlistrels.user_id=$curr_user) AS nn ON users.id=nn.creator_id;

And correct laravel code:

$users_sub = DB::table('userlistrels')->join('wordlists', 'wordlists.id', '=', 'userlistrels.wordlist_id')->where('userlistrels.user_id', $curr_user)->select('wordlists.creator_id');

        $users = DB::table('users')
            ->joinSub($users_sub, 'tt', function ($join) {
                $join->on('users.id', '=', 'tt.creator_id');
            })->get();

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I think the problem comes from your subquery. That subquery will return multiple creator_id depends on your database, so there is two solutions.

Solution 1:

Use whereIn instead of where:

User::whereIn('id', function($query) use($curr_user){
        $query->select('wordlists.creator_id')
            ->from('userlistrels')
            ->join('wordlists', 'wordlists.id', '=', 'userlistrels.wordlist_id')
            ->where('userlistrels.user_id', $curr_user);
    })->get();

Solution 2:

append limit(1) after where('userlistrels.user_id', $curr_user):

User::whereIn('id', function($query) use($curr_user){
        $query->select('wordlists.creator_id')
            ->from('userlistrels')
            ->join('wordlists', 'wordlists.id', '=', 'userlistrels.wordlist_id')
            ->where('userlistrels.user_id', $curr_user)
            ->limit(1);
    })->get();

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 18, 2020 at 23:43

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J K Over a year ago

Well I know I can do it like that, but that's not what I want. I need this subquery to return multiple creator_ids, however I realized this can't be done this way, I have to use another JOIN to join the subquery with users table. Sorry, I'm not really experienced in SQL :( Well I could realise that if it isn't in the docs, then probably it's not possible. Thx for answer.

J K Over a year ago

Btw correct query for what I tried to do was:

SELECT * FROM users JOIN (SELECT wordlists.creator_id FROM userlistrels JOIN wordlists ON wordlists.id=userlistrels.wordlist_id WHERE userlistrels.user_id=$curr_user) AS t ON users.id=t.creator_id;

Collectives™ on Stack Overflow

Laravel Eloquent WHERE field=(...) subquery

1 Answer 1

Solution 1:

Solution 2:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution 1:

Solution 2:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related