How to use paginate on raw sql query?

Level 37

You can't, you need to refactor your query, select will always return an array.

https://laravel.com/docs/9.x/database#running-a-select-query

The select method will always return an array of results.

Maybe provide the full query to help you refactor it.

1 like

OussamaMater

Level 37

@Ligonsker well check @sinnbeck that's the refactor I was going to do.

1 like

Level 8

@OussamaMater This is the query:

select A.*
from (select *,
             row_number() over (partition by number order by date,  id) as single
      from my_table
     ) A
where single= 1;

jlrdw

Level 75

With a lengthaware paginator: https://laracasts.com/discuss/channels/guides/manual-pagination-episode-3

1 like

Level 102

Dont we already have a separate thread for this? :)

\DB::table('table_name')
->select(['special_number', \DB::raw('min(date(date))')])
->groupBY('special_number')
->orderBy('special_number')
->paginate(20);

1 like

Level 8

@Sinnbeck haha well the other post was for a specific query and this is for how to paginate on it, so didn't want to mix xD I now saw that you replied this there

Level 8

@Sinnbeck but this brings us back to the same error with aggregate because it only contains 2 columns and not all :/

jlrdw

Level 75

@Ligonsker I showed you in that post how to turn off group by for one query only. Read all of the replies.

1 like

Level 102

@Ligonsker You just need to make sure that each column is either an aggregate or in group by. And I assume you dont need the ID column

1 like

Level 8

@jlrdw sorry I missed that, going to read now. But if I cancel it for this query and I do happen to get some issue because of same column data, what will happen? will it select arbitrary row of the two?

Level 102

@Ligonsker If you disable strict mode, then yes. But with ONLY_FULL_GROUP_BY by, the data you get should be quite predictable

If you can live with it not being 100% predictable, then you can turn it off :)

Level 8

@Sinnbeck But I need to make sure that it's unique by special_number column and that it's the oldest date, if I just put all columns in either, how to put them in such way that it does what I wanted it to do? won't that just do it randomly?

Level 102

@Ligonsker Can you give an example of just two rows with the same special_number ? What other columsn are there and what info do they hold?

1 like

Level 8

@Sinnbeck alright sorry I will do some more realistic data (still not the actual DB since it's too many columns):

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 I need to get the oldest message of each user, in the example above it would be rows 2 and 3

Level 102

@Ligonsker this is the closest I have been able to find https://stackoverflow.com/a/584442

Never had to write a query like that myself :)

1 like

Level 8

@Sinnbeck You think it's translatable to either Query Builder or Eloquent (for use with paginate)

jpmg

Level 13

The paginate method lives on the \Illuminate\Database\Query\Builder class, so you'll have to use that in order to leverage pagination:

			DB::table('YUOUR SQL QUERY')->paginate(8)

Level 8

@jpmg But how can I use that syntax? inside table() I need to specify the table, not query

jpmg

Level 13

Hello @Ligonsker this will help you a lot https://codeutility.org/php-how-to-use-pagination-with-laravel-dbselect-query-stack-overflow/

jlrdw