Get all objects which have ALL of the selected items

Question

I have 3 tables:

Certificates - all available certificates

Users - table of users

UserCertificates - certificates that users own. each record has it's own ID, user_id and certificate_id foreign keys.

Now, I have an array (or just one value) of the certificates IDs, and I need to get all users who own those chosen certificates. So if the certificate IDs are 1,4,7 I need to select all users who own ALL of those certificates, not 1 OR 4 OR 7.

I have the working code, it took me few good hours to get it working but it does not look clean. Is there a cleaner way of doing it?

$requiredCertificates is an array of certificate_id's

public function getMatches(Opportunity $opportunity)
{
    $requiredCertificates = $this->getRequiredCertificates($opportunity);

    return User::with('certificates')
        ->whereHas('certificates', function ($query) use ($requiredCertificates) {
            $query->select(\DB::raw('count(distinct certificate_id)'))->whereIn('certificate_id', $requiredCertificates);
        }, '=', count($requiredCertificates))
        ->get();
}

score 0 · Answer 1 · edited Mar 15 '18 at 10:54

0

Try this..

public function getMatches(Opportunity $opportunity)
{
    $requiredCertificates = $this->getRequiredCertificates($opportunity);

    $users = User::with('certificates');

    foreach($requiredCertificates as $certificateId) {
        $users->whereHas('certificates', function ($query) use ($certificateId) {
            $query->where('certificate_id', $certificateId);
        });
    }

    return $users->get();
}

edited Mar 15 '18 at 10:54

Varin

2,354
2
20
37

answered Mar 15 '18 at 10:33

iamab.in

2,022
3
18
39

1

Try it first with return $users->toSql() to see what this thing actually builds. While it looks cleaner (I wanted to suggest it too, he was faster), it could possibly more taxing on the db. Have to see the sql – Inuyaki Mar 15 '18 at 11:12
sql generated using the method in question(used arry[1,2]) : `select * from 'users' where (select count(distinct certificate_id) from 'certificates' inner join 'user_certificates' on 'certificates'.'id' = 'user_certificates'.'certificates_id' where 'users'.'id' = 'user_certificates'.'user_id' and 'certificate_id' in (1, 2)) = 2` – iamab.in Mar 15 '18 at 11:55
sql generated using the method in question(used arry[1,2]) : `select * from 'users' where exists (select * from 'certificates' inner join 'user_certificates' on 'certificates'.'id' = 'user_certificates'.'certificate_id' where 'users'.'id' = 'user_certificates'.'user_id' and 'certificate_id' = 1) and exists (select * from 'certificates' inner join 'user_certificates' on 'certificates'.'id' = 'user_certificates'.'certificate_id' where 'users'.'id' = 'user_certificates'.'user_id' and 'certificate_id' = 2)` – iamab.in Mar 15 '18 at 12:03
@Inuyaki, exists works faster than in. Isn't it?(I just google this but not sure) – iamab.in Mar 15 '18 at 16:25
[related search](https://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-mysql) – iamab.in Mar 15 '18 at 17:23
Your method is about 50% faster! I use https://github.com/barryvdh/laravel-debugbar with Laravel and it adds a toolbar to your project which shows all queries ran on the page and the time it took to process them. Thanks dude! Now I have another issue as I'm trying to use this with Datatables plugin and currently this runs 2 queries per row in the table, so if you have 50 rows it will run 100 queries... – Varin Mar 15 '18 at 18:31
exists is faster, yes... I am still wondering about the scalability. For only 2 it's obviously faster, but what if the array has length 50 or so? Why do you have 2 queries per row in the table? – Inuyaki Mar 16 '18 at 06:51

Get all objects which have ALL of the selected items

1 Answers1