Is it possible simplify this query to work with paginator (without creating a custom paginator)?

Question

I have list of messages by users and I need to get the oldest message of each user, then use paginate() on the result.

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

So in this case I need to get only rows with id 2 and 3

The problem is that I got this complex query to do it, and I have to use it inside DB::select(DB::raw($query));, which returns an array, and paginate can't be used on array.

This is the query:

select T.*
from (select *,
             row_number() over (partition by user_id order by date_posted, id) as sn
      from my_table
     ) T
where sn = 1;

Is there a way to get these results with statements that can be converted to Query Builder or Eloquent?

*I can't disable only_full_group_by

Could you create a model to handle the pivot to return an eloquent model which you can then handle with pagination? There are limitations of the orm. By running a raw query you are essentially reaching outside the knowledge of the orm. Therefore it's only option is to return an array. If you create a model for this then eloquent then has knowledge and handle it like you expect. — amac, Aug 30 '22 at 06:31
I think it's impossible in my case, but I might not understand you right? Since my DB is handled not by me and it's a Microsoft SQL server. Could you provide an example? — pileup, Aug 30 '22 at 06:35
Basically you are asking the model to create a piece of data created on the fly and expecting eloquent eloquent on the fly to know how to handle that without a definition. Therefore assuming it would conform to the expectations of orm... — amac, Aug 30 '22 at 06:37
So you mean I need create some model called `GetUniqueMessages`, which will fetch the data I need (do the query above), then return it. Then when I call the model it will return this data as the ORM object: `GetUniqueMessages::get();`? — pileup, Aug 30 '22 at 06:39
Without that how could eloquent have knowledge of the runtime query meets it's requirements? Therefore how could it know if it abides it's rules. — amac, Aug 30 '22 at 06:42
I just missed one part: to do this query inside the Model's method, not directly `GetUniqueMessages::get();`, but actually it should be `GetUniqueMessages::getMessages()->get();` — pileup, Aug 30 '22 at 06:43
There are many ways to approach this but basically you can relationships, queries, quiry scopes, etc... The main point is you need to return a model that extends eloquent to run eloquent methods. — amac, Aug 30 '22 at 06:46
Understood, I will try to do the model approach. Otherwise, I will create my own custom paginator. In fact I already made it, but the pagination links are broken. Will see which one works better. thank you — pileup, Aug 30 '22 at 06:49
I feel like a custom pagination is to specific. I could be wrong, only because you will need one for each query and will have redundant code. However if it works — amac, Aug 30 '22 at 06:52
Check this out: You can get a relatively general paginate method: https://stackoverflow.com/a/63392687/19815685 — pileup, Aug 30 '22 at 06:53
Basically creating the models are a lot easier to make and are like 30 lines per file vs a 200 line pagination file that refuses code. — amac, Aug 30 '22 at 06:53
Ya in your example, see what I'm saying. They are using logic specific to the query. Just make the model and have the power of eloquent, relationships, etc... — amac, Aug 30 '22 at 06:56
But now I wonder - do I need a new model for that, or I can just put that query inside a method in the `Messages` model, then get it like `Messages::getUniqueMessages()`? — pileup, Aug 30 '22 at 06:58
That's even easier. Sorry I don't know your whole use case but basically if you can keep the orm in play you can leverage it. — amac, Aug 30 '22 at 07:00
Like is said previously you can utilize a bunch of approaches to return a model. So as long as you return a model that extends eloquent you can leverage it's power. — amac, Aug 30 '22 at 07:02
Second you start using db or raw.. you are gonna return arrays and loose that leverage. — amac, Aug 30 '22 at 07:03
Ya also it sounds like you may want to use a query scope for your exact case. Look it up. — amac, Aug 30 '22 at 07:06

Hosserin Ibrahim · Answer 1 · 2022-08-30T07:09:00.000

1

I'm solve the problem with this sql command:

select MIN(id) AS id, user_id, MIN(date_posted) AS date_posted, substring_index(GROUP_CONCAT(message), ',', 1) AS message from `messages` group by `user_id` order by `date_posted` asc

if you need Laravel code:

    $message = Message::
    selectRaw("MIN(id) AS id, user_id,
     MIN(date_posted) AS date_posted,
     substring_index(GROUP_CONCAT(message), ',', 1) AS message")
    ->groupBy('user_id')
    ->orderBy('date_posted')
    ->get();

if you need paginate just insted of get write paginate()

edited Aug 30 '22 at 07:09

answered Aug 30 '22 at 07:06

Hosserin Ibrahim

184
7

Yup and to my point. This answer demonstrates utilizing the model. – amac Aug 30 '22 at 07:07
This doesn't work, I get the same error of `column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause`. (This is why I said at the end of the post that I can't disable `only_full_group_by`). Is there a way to make this query work with this constraint? – pileup Aug 30 '22 at 07:21
yes what column you need in data? – Hosserin Ibrahim Aug 30 '22 at 07:39
you can use aggregate function like `MIN()` or `SUM()` if you need jsut the data you can use `MIN()` or `MAX` – Hosserin Ibrahim Aug 30 '22 at 07:46

Is it possible simplify this query to work with paginator (without creating a custom paginator)?

1 Answers1