I have this method in my model:
public static function BasicsPagination($perPage = null)
{
$perPage = $perPage ?: self::$defaultPagination;
return self::with(['users' => function ($query) {
$query->where('active', 1)
->where('banned', 0)
->where('type', 'basic');
}])->orderBy('first', 'DESC')
->paginate($perPage);
}
but this method generate 3 SQL queries:
select count(*) as aggregate from `data`
select * from `data` order by `first` desc limit 5 offset 0
select * from `users` where `users`.`id` in ('1', '9', '14',
'37', '49') and `active` = '1' and `banned` = '0' and `type` =
'basic'
And of I want a pagination with 20 items per page and if the conditionals in the last query don´t met i have less than 20 items per page.
How can I solve this?
So by default Laravel does not use joins when creating a query with some relationship. So it will always create two queries when fetching some entity with some relationship. In this case it also does an extra query for the count because it needs that count to determine how many pages you can have. So this is normal.
Not really sure what the problem is right now for you? You get results right?
ok, the first query counts
but look this example:
I want a pagination of 2 items per page and i have tables like this:
Users table:
| id | name | active | banned | type | | --- | --- | --- | --- | --- | | 1 | Joe | 0 | 1 | guest | | 2 | Bobby | 1 | 0 | basic | | 3 | Omar | 0 | 1 | guest |
Data table:
| id | user_id | first | | --- | -------- | -------- | | 1 | 1 | 1 | | 2 | 2 | 1 | | 3 | 3 | 0 |
So the second query gets all the data table and ordered by first column and limit 2, and give me the first 2 records in data table.
| id | user_id | first | | --- | -------- | ------- | | 1 | 1 | 1 | | 2 | 2 | 1 |
then the 3 query:
select * from users where users.id in (1, 2) and active = 1 and banned = 0 and type = basic
the result is:
| id | name | active | banned | type | | --- | -------- | -------- | ---------- | ------ | | 2 | Bobby | 1 | 0 | basic |
The result is only 1 row, but my pagination is 2.
How can I achieve always get the specified items per page?
The paginator is giving you 2 results per page. I'm sure of that!! It looks like you always get 2 data results, but not every data results has a set of users. And therefore you only see one result.
You can get around this by doing a join instead of with
Documentation: https://laravel.com/docs/5.7/queries#joins
Please or to participate in this conversation.