Laravel Query where All

I wondered how to make a Where All clause with Laravel
I’m trying to check if the episodes that a user saw are all the episodes of the series.

I’m using the WhereIn clause but i returns the results if i saw one episode of the serie.

$alleps get all the episodes of the serie
$seriessaw get all the episodes a user saw

Thank you for your answers !

    $alleps = DB::table('episodes')
        ->select('episodes.id as ep_id')
        ->join('seasonsepisodes', 'episodes.id', '=', 'seasonsepisodes.episode_id')
        ->join('seriesseasons', 'seasonsepisodes.season_id', '=', 'seriesseasons.season_id')
        ->where('seriesseasons.series_id', '=', $id);

    $seriesSaw = DB::table('usersepisodes')
    ->select('usersepisodes.episode_id as ep_id')
    ->where('usersepisodes.user_id', '=', Auth::user()->id)
    ->whereIn('usersepisodes.episode_id', $alleps)
    ->get();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I think you would need to set a having clause which would force records to only return if there’s the same amount of records as there are amount of episodes.

$seriesSaw = DB::table('usersepisodes')
    ->select('usersepisodes.episode_id as ep_id')
    ->where('usersepisodes.user_id', '=', Auth::user()->id)
    ->whereIn('usersepisodes.episode_id', $alleps)
    ->having(DB::raw('count(*)'), count($alleps))
    ->get();

You should note however that this would likely break if there’s any possibility of having duplicates in the userepisodes table.

Method 2

When you use WhereIn, you have to pass an array as the second parameter.

$alleps = DB::table('episodes')
        ->select('episodes.id as ep_id')
        ->join('seasonsepisodes', 'episodes.id', '=', 'seasonsepisodes.episode_id')
        ->join('seriesseasons', 'seasonsepisodes.season_id', '=', 'seriesseasons.season_id')
        ->where('seriesseasons.series_id', '=', $id)->get()->toArray();

    $seriesSaw = DB::table('usersepisodes')
    ->select('usersepisodes.episode_id as ep_id')
    ->where('usersepisodes.user_id', '=', Auth::user()->id)
    ->whereIn('usersepisodes.episode_id', $alleps)
    ->get();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x