Laravel 5: How to express (HAVING COUNT(1) IN) in Eloquent ORM

Asked by: Atantares

How can I express this query in Laravel 5?

select * from db.products prod where prod.id in (
    select cvp.product_id from db.characteristic_value_product cvp
    where cvp.characteristic_value_id in (5,6,19)
    group by cvp.product_id
    having count(1) in (
        select count(1) from (
            select distinct characteristic_id from 
            db.characteristic_values
            where id in (5,6,19)
        ) char_val
    )
)

My code is this

       $query->whereIn('id', function ($query) use($filters) {
           $query->select('product_id')
               ->from('characteristic_value_product')
               ->whereIn('characteristic_value_id', $filters)
               ->groupBy('product_id')
               ->havingRaw('COUNT(SELECT COUNT(1) FROM (SELECT DISTINCT characteristic_id FROM characteristic_values WHERE id IN (?)) char_val) = 1', $filters);
       });

It returns this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(1) FROM (SELECT DISTINCT characteristic_id FROM characteristic_valu' at line 1 (SQL: select products.* from products inner join subcats on products.subcat_id = subcats.id where cat_id = 1 and subcat_id = 2 and id in (select product_id from characteristic_value_product where characteristic_value_id in (6) group by product_id having COUNT(SELECT COUNT(1) FROM (SELECT DISTINCT characteristic_id FROM characteristic_values WHERE id IN (6)) char_val) = 1))

Source