Shop optimization



  • Hi, my website is loading super hard and I can't find the exact reason why.
    As products I have 8000 but the first page loads them the hardest.
    On the first page I have only two categories of products.
    I saw that in ProductRepository this query does everything.
    How can I optimize it?

     /**
         * @param int $categoryId
         *
         * @return \Illuminate\Support\Collection
         */
        public function getAll($categoryId = null)
        {
            $params = request()->input();
    
            if (core()->getConfigData('catalog.products.storefront.products_per_page')) {
                $pages = explode(',', core()->getConfigData('catalog.products.storefront.products_per_page'));
    
                $perPage = isset($params['limit']) ? (! empty($params['limit']) ? $params['limit'] : 40) : current($pages);
            } else {
                $perPage = isset($params['limit']) && ! empty($params['limit']) ? $params['limit'] : 40;
            }
    
            $page = Paginator::resolveCurrentPage('page');
    
            $repository = app(ProductFlatRepository::class)->scopeQuery(function ($query) use ($params, $categoryId) {
                $channel = request()->get('channel') ?: (core()->getCurrentChannelCode() ?: core()->getDefaultChannelCode());
    
                $locale = request()->get('locale') ?: app()->getLocale();
    
                $qb = $query->distinct()
                    ->select('product_flat.*')
                    ->join('product_flat as variants', 'product_flat.id', '=', DB::raw('COALESCE(' . DB::getTablePrefix() . 'variants.parent_id, ' . DB::getTablePrefix() . 'variants.id)'))
                    ->leftJoin('product_categories', 'product_categories.product_id', '=', 'product_flat.product_id')
                    ->leftJoin('product_attribute_values', 'product_attribute_values.product_id', '=', 'variants.product_id')
                    ->where('product_flat.channel', $channel)
                    ->where('product_flat.locale', $locale)
                    ->whereNotNull('product_flat.url_key');
    
                if ($categoryId) {
                    $qb->where('product_categories.category_id', $categoryId);
                }
    
                if (is_null(request()->input('status'))) {
                    $qb->where('product_flat.status', 1);
                }
    
                if (is_null(request()->input('visible_individually'))) {
                    $qb->where('product_flat.visible_individually', 1);
                }
    
                if (isset($params['search'])) {
                    $qb->where('product_flat.name', 'like', '%' . urldecode($params['search']) . '%');
                }
    
                /* added for api as per the documentation */
                if (isset($params['name'])) {
                    $qb->where('product_flat.name', 'like', '%' . urldecode($params['name']) . '%');
                }
    
                /* added for api as per the documentation */
                if (isset($params['url_key'])) {
                    $qb->where('product_flat.url_key', 'like', '%' . urldecode($params['url_key']) . '%');
                }
                
                # sort direction
                $orderDirection = 'asc';
                if (isset($params['order']) && in_array($params['order'], ['desc', 'asc'])) {
                    $orderDirection = $params['order'];
                } else {
                    $sortOptions = $this->getDefaultSortByOption();
                    $orderDirection = ! empty($sortOptions) ? $sortOptions[1] : 'asc';
                }
    
                if (isset($params['sort'])) {
                    $this->checkSortAttributeAndGenerateQuery($qb, $params['sort'], $orderDirection);
                } else {
                    $sortOptions = $this->getDefaultSortByOption();
                    if (! empty($sortOptions)) {
                        $this->checkSortAttributeAndGenerateQuery($qb, $sortOptions[0], $orderDirection);
                    }
                }
    
                if ($priceFilter = request('price')) {
                    $priceRange = explode(',', $priceFilter);
                    if (count($priceRange) > 0) {
                        $qb->where('variants.min_price', '>=', core()->convertToBasePrice($priceRange[0]));
                        $qb->where('variants.min_price', '<=', core()->convertToBasePrice(end($priceRange)));
                    }
                }
    
                $attributeFilters = $this->attributeRepository
                    ->getProductDefaultAttributes(array_keys(
                        request()->except(['price'])
                    ));
    
                if (count($attributeFilters) > 0) {
                    $qb->where(function ($filterQuery) use ($attributeFilters) {
    
                        foreach ($attributeFilters as $attribute) {
                            $filterQuery->orWhere(function ($attributeQuery) use ($attribute) {
    
                                $column = DB::getTablePrefix() . 'product_attribute_values.' . ProductAttributeValueProxy::modelClass()::$attributeTypeFields[$attribute->type];
    
                                $filterInputValues = explode(',', request()->get($attribute->code));
    
                                # define the attribute we are filtering
                                $attributeQuery = $attributeQuery->where('product_attribute_values.attribute_id', $attribute->id);
    
                                # apply the filter values to the correct column for this type of attribute.
                                if ($attribute->type != 'price') {
    
                                    $attributeQuery->where(function ($attributeValueQuery) use ($column, $filterInputValues) {
                                        foreach ($filterInputValues as $filterValue) {
                                            if (! is_numeric($filterValue)) {
                                                continue;
                                            }
                                            $attributeValueQuery->orWhereRaw("find_in_set(?, {$column})", [$filterValue]);
                                        }
                                    });
    
                                } else {
                                    $attributeQuery->where($column, '>=', core()->convertToBasePrice(current($filterInputValues)))
                                        ->where($column, '<=', core()->convertToBasePrice(end($filterInputValues)));
                                }
                            });
                        }
    
                    });
    
                    # this is key! if a product has been filtered down to the same number of attributes that we filtered on,
                    # we know that it has matched all of the requested filters.
                    $qb->groupBy('variants.id');
                    $qb->havingRaw('COUNT(*) = ' . count($attributeFilters));
                }
    
                return $qb->groupBy('product_flat.id');
    
            });
    
            # apply scope query so we can fetch the raw sql and perform a count
            $repository->applyScope();
            $countQuery = "select count(*) as aggregate from ({$repository->model->toSql()}) c";
            $count = collect(DB::select($countQuery, $repository->model->getBindings()))->pluck('aggregate')->first();
    
            if ($count > 0) {
                $randomPage = false;
    
                if(isset($params['random']))
                {
                    $randomPage = rand(1, intval(ceil($count / $perPage)));
                }
                
                
                # apply a new scope query to limit results to one page
                $repository->scopeQuery(function ($query) use ($page, $perPage, $randomPage) {
                    if($randomPage)
                    {
                        $page = $randomPage;
                    }
    
                    return $query->forPage($page, $perPage);
                });
    
                # manually build the paginator
                $items = $repository->get();
            } else {
                $items = [];
            }
            
            $results = new LengthAwarePaginator($items, $count, $perPage, $page, [
                'path'  => request()->url(),
                'query' => request()->query(),
            ]);
    
            return $results;
        }
    


  • Issue not present here. The issue is due to the product_attribute_values table.


Log in to reply