laravel write multiple where clause to filter files-CodePudding

I don't know how to filter this in my laravel 9 app using eloquent.

I am having a files table and file_metadata (pivot) - where files has multiple metas:

ex: file_metadata for 3 files:

metadata_id|file_id|value
 3         | 5     | 1
 6         | 5     | 1
 3         | 6     | 1
 6         | 6     | 1
 3         | 7     | 1
 6         | 7     | 0

So files 5 ,6 ,7 have 2 metas (3 active, 6 signed). File 7 is active but not signed.

How to write the where clause to show in this case those both files (5,6) that are signed and active ?

I have tried this but is showing me also the file with id 7 (which is not signed)

->where(function($query) {
   $query->orWhere('file_metadata.metadata_id', 3);
   $query->orWhere('file_metadata.metadata_id', 6)
   $query->where('file_metadata.value', true);
})

I have also tried this:

->where(function($query) {
   $query->Where([
     ['file_metadata.metadata_id', 3],
     ['file_metadata.value', true']
   ]);
   $query->where([
     ['file_metadata.metadata_id', 6],
     ['file_metadata.value', true']
   ]);
}

Any idea how to make the conditions to get the files that are both signed and active ?

CodePudding user response：

calin24 Try this one

->where(function($query) {
   $query->orWhere('file_metadata.metadata_id', 3);
   $query->orWhere('file_metadata.metadata_id', 6);
})->where('file_metadata.value', true)

CodePudding user response：

You could use subquery like below

->where('file_metadata.metadata_id', 6)
->where('file_metadata.value', 1);
->whereIn('file_metadata.file_id', function($query) {
    $query->select('file_id')->from('file_metadata')
        ->where('metadata_id', 3)->where('value', 1);
})