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)]) }); })