Wrong "total" from pagination when joining with a table with "uninteresting" columns

Questions
#1

I’m trying to use October CMS’s Builder::paginate, but I’m ending up with an overly high total count in the returned Paginator due to it including duplicate rows. The underlying Laravel API provides a way to override the total, but October doesn’t expose this. What’s the best thing to do here?

I have two tables, X, with columns x1 and x2 that I need for display, and Y with columns x1 and y1, that I’m joining against to filter X. I have a query equivalent to this:

SELECT DISTINCT
  X.x1, X.x2
FROM X
INNER JOIN Y
  ON Y.x1 = X.x1
WHERE
  Y.y1 IN (1, 2);

I need distinct in the select and to avoid selecting columns from Y since there may be multiple rows in Y with distinct y1 values for each row in X.

I get the proper content displayed – no duplicates – but paginator->total() has the wrong count – the one we’d get doing a select * on all the columns from the join.

Overriding $columns doesn’t seem to work.

I’m currently stuck on October CMS 3.x. I wonder if this explains one other thing. Looking at my local copy of October\Rain\Database\Builder::paginate, it has the following API:

public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $currentPage = null)

That’s a little different than Builder - October CMS

public paginate(
    int $perPage = null,
    array $columns = [ 0 => '*'],
    string $pageName = 'page',
    int $page = null
)

I know I can probably avoid this by just getting rid of the join, but I’d like to keep it if I can.

#2

Getting rid of the join did work, but it’d be great to get a solution that allows keeping the join.

#3

The method signature doesn’t really change the internals, so this is likely caused by how Laravel handles pagination at the SQL query level. It would be worthwhile going down this path for investigation… I’d probably start by looking at the SQL statement produced. You can write all SQL statements to the log file by calling the following code before SQL occurs:

trace_sql();

Hopefully this helps.