laravel - 如何在数据库原始复杂查询中使用分页

Question

请帮助我将 laravel 的 Paginate 类与 DB 原始复杂查询一起使用...

尝试了一些技巧来获得 laravel paginate 的结果，但由于查询的复杂性，它给我带来了问题

$data = DB::select(DB::raw("SELECT DISTINCT c.id, c.name, c.active, c.image, c.created_at, (SELECT COUNT(*) FROM categories WHERE c.id=parent_id) AS subcat_count, (SELECT COUNT(*) FROM post_ads WHERE c.id=cat_id) AS postad_count FROM categories AS c LEFT OUTER JOIN categories AS subc ON c.id = subc.parent_id LEFT OUTER JOIN post_ads AS postadc ON c.id = postadc.cat_id WHERE c.parent_id=0 ORDER BY c.name ASC LIMIT 9"));

标签： laravel

Answer 1

您可以使用长度感知分页器进行分页，在您的控制器中这样做，

use Illuminate\Pagination\LengthAwarePaginator;
...
class StudentAvailablitiesController extends Controller
{
   public function index()
   {
       $collection = DB::select(DB::raw...)->get();
       $paginatedData = $collection->paginate(10);
   }

   public function paginate($perPage, $total = null, $page = null, $pageName = 'page')
   {
        $page = $page ?: LengthAwarePaginator::resolveCurrentPage($pageName);

        return new LengthAwarePaginator(
            $this->forPage($page, $perPage),
            $total ?: $this->count(),
            $perPage,
            $page,
            [
                'path' => LengthAwarePaginator::resolveCurrentPath(),
                'pageName' => $pageName,
            ]
        );
    }
}