mysql query to laravel query

1
votes

I've been trying for several hours to make this little query work, but I cant seem to make it work. Basically I'm trying to do a foreach from the results of the query.

This is the query I want in raw MYSQL:

SELECT id FROM albums WHERE id IN (SELECT album_id FROM user_albums WHERE user_id =".Auth::user()->id

And I tried the following:

$myAlbums = Album::whereIn('id', DB::table('user_albums')->where('user_id', Auth::user()->id)->select('album_id')->get());

But it seems like the whereIn doesnt take the array from the select correctly and it gives nothing back.

1
phpmysqllaravel

1 Answers

0
votes

The query builder doesn't support subquery.

Break it up into two queries. Something like this. (untested)

$albums = DB::table('user_albums')->where('user_id', Auth::user()->id)->get();

// collect the album ids
$albumIds = array();
foreach ($albums as $a) {
    albumIds[] = $a->album_id;
}

$myAlbums = Album::whereIn('id', $albumIds)->get();