Laravel Pagination with Duplicate Grouping: Ensuring Consistent Result Count

Description

I am working on a Laravel project where I need to paginate products with certain filters and sort criteria. Some products are considered duplicates if they have the same type and website->domain->id, but different prices. The goal is to return unique products while grouping duplicates under each main product. The challenges are to ensure that the pagination returns a consistent number of results (e.g., 20 products) even after removing duplicates and to remove duplicates effectively.

Current Implementation and why it does not work

I have implemented a solution where I fetch products applying filter, sorting and pagination criteria of the user input and then find duplicate products for each of the fetched products. This does not work as it does not omit the duplicates in the initial query. So if in the first 20 results I have duplicate products they will be returned like this:

[
    {
        product1
    },
    {
        product2a (one with duplicates),
        otherOffers: [
            {
                product2b
            },
            {
                product2c
            }
        ]
    },
    {
        product3
    },
    {
        product4
    },
     {
        product2b (one with duplicates),
        otherOffers: [
            {
                product2a
            },
            {
                product2c
            }
        ]
    },
]

So, each duplicate will be returned to the frontend and each one inside will hold the duplicates. Instead what should be returned is 20 unique results with the duplicates inside if there are any.

$productsQuery = Product::query()
    ->select(['products.*'])
    ->with([
        'niches',
        'currency',
        'website',
        'flags',
        'account',
        'languages',
        'categories',
        'productFavorites' => function ($query) {
            $query->where('account_id', Auth::user()->selected_account_id);
        },
    ])
    ->where('products.active', true)
    ->joinWebsites()
    ->filter($request->filters)
    ->sort();

$collection = $productsQuery->getCollection();

$mergedProductsCollection = $collection->map(function (Product $product) {
    # Find duplicate products
    $duplicates = Product::select(['products.*'])
        ->where('product.id', '!=', $product->id)
        ->where('product.active', true)
        ->joinWebsites()
        ->whereHas('website', function ($query) use ($product) {
            $query->where('domain_id', $product->website->domain_id);
        })
        ->whereProductType($product->product_type)
        ->get();
    return $duplicates->isNotEmpty() ? $duplicates->prepend($product) : $product;
});
$paginated = $productsQuery->setCollection($mergedProductsCollection);

What I Have Tried

Except for this not desirable result I have tried grouping products by name (consider it website->domain->id, I call it name for simplicity) and type with a subquery:

$subQuery = DB::table('products') 
    ->select('product_type', 'name', DB::raw('MIN(price) as price')) 
    ->groupBy('product_type', 'name');

$productsQuery = Product::query()
    ->select(['products.*'])
    ->with([
        'niches',
        'currency',
        'website',
        'flags',
        'account',
        'languages',
        'categories',
        'productFavorites' => function ($query) {
            $query->where('account_id', Auth::user()->selected_account_id);
        },
    ])
    ->where('products.active', true)
    ->joinSub($subQuery, 'unique_products', function ($join) {
        $join->on('products.type', '=', 'unique_products.type')
             ->on('products.name', '=', 'unique_products.name')
             ->on('products.price', '=', 'unique_products.price');
    })
    ->joinWebsites()
    ->filter($request->filters)
    ->sort();

Why this does not work?

This way the duplicates are eliminated and we get exactly 20 unique results back. But it messes the query. For example - if I have 3 duplicate products - one with 100 price, one 200 and third 300 and I filter products with a price range from 150 to 400 I do not get back the 200 product and the other two as duplicate offers as with the subquery I have selected the min price (100) and then try to join back with it, but it is already eliminated by the filters.

The Challenge

I could eliminate duplicates after fetching the products, but if a user requests 20 results per page, and some of these results are duplicates, the final result count after removing duplicates might be less than 20. For instance, if there are 4 duplicates among the first 20 products, only 16 unique products will be returned. This inconsistency in the result count is not desirable.

My Thought Process

I considered fetching an initial batch of results (e.g., 20), removing duplicates, counting the remaining unique products, and then making additional database queries to fetch the required number of unique products to maintain the desired count (e.g., 20). If I have 16 left after removing duplicates, I fetch 4 more products. If by any chance there is again a duplicate in these 4, I fetch again for, let's say, only 1 product. However, I am afraid this might slow down the query.

Request for Help

I am looking for a solution to:

1. Ensure that the pagination always returns the desired number of unique results (e.g., 20 products) after removing duplicates and adding these as other offers.

2. Efficiently handle the fetching and filtering process without excessive database queries.

Additional info

Laravel version: 10.46

Databsae: MySQL

Thank you

Thank you in advance for your help and suggestions. Any insights or examples would be greatly appreciated!