How do I limit amount of products in each category?

imdevlper18 wrote:If you are on version 2.1.0.2 or lowere then

Go to admin - system - settings - store - edit - Default Items Per Page (Catalog)
Here you can set the number.


If you are on version 2.2.0.0 or higher then

Go to admin - extension - themes - store - edit - Default Items Per Page (Catalog)
Here you can set the number.

I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down

imdevlper18 wrote:

I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down

As i understand, but i said above based on this line.

See when query is made all products are not called from database, but only part of it are called. So slowing things down is never going to happen. If you are showing limited number of products per page to your customers.

imdevlper18 wrote:If you show limited products per page, then page will load fast.
You can follow instructions mentioned in first post.

chongshengdz wrote:it is not about products per page.
it is about not to select all the products from database per category.

pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

labeshops wrote:

chongshengdz wrote:it is not about products per page.
it is about not to select all the products from database per category.

Just wondering why you would limit your sales this way?

If you don't want to show more than 100 products per category, maybe you should rethink your categories creating more of them or perhaps divide your products by different niches across multiple stores using multistores. I mean why add more products if you aren't going to let a customer see and buy them??

chongshengdz wrote:

pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

please read my question twice before answering my question.
it is really not about setting products per page.
if it is so,i won't post a question here,it is really simple.
please read my question again below.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?

$pagination = new Pagination();
			$pagination->total = $product_total;
			$pagination->page = $page;
			$pagination->limit = $limit;
			$pagination->url = $this->url->link('product/category', 'path=' . $this->request->get['path'] . $url . '&page={page}');

			$data['pagination'] = $pagination->render();

			$data['results'] = sprintf($this->language->get('text_pagination'), ($product_total) ? (($page - 1) * $limit) + 1 : 0, ((($page - 1) * $limit) > ($product_total - $limit)) ? $product_total : ((($page - 1) * $limit) + $limit), $product_total, ceil($product_total / $limit));

			// http://googlewebmastercentral.blogspot.com/2011/09/pagination-with-relnext-and-relprev.html
			if ($page == 1) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'canonical');
			} elseif ($page == 2) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'prev');
			} else {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page - 1), true), 'prev');
			}

			if ($limit && ceil($product_total / $limit) > $page) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page + 1), true), 'next');
			}

straightlight wrote:

chongshengdz wrote:

pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

please read my question twice before answering my question.
it is really not about setting products per page.
if it is so,i won't post a question here,it is really simple.
please read my question again below.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?

What you might be referring to is the Pagination range that splits the product results into separate pages based on the over-limited quantity of products . The codes you are looking for by each categories would be located in the catalog/controller/product/category.php file where:

Code: Select all

$pagination = new Pagination();
			$pagination->total = $product_total;
			$pagination->page = $page;
			$pagination->limit = $limit;
			$pagination->url = $this->url->link('product/category', 'path=' . $this->request->get['path'] . $url . '&page={page}');

			$data['pagination'] = $pagination->render();

			$data['results'] = sprintf($this->language->get('text_pagination'), ($product_total) ? (($page - 1) * $limit) + 1 : 0, ((($page - 1) * $limit) > ($product_total - $limit)) ? $product_total : ((($page - 1) * $limit) + $limit), $product_total, ceil($product_total / $limit));

			// http://googlewebmastercentral.blogspot.com/2011/09/pagination-with-relnext-and-relprev.html
			if ($page == 1) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'canonical');
			} elseif ($page == 2) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'prev');
			} else {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page - 1), true), 'prev');
			}

			if ($limit && ceil($product_total / $limit) > $page) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page + 1), true), 'next');
			}

is already handled and re-worked into the Opencart v2.2.0.0 release with the previous and next page sessions.

straightlight wrote:You can look for an extension on the site that would match your needs. If you are unable to find any results, you can always post a new topic in the commercial support forum to get this done as a custom job.

chongshengdz wrote: $pagination->total = $product_total = 1000; # is what i need,but the category page still loads slow.

$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

chongshengdz wrote: i need a better solution.
i need something like this select * from table order by product_id desc limit 1000;

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS tot ....
....
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

pprmkr wrote:

chongshengdz wrote: $pagination->total = $product_total = 1000; # is what i need,but the category page still loads slow.

1 quick query counts total records in table with given filter ( category, searchstring or tag ):

Code: Select all

$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

Returns 1 value

chongshengdz wrote: i need a better solution.
i need something like this select * from table order by product_id desc limit 1000;

Next query gets restricted amount of records with the same filter but with start and limit when needed.
For example:
- table holds 175000 records
- 5000 products in category
- limit / page is set to 15 ( default )
- start = 0 because page = 1

Code: Select all

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS tot ....
....
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

Returns 15 records

Queries can be found in catalog/model/catalog/product.php.

Try it yourself in phpMyAdmin:
- select database
- select tab SQL
- enter "SELECT COUNT(DISTINCT product_id) AS total FROM oc_product"
- study result
- Then enter SQL "SELECT * FROM oc_product LIMIT 0,15"
- study result

What did you see?

There are may tips out there regarding speeding up database. From MyISAM to InnoDB, adding indexes and more ...