Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
My previous posts asked about the query itself but led to problematic results so I thought maybe it's possible to filter results after query and before pagination, or after pagination?
This is the problem: I need a complex query, so I have to use raw SQL which returns an array, thus I can't use paginate on it.
What I need is to display is the oldest message of a user, example data:
id user_id message date_posted
1 5 some_message 2022-07-15
2 125 some_message 2022-08-02
3 5 some_message 2022-04-05
In this example, I'll need to display only rows 2 and 3 because the user with user_id of 5 has 2 records, but I need the older one which is row 3
Since the SQL query for that made some problems for pagination. Is it possible to just fetch everything and then filter it after or before I use the ->paginate() method?
Messages::paginate(15); // then do something to only show the latest message of each user
If nothing works, I'll give LengthAwarePaginator a try to create my own paginator
I believe I mentioned in another post that you can use a custom lengthaware paginator for this. However I have never tried it with MS SQLserver.
But a custom paginator isn't hard to write, just simple math.
Or, why not use collections for this. And yes there is a way to paginate a collection. Myself and others have given examples in past post.
if you load all records to memory and then filter, you may as well send all records to the view since you have already killed all your memory. pagination should be for database queries,
I suggest that you create a database view of your query and then query it using eloquent and pagination.
You can read about views here https://tray2.se/posts/database-design-part-2
I never used SQL Server so the syntax for creating a view might differ a little.
@jlrdw @snapey @tray2 I managed to get my custom paginator to work using this post :D:
https://stackoverflow.com/a/63392687/19815685
I was given another solution, however I did not try it yet: To do the complex query as a method inside the Model, then use this to paginate.
For example: MyMode::getUniqueData()->paginate();. Might work as well no?
@Ligonsker no
At the end of the day, you filter and prepare your data in SQL or do it in memory. The advice is to do it in SQL.
Putting code in the model won't change anything. Its still running in php after the data has been retrieved from the database
@Snapey Oh yes that's what I meant: I will filter it in SQL first, just put it inside the model:
// Messages Model:
public function getComplexQueryData() {
$query = 'the complex SQL query';
$data = DB::select(DB::raw($query));
return $data;
}
Then in the Controller where I need to paginate I will use it:
Messages::getComplexQueryData->paginate();
Please or to participate in this conversation.