Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by Mmiele » Mon Jul 06, 2020 10:42 pm

OpenCart version: Version 3.0.2.0

I've checked through a fair bit of posts in the forums but haven't been able to get a solution for being able to naturally sort
the text values for a product in a page in my site on loading of the page. The page I'm working in is used to show the back issues
for the client we work with. The intended functionality is showing the latest added issue, with earlier issues being sorted to the end.
It was functioning fine with using the model as the way to sort the items up until we hit model CUS100. Now that the model number is in
the 100 range, when the model sort is applied, CUS100 is being listed in with the CUS10 and CUS11 products. I know about changing the
default sort in the category.php file and to that end I've tried a few different solutions to this problem. The one that looked the most
promising was sorting by both the model and sort order for the product. The sort order for the products from CUS1-CUS99 is set to be 1, while
the CUS100 product is set to a sort order of 0. Running the query in our database returns the products in the correct sort order, with the
CUS100 product showing up first and then CUS99, CUS98, and so on. But when I tried to add the query into the category page, the page loaded
a completely different order for the products.

Here is an example of what I changed in the category.php file to try this out:

original section:

Code: Select all

  $data['sorts'][] = array(
				'text'  => $this->language->get('text_default'),
				'value' => 'p.model-DESC',
				'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '&sort=p.sort_order&order=DESC' . $url)
			);

changed section:

Code: Select all

   $data['sorts'][] = array(
				'text'  => $this->language->get('text_default'),
				'value' => 'p.sort_order,p.model-DESC',
				'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '&sort=p.sort_order,p.model&order=DESC' . $url)
			);

Wanted to post in the forum to ask if there is something I'm missing in getting this set up. I know that natural sort is a common sql problem
but a lot of the solutions that were presented to me look as though they will not work within the framework of OpenCart. Let me know if anyone
has sugestions to fix this issue.
Mmiele
Newbie
Posts
4
Joined
Fri Jul 03, 2020 5:04 am
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by netruxa » Tue Jul 07, 2020 4:43 am

if u wanna default sort set p.sort_order ASC and p.model DESC - go to
/catalog/model/catalog/product.php and change this line
$sql .= " ORDER BY p.sort_order";
to this
$sql .= " ORDER BY p.sort_order ASC, p.model DESC";
User avatar
netruxa
New member
Posts
20
Joined
Fri Dec 16, 2016 1:34 pm
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by letxobnav » Tue Jul 07, 2020 7:03 am

you could also add a field to the select which takes the substring of model after the "S":

Code: Select all

SUBSTRING_INDEX(p.model,'S',-1) as sort_model_index
and sort on that:

Code: Select all

ORDER BY sort_model_index DESC

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.

User avatar
letxobnav
Expert Member
Posts
3264
Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by Mmiele » Tue Jul 07, 2020 9:24 pm

Should have mentioned yesterday that the page where the back issues are loaded is a specific category that the issues can be added to. This is why I was editing the category file in the path: \catalog\controller\product\category.php. Since that looked to be the file that handled how the category page was being sorted on page load.

I've tried changing the product.php file using your suggestion for the path of: \catalog\model\catalog\product.php but after clearing the cache and refreshing the page it hasn't changed how the products are loaded in. This may be because the product page is for the general list of products in Opencart and the page I am working in is within the category section.

Which select statement am I adding the substring code to? Is there a particular file that has a select I should be changing? I can see how that would work out, but I'm not sure where the selects are being set up as the Opencart site is built.

Thank you for the responses so far! These are great suggestions for solutions.
Mmiele
Newbie
Posts
4
Joined
Fri Jul 03, 2020 5:04 am
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by letxobnav » Tue Jul 07, 2020 10:43 pm

The lists of products are selected in function getProducts in file catalog/model/catalog/product.php
There the whole select is constructed based on the passed variables in the data array.

Code: Select all

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id........
Then, based on whether a sort order was passed, the sort is set (by the user for instance) when it is one of the following:

Code: Select all

		$sort_data = array(
			'pd.name',
			'p.model',
			'p.quantity',
			'p.price',
			'rating',
			'p.sort_order',
			'p.date_added'
		);

If no sort is set (via $data['sort']), it defaults to sort_order:

Code: Select all

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} elseif ($data['sort'] == 'p.price') {
				$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}


sort and order for the category is set in catalog/controller/product/category.php

Code: Select all

		if (isset($this->request->get['sort'])) {
			$sort = $this->request->get['sort'];
		} else {
			$sort = 'p.sort_order';
		}

		if (isset($this->request->get['order'])) {
			$order = $this->request->get['order'];
		} else {
			$order = 'ASC';
		}


so you could add this to the query select:

Code: Select all

$sql = "SELECT p.product_id, SUBSTRING_INDEX(p.model,'S',-1) as sort_model_index, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id........
add sort_model_index to the allowed sort fields

Code: Select all

		$sort_data = array(
			'pd.name',
			'p.model',
			'p.quantity',
			'p.price',
			'rating',
			'p.sort_order',
			'p.date_added',
			'sort_model_index'
		);

and use the construct you had for a link:

Code: Select all

'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '&sort=sort_model_index&order=DESC' . $url)
that would set $data['sort'] to sort_model_index which would be allowed and used.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.

User avatar
letxobnav
Expert Member
Posts
3264
Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by Mmiele » Wed Jul 08, 2020 5:21 am

Thank you for the detailed response letxobnav! I followed the explanation and changed the sections outlined in your examples, but the page is not sorting on the sort_model_index. I've run the query that should be running within the page as it is being loaded and within my database it is bringing back the correct data in order. I don't believe this should have a major effect on the outcome, but I did add a call to convert the sort_model_index to an unsigned integer value. This was done because the field that I'm pulling model from is a text field and in order to sort it by the proper numerical index, I had to convert the value first. This means that my substring call looks like this:

Code: Select all

CAST(SUBSTRING_INDEX(p.model,'S',-1) as UNSIGNED) as sort_model_index,
I've changed the default sorting path in the category file to use sort_model_index as well and double checked to make sure all the areas are updated correctly.

Is there something extra I have to do to be able to cast as an unsigned integer? Or is Opencart stripping out that section of the sql call?
Mmiele
Newbie
Posts
4
Joined
Fri Jul 03, 2020 5:04 am
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by letxobnav » Wed Jul 08, 2020 7:38 am

at the end of the function getProducts you have this:

Code: Select all

		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

		return $product_data;
	}
that is where the query is executed and then all individual product data is retrieved before it is returned.
temporarily change that to:

Code: Select all

		$query = $this->db->query($sql);
		$this->log->write($sql); // write the sql to the OC error log
		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

		return $product_data;
	}
then run your page and check in your OC error log what the query is your page is actually generating and executing.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.

User avatar
letxobnav
Expert Member
Posts
3264
Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan
Top

Re: Having trouble implementing a natural sort for a page in my OpenCart site

Quote

Post by Mmiele » Wed Jul 08, 2020 10:46 pm

Thank you letxobnav, I checked the query and found that at the very end there was a section that was still ordering by the model number. There was a section of code that was defaulting the order by clause to be done by p.model, so I changed that to be sort_model_index and the page loaded correctly with the CUS100 at the front of the list.

Code: Select all

	if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
        $myDefault = "n";
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} elseif ($data['sort'] == 'p.price') {
				$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
			} else {
				$sql .= " ORDER BY sort_model_index";    //changed this from p.model to specify ordering by sort_model_index
        $myDefault = "y";
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}
I'll start working on changing the sorts I have set for the dropdowns next. This has been very helpful! I hope this chain of answers is helpful for others who may have run into this problem as well.
Mmiele
Newbie
Posts
4
Joined
Fri Jul 03, 2020 5:04 am
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: Semrush [Bot] and 112 guests