When using Laravel, you will get used to making SQL queries in a fast and flexible way. However, right now there is a problem with pagination when you use the $query->union() function with a subsequent $query->paginate(). You will get an error saying SQLSTATE: Cardinality violation: 1222 The used SELECT statements have a different number of columns
Laravel can’t handle the pagination with the union query, as the first SELECT that you have will be altered to be count(*) AS aggregate but the second one will not, resulting in a different number of columns and thus throwing an error.
To get around this, you will need to create a custom instance of the LengthAwarePaginator class. It wasn’t immediately obvious how to do it for me, so I decided to write a blog post about it. The link gets you to the constructor of the class and you can see it takes the items, the total, how many items per page should be displayed, the current page we are on and an array of options.
For reference, here is the full constructor taken from above link:
void __construct(mixed $items, int $total, int $perPage, int|null $currentPage = null, array $options = array())
For lagorial.com, I needed to do a UNION SELECT for a query. In order to achieve that, I first wrote out the query as I would normally. Then, instead of using $query->paginate() I used $query->get(). Then I created the LengthAwarePaginator class like so:
$results = new \Illuminate\Pagination\LengthAwarePaginator(array_slice($results->toArray(), ($page - 1) * 25, 25), count($results), 25, $page, ["path" => "search"]);
As you can see, I manually array_slice() the results. This is OK if you do not expect a lot of rows to be returned (because of sufficient WHERE clauses, for example). If you do expect a somewhat larger result set, you should also include a LIMIT and OFFSET option in your SQL query for optimization (and just pass these results without manually slicing the array).
If you experience problems, drop me a line in the comments section below.