Laravel Query Builder where

->where(function($query) {
    return $query->where( property_id ) // 4
					->where( property_value_id ); // 1
})
->where(function($query) {
    return $query->where( property_id ) // 7
					->where( property_value_id ); // 6
})
->where(function($query) {
    return $query->where( property_id ) // 6
					->where( property_value_id );  // 3
})

For that simpler table, it sounds like you want the following sql:

select ad_id
from products
where(property_id = 4 and property_value_id = 1) 
OR (property_id = 7 and property_value_id = 6)
group by ad_id
having count(ad_id) = 2

You can't make it all and because there is no row that fulfils that requirement.

So what this does is find all the rows that have at least one of the requirements and group them by ad_id.

The having count(ad_id) is there to only select out those that have all the requirements. This equals 2 since you have two sets of row requirements that needs to be fulfilled. In your original query this would be 3.

If you then wanted to translate that to query builder:

DB::table('products')
   ->where(function ($query) {
      $query->where('property_id', 4)
         ->where('property_value_id', 1);
   })
   ->orWhere(function ($query) {
      $query->where('property_id', 7)
         ->where('property_value_id', 6);
   })
   ->select('ad_id')
   ->groupBy('ad_id')
   ->havingRaw('count(ad_id) = ?', [2]);

I haven't tested the query builder version but it should be right.

for your simpler table, maybe like this

        $arr_property = [ 4, 7 ];
        $arr_property_value = [ 1, 6 ];

        $ads = DB::table('ads')
                    ->select('ads.*')
                    ->when( count( $arr_property ) > 0, function($query) use ($arr_property, $arr_property_value) {
                        return $query->whereIn('ads.property_id', $arr_property )
                                ->whereIn('ads.property_value_id', $arr_property_value );
                    })
                    ->groupBy('ads.product_id')
                    ->havingRaw('count(`ads`.`product_id`) = ?', [ count( $arr_property ) ] )
                    ->get();

        // $arr_property = [ 4 ];
        // $arr_property_value = [ 1 ];
        // 1 rows result : 69

        // $arr_property = [ 7 ];
        // $arr_property_value = [ 6 ];
        // 2 rows result : 67 & 69

        // $arr_property = [ 4, 7 ];
        // $arr_property_value = [ 1, 6 ];
        // 1 rows result : 69