Filter all row with the same foreign key when a those column is null in laravel

Question

i have table schema in mysql for my project.

this project build with laravel and as you can see in the below image i want to fillter all row with the same store_id when a row with the same store_id has a column with a custom value such as unactive

enter image description here

in the select result i dont want to see a row with the sotre_id = 2.

because in mysql table i have row with sotre_id = 2 that row have a column active = 0

please help me to get the wanted result in laravel query builder or mysql query

Answer 1

select * from table
where store_id in (select store_id from table where active = 0 )

eloquent:

Model::whereIn('store_id, Model::where('active', 0)->pluck('store_id))->get()