Post by head_dunce » Fri Apr 19, 2019 10:19 pm

So I see a product has one sort order, but what happens when it's on more than one category? I won't want it in the same position in both categories.

I'm really confused as to why the sort order isn't part of the category instead.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Sat Apr 20, 2019 4:29 am

Ok, so I've dug around and it seems this question has come up quite a few times, but no one has a solution.

I've found these old things -
https://www.opencart.com/index.php?rout ... n_id=14137
https://www.youtube.com/watch?v=owyY7LSyYfI

So now I'm poking around in the code. Can anyone save me some time and tell me what file returns the sort order?

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Sat Apr 20, 2019 4:40 am

Humm, looks like -
/var/www/html/catalog/model/catalog/product.php
public function getProducts($data = array())

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Sat Apr 20, 2019 11:51 pm

it seems this question has come up quite a few times
in 2012 on youtube?

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by head_dunce » Sun Apr 21, 2019 11:21 pm

letxobnav wrote:
Sat Apr 20, 2019 11:51 pm
it seems this question has come up quite a few times
in 2012 on youtube?
Search and you will find more. Still no solution? Seems like major platform problem for big stores. I'll try and come up with some hacks, but it'd be nice to know if this is going to be fixed.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Mon Apr 22, 2019 4:50 pm

well, setting the manual sort order globally per product is already a pain, doing that per product per category is a multiplied pain.

My guess is that's why nobody bothered.

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by head_dunce » Mon Apr 22, 2019 9:54 pm

It should be a variable on the category, not on the product.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Tue Apr 23, 2019 3:13 am

Fixed this problem with a hack. Just add a meta tag to the category description with the sort order of the products. I've got jquery that lets me drag and drag items already via my other system, so I'll have to figure out the best way to export that info to OC.

As I mentioned before, OC is making me learn PHP, I'm usually using Perl. I couldn't seem to get one value from the sql query, so there's an unneeded foreach loop, but since it only ever loops once it's not a major problem. If someone knows the proper code, I'd like to see it to understand it. ---- No idea how to make extensions yet, so here's the code.

Code: Select all

// ######################################################################################################
// ## START: New sort code that looks for a meta tag in the section description to sort the products.
// ## Put in the category description via the admin panel source view. Note you must unview source view
// ## for it to save in the admin panel.
// ## <meta name="sortorder" content="21536,16146,16133">
// ######################################################################################################
		$sortflag = 0;
 		if ( (isset($data['sort']) && in_array($data['sort'], $sort_data) && ($data['sort'] == 'p.sort_order')) || !(isset($data['sort']) && in_array($data['sort'], $sort_data))   ){
				$description = '';
				$sortordersql = "select description from oc_category_description where category_id=".$data['filter_category_id']." limit 1";
				$query = $this->db->query($sortordersql);
				foreach ($query->rows as $result) {
					$description = $result['description'];
				}
				$pattern = '/&lt;meta.*?name=&quot;sortorder&quot;.*?content=&quot;(.*)?&quot;&gt;/i';
				$matches = array();
				$sortorder = '';
				if ( preg_match($pattern, $description, $matches) ){
					$sortorder = $matches[1];
				}
				if(!empty($sortorder)){
					$sql .= " ORDER BY IF(FIELD(p.product_id,$sortorder)=0,1,0),FIELD(p.product_id,$sortorder)";
					$sortflag = 1;
				}
		}
		if (!$sortflag) {
// ##################################################################################
// ## START: Original unmodified sorting code
// ##################################################################################
			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";
			}
// ##################################################################################
// ## END: Original unmodified sorting code
// ##################################################################################
		}
// ######################################################################################################
// ## END: New sort code that looks for a meta tag in the section description to sort the products.
// ######################################################################################################
Edit: code updated
Last edited by head_dunce on Tue Apr 23, 2019 10:06 pm, edited 2 times in total.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Tue Apr 23, 2019 8:44 am

You know, I should probably re-do the regex so that it's looking at a HTML comment tag instead of a meta tag. Not a big difference really though. Since I'm a PHP newbie I saw the function https://www.php.net/manual/en/function. ... a-tags.php and thought it would be useful, only to find out it only accepted files and not strings. So I just used a regex, but yeah, if I'm regex'n might as well just use a HTML comment tag to make everyone happy.

EDIT: Here is the code using a comment tag instead of a meta tag

Code: Select all

// ######################################################################################################
// ## START: New sort code that looks for a meta tag in the section description to sort the products.
// ## Put in the category description via the admin panel source view. Note you must unview source view
// ## for it to save in the admin panel.
// ## <!-- name="sortorder" content="21536,16146,16133" -->
// ######################################################################################################
		$sortflag = 0;
 		if ( (isset($data['sort']) && in_array($data['sort'], $sort_data) && ($data['sort'] == 'p.sort_order')) || !(isset($data['sort']) && in_array($data['sort'], $sort_data))   ){
				$description = '';
				$sortordersql = "select description from oc_category_description where category_id=".$data['filter_category_id']." limit 1";
				$query = $this->db->query($sortordersql);
				foreach ($query->rows as $result) {
					$description = $result['description'];
				}
				$pattern = '/&lt;!--.*?name=&quot;sortorder&quot;.*?content=&quot;(.*)?&quot;.*?--&gt;/i';
				$matches = array();
				$sortorder = '';
				if ( preg_match($pattern, $description, $matches) ){
					$sortorder = $matches[1];
				}
				if(!empty($sortorder)){
					$sql .= " ORDER BY IF(FIELD(p.product_id,$sortorder)=0,1,0),FIELD(p.product_id,$sortorder)";
					$sortflag = 1;
				}
		}
		if (!$sortflag) {
// ##################################################################################
// ## START: Original unmodified sorting code
// ##################################################################################
			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";
			}
// ##################################################################################
// ## END: Original unmodified sorting code
// ##################################################################################
		}
// ######################################################################################################
// ## END: New sort code that looks for a meta tag in the section description to sort the products.
// ######################################################################################################
Last edited by head_dunce on Wed Apr 24, 2019 2:16 am, edited 2 times in total.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Tue Apr 23, 2019 9:15 am

these are your product id's?

<meta name="sortorder" content="21536,16146,16133">

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by head_dunce » Tue Apr 23, 2019 9:26 am

letxobnav wrote:
Tue Apr 23, 2019 9:15 am
these are your product id's?

<meta name="sortorder" content="21536,16146,16133">
Yes, change "21536,16146,16133" to whatever id's you're using. It will sort the id's there first, skip any numbers that aren't in the category, and list whatever isn't listed in the sort order after.
Working example here (although this link will not be the same in a few days) -
http://carguygarage.org/index.php?route ... 5053_34518

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by letxobnav » Tue Apr 23, 2019 9:58 am

well, creative, innovative even.

Still, if you have the product_id's at hand, I would simply create a table with category_id and product sort order, import the product_id's and use those entries while doing the query sort.

category_id
24
product_sort
21536,16146,16133

category_id
46
product_sort
x,y,x

then you can later add a nice admin interface to it.

saves you a huge meta tag if your category gets larger.

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by head_dunce » Tue Apr 23, 2019 5:51 pm

Yes, this would be better stored in the database than in a meta tag or doc tag. This works for now and would be easy to convert to storing in the db later. Being that I'm new to OC and still just trying to fit my current Yahoo store into it, I'd rather not mess with the stock db at this point. Hopefully this idea can make it into a newer version of OC with a drag and drop front end to it.

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Thu Apr 25, 2019 5:33 am

Here's the code to make the subcategories sort the same way. So you can mix the subcategories and products in the same comment line in the description of the parent category.
/var/www/html/catalog/model/catalog/catalog.php
change this function

Code: Select all

	public function getCategories($parent_id = 0) {
		$sqlquery = "SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' ";

		$description = '';
		$sortordersql = "select description from oc_category_description where category_id=".$parent_id." limit 1";
		$query = $this->db->query($sortordersql);
		foreach ($query->rows as $result) {
				$description = $result['description'];
		}
		$pattern = '/&lt;!--.*?name=&quot;sortorder&quot;.*?content=&quot;(.*)?&quot;.*?--&gt;/i';
		$matches = array();
		$sortorder = '';
		if ( preg_match($pattern, $description, $matches) ){
			$sortorder = $matches[1];
		}
;
		if(!empty($sortorder)){
			$sqlquery .= " ORDER BY IF(FIELD(c.category_id,$sortorder)=0,1,0),FIELD(c.category_id,$sortorder)";
		} else {
			$sqlquery .= " ORDER BY c.sort_order, LCASE(cd.name)";
		}

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

		return $query->rows;
	}

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm

Post by head_dunce » Wed May 08, 2019 4:48 am

Ok, so I see now how this can be an extension -

Code: Select all

INSERT INTO `oc_event` (`code`, `trigger`, `action`, `status`) VALUES ('cgg_product_sort', 'catalog/model/catalog/product/getProducts/after', 'extension/module/cgg_product_sort/cggProductSort', 1);
/var/www/html/catalog/controller/extension/module/cgg_product_sort.php

Code: Select all

<?php
class ControllerExtensionModulecggProductSort extends Controller {
	public function cggProductSort(&$route, &$args, &$output) {

		ob_start();
		var_dump($route);
		$dump_route = ob_get_clean();

		ob_start();
		var_dump($args);
		$dump_args = ob_get_clean();

		ob_start();
		var_dump($output);
		$dump_output = ob_get_clean();

		ob_start();
		var_dump($this);
		$data_dump = ob_get_clean();

		$data_dump = "ROUTE:\n$dump_route\n\nARGS:\n$dump_args\n\nOUTPUT:\n$dump_output";

		file_put_contents('/var/www/html/test.txt',  $data_dump );

	}
}
Then I can pickup that array and re-sort it before the page prints instead of hacking the stock code. Now I get it!

Jim
Middle Caicos, Turks and Caicos Islands


Active Member

Posts

Joined
Thu Apr 04, 2019 11:50 pm
Who is online

Users browsing this forum: No registered users and 8 guests