Multiple exists queries not returning anything (SQL)

When I run single exist query I get results as expected:

SELECT * 
FROM `slasher_farming_mods` 
WHERE EXISTS (SELECT * FROM `slasher_farming_brands` 
              WHERE `slasher_farming_mods`.`brand_id` = `slasher_farming_brands`.`id` 
                AND `brand_id` = '7' 
              ORDER BY `id` asc) 
LIMIT 4 OFFSET 0

When I run multiple exist queries, I don’t get any results:

SELECT * 
FROM `slasher_farming_mods` 
WHERE EXISTS (SELECT * FROM `slasher_farming_brands` 
              WHERE `slasher_farming_mods`.`brand_id` = `slasher_farming_brands`.`id` 
                AND `brand_id` = '7' 
                ORDER BY `id` ASC) 
  AND EXISTS (SELECT * FROM `slasher_farming_brands` 
              WHERE `slasher_farming_mods`.`brand_id` = `slasher_farming_brands`.`id` 
                AND `brand_id` = '24' 
              ORDER BY `id` ASC) 
LIMIT 4 OFFSET 0

Tried using debugbar in laravel to see if my query is taking too long, but it takes less than 1ms. What could be wrong here, I tried to run these query also directly inside phpmyadmin but still no results with more than one where exists.

Query is populated by foreach loop in laravel.

   foreach ($brands as $brand){
            $query->whereHas('brand', function($q) use ($brand){
                $q->where('brand_id', '=', $brand)->orderBy('id');
            });
        }
    }

Answer

Your query doesn’t work because brand_id cannot be both 7 and 24 for a given record. You want “OR”. It would be clearer to just use the expression brand_id in (7, 24) instead of the two separate sub-queries. There is no point in sorting the sub-query.

Alternatively, join the two tables:

select ...
from slasher_farming_mods m
join slasher_farming_brands b on m.brand_id = b.id
where brand_id in (7, 24)
LIMIT 4 OFFSET 0

Leave a Reply

Your email address will not be published. Required fields are marked *