0

Laravel query builder needs to paginate from multiple join tables.

I've tried many ways to paginate this query. But nothing.

Controller.php

$ord_heds = DB::select('select d.family, d.category, d.product_img, d.ord_ref, m.min_qty, m.max_qty, t.qty, t.value, SUM(replace(d.value,",","")) as ordval, SUM(replace(d.qty,",","")) as Tord_qty, COUNT(d.qty) as no_of_items_ordered, (select COUNT(itemCode) as mcount from item_statuses where '.'s'.$siteG.' ="'.$site.'" and itemCode in (select itemCode FROM items WHERE category=d.category and brand=d.family)) as manCount, (select COUNT(itemCode) as mcount1 from item_statuses where '.'s'.$siteG.' ="'.$site.'" and itemCode in (select itemCode FROM items WHERE category=d.category and brand=d.family and itemCode in (select itemcode FROM ord_dets where ord_dets.site = "'.$ordSite.'"))) as ordManCount FROM ord_dets d JOIN ord__heds h ON d.ord_ref = h.id INNER JOIN min__maxes m ON d.category = m.category AND d.site = m.site AND d.scode = m.season INNER JOIN targets t ON d.category = t.category AND d.family = t.brand AND d.month = t.month AND d.year = t.year AND d.site = t.site WHERE h.month = "'.$date[1].'" AND h.year = "'.$date[0].'" AND h.site = "'.$ordSite.'" GROUP BY d.family, d.category, d.product_img, d.ord_ref, m.min_qty, m.max_qty, t.qty, t.value');

$pag = $ord_heds->paginate(10);

dd($pag);

Above code result an error 'FatalThrowableError Call to a member function paginate() on array'.

halfer
  • 19,824
  • 17
  • 99
  • 186
Nuwan Withanage
  • 393
  • 7
  • 19
  • Debug `$ord_heds` first. If it returns an array, you are getting a result, while `paginate` can be used with a DB query object NOT a result. Check your query and try to use query builder properly. – Shudhansh Shekhar May 15 '19 at 05:14
  • Thnakz @ Shudhansh Shekhar my result is this, array:16 [▼ 0 => {#342 ▶} 1 => {#348 ▶} 2 => {#350 ▶} 3 => {#341 ▶} 4 => {#353 ▶} 5 => {#349 ▶} 6 => {#355 ▶} 7 => {#356 ▶} 8 => {#357 ▶} 9 => {#358 ▶} 10 => {#359 ▶} 11 => {#360 ▶} 12 => {#361 ▶} 13 => {#362 ▶} 14 => {#363 ▶} 15 => {#364 ▶} ], then how can I return this as a DB query object – Nuwan Withanage May 15 '19 at 06:21
  • It is probably a good idea to line-wrap that query **in your code file**, and then reflect that same change here. It is not very readable on one line, and that will make understanding it hard for you, and reading it hard for readers. – halfer May 15 '19 at 21:42
  • As from the Laravel definition for your raw queries, " The select method will always return an array of results. Each result within the array will be a PHP stdClass object, allowing you to access the values of the result. " So, you just can't paginate by using the queries that you have tried. – Shudhansh Shekhar May 16 '19 at 03:53
  • Your code is too clumsy. You better change your queries in more query builder way rather than just using raw query. Also try to get query object. For example: For getting query i'll use `DB::table()->where()`. For getting first result i'll use `DB::table()->where()->first()` – Shudhansh Shekhar May 16 '19 at 03:56

0 Answers0