As can be seen i have a 12 items per_page. I have 21 items in the query, and if I check with $query->count() i get 21 items as expected.
So the result should be two pages, first with 12 items, and second with 9 items.
But when i execute the pagination i get 2 pages. First with 8 items, second with 4 items.
Where can be the error?
If it can help, if i delete "rows_offset" and "rows_limit" i get the expected result, 21 items. Same ocurs if i set a elevate "per_page" number, for example 500.
Also i check the values of $pagination->offset and $pagination->per_page, and it seems be ok (0 and 12 in first page).
I can't see how you construct your query, but my guess is that it includes one-to-many or many-many relations.
However, in that case I would expect this behaviour with rows() and limit(), rows_limit() and rows_offset() was meant to exactly fix this problem, so I can't really explain this.
Enable profiling in the config, and enable profiling on your database connect, so you can see the queries that are generated. Perhaps that will tell you more.
Yes, i have relations one-to-many and many-many too in the query.
I have enabled the profiling, and this is my query without rows_offset and rows_limit (before pagination code):
SELECT `t0`.`id` AS `t0_c0`, `t0`.`idioma` AS `t0_c1`, `t0`.`nombre` AS `t0_c2`, `t0`.`entradilla` AS `t0_c3`, `t0`.`descripcion` AS `t0_c4`, `t0`.`slug` AS `t0_c5`, `t0`.`meta_titulo` AS `t0_c6`, `t0`.`meta_descripcion` AS `t0_c7`, `t0`.`producto_id` AS `t0_c8`, `t1`.`id` AS `t1_c0`, `t1`.`modelo` AS `t1_c1`, `t1`.`categoria_id` AS `t1_c2`, `t1`.`visible` AS `t1_c3`, `t1`.`precio` AS `t1_c4`, `t1`.`imagen_principal` AS `t1_c5`, `t1`.`permite_compra` AS `t1_c6`, `t1`.`permite_venta` AS `t1_c7`, `t1`.`permite_reparacion` AS `t1_c8`, `t1`.`permite_fabricacion` AS `t1_c9`, `t1`.`numero_serie` AS `t1_c10`, `t1`.`stock_minimo` AS `t1_c11`, `t1`.`notificar_stock_minimo` AS `t1_c12`, `t1`.`impuesto_id` AS `t1_c13`, `t1`.`fabricante_id` AS `t1_c14`, `t1`.`recargo_equivalencia` AS `t1_c15`, `t1`.`stock_control` AS `t1_c16`, `t1`.`precio_anterior` AS `t1_c17`, `t1`.`producto_padre_id` AS `t1_c18`, `t1`.`created_at` AS `t1_c19`, `t1`.`updated_at` AS `t1_c20`, `t2`.`id` AS `t2_c0`, `t2`.`left_id` AS `t2_c1`, `t2`.`right_id` AS `t2_c2`, `t2`.`tree_id` AS `t2_c3`, `t2`.`name` AS `t2_c4`, `t2`.`slug` AS `t2_c5`, `t2`.`valor` AS `t2_c6`, `t2`.`datos_extra` AS `t2_c7`, `t2`.`imagen` AS `t2_c8`, `t3_through`.`zona_id`, `t3_through`.`producto_id`, `t3`.`id` AS `t3_c0`, `t3`.`nombre` AS `t3_c1`, `t3`.`created_at` AS `t3_c2`, `t3`.`updated_at` AS `t3_c3`, `t4_through`.`categoria_id`, `t4_through`.`producto_id`, `t4`.`id` AS `t4_c0`, `t4`.`left_id` AS `t4_c1`, `t4`.`right_id` AS `t4_c2`, `t4`.`tree_id` AS `t4_c3`, `t4`.`name` AS `t4_c4`, `t4`.`slug` AS `t4_c5`, `t4`.`valor` AS `t4_c6`, `t4`.`datos_extra` AS `t4_c7`, `t4`.`imagen` AS `t4_c8` FROM `productos_i18n` AS `t0` LEFT JOIN `productos` AS `t1` ON (`t0`.`producto_id` = `t1`.`id`) LEFT JOIN `categorias` AS `t2` ON (`t1`.`categoria_id` = `t2`.`id`) LEFT JOIN `productos_zonas` AS `t3_through` ON (`t1`.`id` = `t3_through`.`producto_id`) LEFT JOIN `zonas` AS `t3` ON (`t3_through`.`zona_id` = `t3`.`id`) LEFT JOIN `productos_categorias` AS `t4_through` ON (`t1`.`id` = `t4_through`.`producto_id`) LEFT JOIN `categorias` AS `t4` ON (`t4_through`.`categoria_id` = `t4`.`id`) WHERE `t0`.`idioma` = 'es' AND `t1`.`visible` = 1 AND `t3`.`id` IN ('7') AND (`t1`.`categoria_id` IN ('5', '37', '38', '39', '40', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '41', '57', '58', '59', '60', '42', '43', '44', '45', '4'))
And this one is after (in page 1):
SELECT `t0`.`id` AS `t0_c0`, `t0`.`idioma` AS `t0_c1`, `t0`.`nombre` AS `t0_c2`, `t0`.`entradilla` AS `t0_c3`, `t0`.`descripcion` AS `t0_c4`, `t0`.`slug` AS `t0_c5`, `t0`.`meta_titulo` AS `t0_c6`, `t0`.`meta_descripcion` AS `t0_c7`, `t0`.`producto_id` AS `t0_c8`, `t1`.`id` AS `t1_c0`, `t1`.`modelo` AS `t1_c1`, `t1`.`categoria_id` AS `t1_c2`, `t1`.`visible` AS `t1_c3`, `t1`.`precio` AS `t1_c4`, `t1`.`imagen_principal` AS `t1_c5`, `t1`.`permite_compra` AS `t1_c6`, `t1`.`permite_venta` AS `t1_c7`, `t1`.`permite_reparacion` AS `t1_c8`, `t1`.`permite_fabricacion` AS `t1_c9`, `t1`.`numero_serie` AS `t1_c10`, `t1`.`stock_minimo` AS `t1_c11`, `t1`.`notificar_stock_minimo` AS `t1_c12`, `t1`.`impuesto_id` AS `t1_c13`, `t1`.`fabricante_id` AS `t1_c14`, `t1`.`recargo_equivalencia` AS `t1_c15`, `t1`.`stock_control` AS `t1_c16`, `t1`.`precio_anterior` AS `t1_c17`, `t1`.`producto_padre_id` AS `t1_c18`, `t1`.`created_at` AS `t1_c19`, `t1`.`updated_at` AS `t1_c20`, `t2`.`id` AS `t2_c0`, `t2`.`left_id` AS `t2_c1`, `t2`.`right_id` AS `t2_c2`, `t2`.`tree_id` AS `t2_c3`, `t2`.`name` AS `t2_c4`, `t2`.`slug` AS `t2_c5`, `t2`.`valor` AS `t2_c6`, `t2`.`datos_extra` AS `t2_c7`, `t2`.`imagen` AS `t2_c8`, `t3_through`.`zona_id`, `t3_through`.`producto_id`, `t3`.`id` AS `t3_c0`, `t3`.`nombre` AS `t3_c1`, `t3`.`created_at` AS `t3_c2`, `t3`.`updated_at` AS `t3_c3`, `t4_through`.`categoria_id`, `t4_through`.`producto_id`, `t4`.`id` AS `t4_c0`, `t4`.`left_id` AS `t4_c1`, `t4`.`right_id` AS `t4_c2`, `t4`.`tree_id` AS `t4_c3`, `t4`.`name` AS `t4_c4`, `t4`.`slug` AS `t4_c5`, `t4`.`valor` AS `t4_c6`, `t4`.`datos_extra` AS `t4_c7`, `t4`.`imagen` AS `t4_c8` FROM `productos_i18n` AS `t0` LEFT JOIN `productos` AS `t1` ON (`t0`.`producto_id` = `t1`.`id`) LEFT JOIN `categorias` AS `t2` ON (`t1`.`categoria_id` = `t2`.`id`) LEFT JOIN `productos_zonas` AS `t3_through` ON (`t1`.`id` = `t3_through`.`producto_id`) LEFT JOIN `zonas` AS `t3` ON (`t3_through`.`zona_id` = `t3`.`id`) LEFT JOIN `productos_categorias` AS `t4_through` ON (`t1`.`id` = `t4_through`.`producto_id`) LEFT JOIN `categorias` AS `t4` ON (`t4_through`.`categoria_id` = `t4`.`id`) WHERE `t0`.`idioma` = 'es' AND `t1`.`visible` = 1 AND `t3`.`id` IN ('7') AND (`t1`.`categoria_id` IN ('5', '37', '38', '39', '40', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '41', '57', '58', '59', '60', '42', '43', '44', '45', '4')) LIMIT 12 OFFSET 0
I see no diferences except in the LIMIT and OFFSET values.
If i delete related('categorias') in query it works fine.
As I wrote, the problem you have is with the relations.
Say you run query with parents P and children C, and you use rows_limit(6), and the result is:
row 1: P1 joined with C1
row 2: P1 joined with C2
row 3: P2 joined with C3
row 4: P2 joined with C4
row 5: P2 joined with C5
row 6: P2 joined with C6
meaning you get 6 records back (because of the rows limit), one parent joined with two children, one parent joined with four children.
Then the ORM will hydrate this result to ORM objects:
P1 related to (C1, C2)
P2 related to (C3, C4, C5, C6)
So instead of your expected 6 rows on your pagination page, you see only two (because you paginate on parent objects, and there are only two).
If you want 6 parents back, no matter how many children they have, you need to use limit() instead of rows_limit(). Using limit() will ensure you get the requested number of parent objects, no matter how many children there are. This means it is likely the SQL generated will return more than 6 rows, before hydration.
If you think you can paginate the combined result like you would to from a handcoded join, in other words print these lines in the pagination result:
P1 - C1
P1 - C2
P2 - C3
P2 - C4
P2 - C5
P2 - C6
the answer is "you can't, not with an ORM query", as it will always hydrate the result of the query (i.e. the flat rows in the result) back to ORM objects.
You can work around that by extracting the query from the ORM query builder:
which will bypass the ORM hydration, and give you an array of StdClass objects, each containing one row from the joined result, which you can paginate. Obviously for these objects you lose all ORM functionality.
Donate
