How use whereIn in laravel for get exactly all of array condition items-CodePudding

hi I want to use whereIn to get data from a table :

$values = DB::table('attribute_product')->whereIn('value_id' , [1,5])->get();

but I want to get columns that have all [1,5] items not just one of the array item

my table data :

{
"attribute_id": 1,
"product_id": 1,
"value_id": 1
},
{
"attribute_id": 12,
"product_id": 1,
"value_id": 2
},
{
"attribute_id": 13,
"product_id": 1,
"value_id": 3
},
{
"attribute_id": 14,
"product_id": 1,
"value_id": 4
},
{
"attribute_id": 1,
"product_id": 8,
"value_id": 1
},
{
"attribute_id": 12,
"product_id": 8,
"value_id": 5
},
{
"attribute_id": 13,
"product_id": 8,
"value_id": 10
},
{
"attribute_id": 14,
"product_id": 8,
"value_id": 11
}

I want just return that have both value_ids [1,5]:

"attribute_id": 1,
"product_id": 8,
"value_id": 1
},
{
"attribute_id": 12,
"product_id": 8,
"value_id": 5
},

but that code I wrote above returns:

{
"attribute_id": 1,
"product_id": 1,
"value_id": 1
},
"attribute_id": 1,
"product_id": 8,
"value_id": 1
},
{
"attribute_id": 12,
"product_id": 8,
"value_id": 5
},

CodePudding user response：

You can Use Laravel groupBy

$values = DB::table('attribute_product')->orderBy('product_id', 'desc')->whereIn('value_id', [1, 5])->groupBy('value_id')->get();

CodePudding user response：

$filter_value_id = [1, 5];
$values_by_product = DB::table('attribute_product')
    ->whereIn('value_id', $filter_value_id)
    ->get()
    ->groupBy('product_id');

foreach ($values_by_product as $product => $value) {
    echo "product id: $product<br>";
    if ($value->count() === sizeof($filter_value_id))
        dump($value);
}

CodePudding user response：

This should work:

$values = [1, 5];
$filtered = DB::table('attribute_product')
    ->whereIn('value_id', $values)
    ->get()
    ->groupBy('product_id')
    ->filter(function ($product) use ($values) {
        return $product->pluck('value_id')
            ->intersect($values)
            ->count() === count($values)
    }) 
    ->flatten();

PS: I don't like this solution too much since it does the calculation in memory. You should make use of relationships to do this at database level.