pagination for api.

Hi guys I have two queries that would fetch records for me but I am using laravel paginator and I feel its making it slow, I am doing this query for api

/**
     * we would search the products 1 table
     * with all the request params posted
     * we would query only when the request 
     * params are passed.
     * @param  object $data This is the request param
     * @return eloquent collection
     */
    public function searchAdsInProducts1($data)
    {   
        $searchString = $data->get('searchStr');
        $categoryId = $data->get('productId');//categories_category_id ie. car, property etc.
        $subCategoryIds = $data->get('subCategoryId');//subcat
        $subSubCategoryIds = $data->get('subsubCategoryId');//subsubCategoryId
        $productsLanguage = $data->get('productsLang');//lang
        $page = $data->get('page');
        $numberPerPage = $data->get('numberperpage');

        $manfactureYearFrom = $data->get('manfactureYearFrom');
        $manfactureYearTo = $data->get('manfactureYearTo');
        $kmFrom = $data->get('kmFrom');
        $kmTo = $data->get('kmTo');
        $priceFrom = $data->get('priceFrom');
        $priceTo = $data->get('priceTo');
        $regionId = $data->get('regionId');
        $furnishedTypeId = $data->get('furnishedTypeId');
        $numberOfRoomsFrom = $data->get('numberOfRoomsFrom');
        $numberOfRoomsTo = $data->get('numberOfRoomsTo');
        $cityId = $data->get('cityId');
        $categoryAdvertiseTypeId = $data->get('categoryAdvertiseTypeId');
        $orderBy = $this->decideOrderBy($data);
        $time = $this->decideTime($data);

        if(($data->get('page') == 1) AND ($categoryId != 0)){

            $sticky = Product::categoriesCategoryId($categoryId)
                    ->where('companies', 1)
                    ->where('sticky', 1)
                    // ->subSubCategory($subSubCategoryIds)
                    // ->subCat($subCategoryIds)
                    ->categoryAdvertiseTypeId($categoryAdvertiseTypeId)
                    ->regionId($regionId)
                    ->furnishedTypeId($furnishedTypeId)
                    ->cityId($cityId)
                    // ->searchString($searchString)
                    ->km($kmFrom, $kmTo)
                    ->price($priceFrom, $priceTo)
                    ->numberOfRooms($numberOfRoomsFrom, $numberOfRoomsTo)
                    ->manufactureYear($manfactureYearFrom, $manfactureYearTo)
                    // ->language($productsLanguage)
                    ->orderBy('product_id', 'desc')
                    ->take(50)
                    ->get();
        }

        $ads = Product::categoriesCategoryId($categoryId)
                ->subSubCategory($subSubCategoryIds)
                ->subCat($subCategoryIds)
                ->categoryAdvertiseTypeId($categoryAdvertiseTypeId)
                ->regionId($regionId)
                ->furnishedTypeId($furnishedTypeId)
                ->cityId($cityId)
                ->searchString($searchString)
                ->km($kmFrom, $kmTo)
                ->price($priceFrom, $priceTo)
                ->numberOfRooms($numberOfRoomsFrom, $numberOfRoomsTo)
                ->manufactureYear($manfactureYearFrom, $manfactureYearTo)
                ->lastTwoMonths($categoryId)
                ->time($time)
                ->language($productsLanguage)
                ->orderBy($orderBy, 'desc')
                ->paginate(50, ['*'], 'page', $page);

        if($data->get('isAdsSupported') == 'true'){
            $paidAds = $this->getPaidAds($data);
        }

        if(($data->get('page') == 1) AND ($categoryId != 0)) {
            // $data = $ads;
            $data = $sticky->merge($ads);
        }else{
            $data = $ads;
        }

        return ['ads' => $data, 'paidAds' => $paidAds];
    }

When I see db log query it does SELECT count(*) as aggregate

Now I have million records and it would do count *, I feel this would make it slow, is there any other alternative to this for api ? Please suggest thank you.

bobbybouwmann

8 years ago

Level 88

You need to do a count to determine the total number of records (to know the last page number). I think you have two options here

One: Do the pagination without max numbers, only "next" and "previous". Note that this might work fine, but there is no way to go back from the first to the last page. You need to implement this yourself, but you can do that easily with limit and offset in your query.

Two: Do the count once and keep a session or database record that holds the count of the table. A query for that or reading a session is always faster than counting the whole table. With this option you need to implement your own pagination. Luckily for you that is pretty easy with Laravel since you can simply use the LengthAwarePaginator class ;)

Let me know if you need an example ;)

eddy1992

8 years ago

Level 6

Hi @bobbybouwmann wow thanks for that answer. It would be really helpful if you could provide me an example. I think the second options seems perfect for me. Thank you so much.

bobbybouwmann

8 years ago

Best Answer

Level 88

Here you go

// Get the ?page=1 from the url and default to page 1
$page = request()->get('page', 1);

// Items per page (maybe put this in a config file?)
$perPage = 50; 

// Calculate the offset based on the current page.
$offset = ($page * $perPage) - $perPage;

// Get the data (Collection)
$stickies = Product::categoriesCategoryId($categoryId)
    ->where('companies', 1)
    ->where('sticky', 1)
    ->offset($offset)
    ->limit($perPage)
    ->get();

// Fetch the session, if there is none we need to do a count of course and that will be put in the session
$total = session('stickies.count', Product::all()->count());

return new LengthAwarePaginator(
    $stickies->toArray(), // We need to pass an array here
    $total, 
    $perPage, // Items per page
    $page, // Current page
    [
        'path' => $request->url(),
        'query' => $request->query()
    ] // We need this so we can keep all old query parameters from the url
);

Note: I just realised that this will only do one query, but it needs to fetch all items of course to still determine the count once. So you can either do that earlier or in this bit of code as well!

Let me know if you have questions! Also let me know if this makes it faster!

eddy1992

8 years ago

Level 6

Thank you so much @bobbybouwmann , I think the issue is somewhere else for the performance.

Please or to participate in this conversation.