Issue when using distinct with paginate

Anyone come across this?

I have a query that involves using left join to attach regions to a table so that they can be filtered.

I have a simple paginate(15) on the query, and since it can return duplicate rows, I added distinct('topics.id')

In debugbar I see the following two queries.

select count(distinct `topics`.`id`) as aggregate from `topics` left join `speakers` on `topics`.`speaker_id` = `speakers`.`id` left join `region_speaker` on `region_speaker`.`speaker_id` = `speakers`.`id` where `region_id` in (6, 29, 30, 31, 32, 33, 34) and `category_id` = '1' and `fee_id` = '1' and `status` = 'P' and `topics`.`deleted_at` is null;

select distinct `topics`.*, `region_id` from `topics` left join `speakers` on `topics`.`speaker_id` = `speakers`.`id` left join `region_speaker` on `region_speaker`.`speaker_id` = `speakers`.`id` where `region_id` in (6, 29, 30, 31, 32, 33, 34) and `category_id` = '1' and `fee_id` = '1' and `status` = 'P' and `topics`.`deleted_at` is null order by `subject` asc limit 15 offset 0

In the first query, you see the paginator understanding the number of records. This results in a value of 16

But then when the paginator obtains the records, the distinct no longer specifies the column that should be distinct

This results in 18 rows being returned, and I see this in the paginator links

Showing 16 to 18 of 16 results

And I see one of the rows three times.

Has anyone encountered this?

LaryAI

2 years ago

Level 58

It seems like you're encountering an issue with Laravel's paginator when using distinct() in combination with paginate(). The distinct() method is not being applied as you might expect when fetching the actual results, which is why you're seeing duplicate rows.

To resolve this issue, you can use a subquery to first select the distinct topics.id values and then join this subquery with the rest of your tables. This way, you ensure that the pagination is applied to a result set that already has distinct topics.id values.

Here's an example of how you can modify your query:

// First, create a subquery to select distinct topic IDs
$distinctTopics = Topic::select('topics.id')
    ->distinct()
    ->leftJoin('speakers', 'topics.speaker_id', '=', 'speakers.id')
    ->leftJoin('region_speaker', 'region_speaker.speaker_id', '=', 'speakers.id')
    ->whereIn('region_id', [6, 29, 30, 31, 32, 33, 34])
    ->where('category_id', '1')
    ->where('fee_id', '1')
    ->where('status', 'P')
    ->whereNull('topics.deleted_at');

// Then, use the subquery to join with the topics table and paginate the results
$topics = Topic::joinSub($distinctTopics, 'distinct_topics', function ($join) {
    $join->on('topics.id', '=', 'distinct_topics.id');
})
->leftJoin('speakers', 'topics.speaker_id', '=', 'speakers.id')
->leftJoin('region_speaker', 'region_speaker.speaker_id', '=', 'speakers.id')
->select('topics.*', 'region_id') // Select the columns you need
->orderBy('subject', 'asc')
->paginate(15);

return view('your_view', compact('topics'));

In this solution, $distinctTopics is a subquery that selects the distinct topics.id values based on your conditions. Then, we use joinSub() to join this subquery with the topics table, ensuring that we only get distinct topics. Finally, we apply paginate() to the joined query.

This should give you the correct pagination without duplicate rows. Remember to replace 'your_view' with the actual view name you are using to display the results.

Snapey

2 years ago

Level 122

I resolved the issue after some consultation of the mysql docs.

You cannot pass a column name into the distinct command, well, you can but it does not have any effect. Distinct works by eliminating duplicates that are identical for the full width of the result row. Since my row contains mostly a single record, but joined with a pivot table for filtering purposes, it is the pivot value that makes all the rows different and therefore on the actual query, nothing is discarded.

When the paginator counts the rows, it discards the select statement and only selects the value passed into the eloquent distinct statement (topics.id) in this case, and so removes any duplicate IDs.

The paginator query uses my select list, which includes my unique region_id

The simple answer was to remove region_id from my select statement so that any duplicated rows are identical and removed.

I don't need the region_id since I'm also doing a ->with('regions') on my query

So, sorry Lary, I didn't need a subquery, just a simple removal of the joined table from the result set (the join is still in the query and is used to select the right rows, but it is not present in the results).

My final query looks like

        $talks = Topic::with('speaker.regions', 'tagged', 'category')
            ->leftjoin('speakers', 'topics.speaker_id','=','speakers.id')
            ->leftjoin('region_speaker', 'region_speaker.speaker_id','=','speakers.id')
            ->select('topics.*')  // removed region_id
            ->regions($filters['region'])
            ->category($filters['category'])
            ->fee($filters['fee'])
            ->recency($filters['recency'])
            ->tagged($filters['tagged'])
            ->published()
            ->notice($filters['notice'])
            ->online($filters['online'])
            ->orderBy('subject')
            ->distinct('topics.id')
            ->paginate(15);

Lots of scopes that react to user filter choices on the front end

3 likes

Please or to participate in this conversation.