I have a database with three tables and a one to many relationship from the first two to `records`:

```
Table     → Columns
-----------------------------------------
Firmwares → [id, version, …]
Tests     → [id, name, …]
Records   → [id, firmware_id, test_id, …]

```

Records contains the result of one test with one firmware. I need to make querys to compare two firmware versions.

**Edit:**
Let's say I have a records table like this:

```
Records: [id, firmware_id, test_id, …]
 1, 1,  7, …
 2, 3,  7, …
 5, 1,  6, …
10, 4,  7, …
28, 1, 14, …
43, 4, 14, …
55, 4, 15, …
⋮

```

I would like get do a query with `firmware_id 1,4` a list like this:

```
Records: [id, firmware_id, test_id, …]
 1, 1,  7, …
10, 4,  7, …
28, 1, 14, …
43, 4, 14, …
⋮

```

The output only contains rows where both firmwares have a scan of the **same** test.

---

I found [this](https://stackoverflow.com/questions/10285761/get-records-having-both-values-in-in-using-sql) but I couldn't figure out how to do this in eloquent.

---

Currently, my query looks something like this:

```php
Record::select(['id', 'firmware_id', 'test_id'])
    // Apply firmware selection
    // DO SOMETHING HERE (I think ...)
    ->whereIn('firmware_id', [$data['FirmwareA'], $data['FirmwareB']])
    // Apply test selection
    ->when(array_key_exists('TestPropA', $data) || array_key_exists('Test', $data), static function($query) use ($data) {
        return $query->whereIn('test_id', Test::select(['id'])
            ->when(array_key_exists('TestPropA', $data), static function($query) use ($data) {
                return $query->whereIn('test_prop_a_id', $data['TestPropA']);
            })
            ->when(array_key_exists('Test', $data), static function($query) use ($data) {
                return $query->whereIn('id', $data['Test']);
            })
        );
    })
    ->get();

```

And I get all results containing firmware version A or B. I want a list that contains those results **only if both** are present.

### Solutions:

* I could just do two querys, one for each firmware, and intersect the result on the `test_id`. I would have to do this in software, because MySQL has no `intersect`. Is there maybe a better way?

Using Laravel 8.x