Skip to content
Advertisement

laravel write multiple where clause to filter files

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 ?

Advertisement

Answer

The final where clause is this:

->where(function ($query) use ($metas_ids) {
         $query->whereIn('file_metadata.file_id', function($query) use ($metas_ids) {
            $query->select('file_id')
                ->from('file_metadata')
                ->whereIn('metadata_id', $metas_ids)
                ->where('value', true)
                ->groupBy('file_id')
                ->havingRaw('count(file_id) = ?', [count($metas_ids)]) 
        });
    })
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement