John Davidson

Message:

I'm trying to filter an existing collection to avoid querying database non-stop. As far as I understand, re-using an existing collection does not perform any query in the database, but correct me if I'm wrong.

Unfortunately the results given by the filter are not similiar with the normal query function.

Three tables only:

Clients (id, name)

Products (id, name)

ProductsClients (product_id, client_id)

The main function that works flawless:

public function findAll($productsIds)

{

    $this->clients = Clients::with(['products' => function($query) use($productsIds)

        {

            $query->whereIn('product_id', $productsIds);

        }])

        ->whereHas('products', function($query) use ($productsIds)

        {

            $query->where('active', true)

                ->whereIn('product_id', $productsIds);

        })

        ->groupBy('id')

        ->orderBy('id', 'ASC')

        ->get();

}

The following illustrates my problem:

ProductsClients table sample:

(1000, 1)

(1001, 1)

(1000, 2);

(1002, 2);



Input sample:

Customer 1 : has [1000, 1001] : misses [1002]

Customer 2 : has [1000, 1002] : misses [1001]



Output sample:

Customer 1 : [1000, 1001] + [1002]

Customer 2 : [1000, 1002] + [1001]

The function to perform query operations within the collection:

private function findInCollection($productsIds)

{

    return $this->clients->filter(function($client) use ($productsIds)

    {

        return $client->products->whereIn('product_id', $productsIds)->count() > 0;

    })->all();

}

And the logic I have to grab the missing id's:

foreach ($this->clients as $client)

{

    $missingProductsIds = [...]; // Will be 1002 & 1001 respectively

    $fromOtherClient = $this->findInCollection($missingProductsIds); 

}



Current (wrong) output:

Customer 1 : [1000, 1001] + [1000, 1001] + [1002]

Customer 2 : [1000, 1002] + [1000, 1002] + [1001]

So, it's returning the initial input plus the correct value I'm looking for.

If instead of $fromOtherClient = $this->findInCollection($missingProductsIds); I use $fromOtherClient = $this->findAll($missingProductsIds); it works great.

---

My Answer:
The issue here is that when you filter the existing collection using the `findInCollection` method, you are not actually modifying the original collection. Instead, you are creating a new collection with the filtered results. This new collection does not have the same constraints as the original query, which is why you are getting duplicated results.

To fix this issue, you can modify the `findInCollection` method to return the filtered collection instead of just the filtered items. This way, you can use the filtered collection in your loop to get the correct results.

Here's how you can modify the `findInCollection` method:

php
private function findInCollection($productsIds)
{
return $this->clients->filter(function($client) use ($productsIds)
{
return $client->products->whereIn('product_id', $productsIds)->count() > 0;
});
}


And then in your loop, you can use the filtered collection like this:

php
foreach ($this->findInCollection($missingProductsIds) as $client)
{
// Do something with the filtered client
}


This way, you will only get the correct results without any duplicates.