Querying Relationship Existence, how to add conditions depending on an array length?

I would like to filter an item (pubs, in this case) by some characteristics that are stored in a separate table (tapps, in this case), and both are related by pub_tapps.

I have the following tables: pubs, tapps, pub_tapps(pub_id, tapp_id)

The relation between Pub and Tapp is the following:

public function pubTapps() { return $this->belongsToMany(Tapp::class, 'pub_tapps'); }

in my Pub model I tried the following testing for an array $request=[5,8, 7]:

public function pubsFilteredByTapps(FilteredTappsPubsRequest $request) { $tapps_chosen = $request->get('tapps');

$tapps_chosen = is_string($tapps_chosen) ? explode(',', str_replace('"', '', $tapps_chosen)) : $tapps_chosen;

return Pub::whereHas('pubTapps', function($query) use($tapps_chosen) {
    $query->where('tapp_id', $tapps_chosen[0]);
})
    ->whereHas('pubTapps', function($query) use($tapps_chosen) {
        $query->where('tapp_id', $tapps_chosen[1]);
    })
    ->whereHas('pubTapps', function($query) use($tapps_chosen) {
        $query->where('tapp_id', $tapps_chosen[2]);
    })
    ->get();

}

This is working perfectly, but for a given 3 dimensional array...

How can I do for a given array of an n length??

->whereHas('pubTapps', function($query) use($tapps_chosen) { $query->where('tapp_id', $tapps_chosen[n-1]); })

I tried this, but doesn't work at all (returns an empty array):

return $pubs = Pub::whereHas('pubTapps', function ($query) use ($tapps_chosen) {

    foreach ($tapps_chosen as $tappId) {
        $query->where('tapp_id', $tappId);
  }
   })->get();

What would I have to do??? Any ideas to make it work??

Thanks a lot!

lostdreamer_nl

8 years ago

Level 53

Why not just

$tapps_chosen = is_string($tapps_chosen) ? explode(',', str_replace('"', '', $tapps_chosen)) : $tapps_chosen;

return Pub::whereHas('pubTapps', function($query) use($tapps_chosen) {
    $query->whereIn('tapp_id', $tapps_chosen);
})

This would give a query like : WHERE tapp_id IN (1,2,3,4,5)

Quadram

8 years ago

Best Answer

Level 2

Finally it worked for me:

 public function pubsFilteredByTapps(FilteredTappsPubsRequest $request)
{
    $tapps_chosen = $request->get('tapps');
    $tapps_chosen = is_string($tapps_chosen) ? explode(',', str_replace('"', '', 
     $tapps_chosen)) : $tapps_chosen;

    $query = Pub::query();

    foreach ($tapps_chosen as $tappId) {
        $query->whereHas('pubTapps', function($query) use($tappId) {
            $query->where('tapp_id', $tappId);
        });
    }

    return $query->get();
}

lostdreamer_nl

8 years ago

Level 53

@Quadram You do realise you are doing just as many queries as you have selected 'tapps' right?

Even though, when you would have used whereIn() it would only have been 1 query.

Quadram

8 years ago

Level 2

I know what you mean, but may I didn't explain right when I made the question: I would like to get an item or more (a pub/pubs) that has only those characteristics (tapps) I choose.

Of course I've tested with whereIn, but result was lots of pubs that has at least one of the tapps selected, but not all and I wanted them to satisfy all.

The only way I found was doing queries from results of last queries.

Testing using my solution I have only two pubs (what I wanted), but with whereIn I had more than ten.

Thanks a lot for trying to help me ;)

lostdreamer_nl

8 years ago

Level 53

Aah, didnt realize it should only return if all tapp_id matched ;)

Please or to participate in this conversation.