Hi guys, I'm wondering if anyone has any experience with the Sorting Order of linked products??
Using: PCI Pro v6.4.1a

Our issue is that we have a large number of products "linked" (not duplicated) into various categories. For example let's say that we are in the category "Birthday Party Supplies", we adjust the order of our products to our liking and that is fine. But then we go to another category where one of the linked products is also located, and we change the sort order there - it will change the sort order inside the First category, and throws everything off... I used phpMyAdmin to inspect the database and I see that each item has it's own sort order, but it is overwritten when you make a sort-order change in any category...

Has anyone implemented any system to make the sort order dependent on the Category each product is in??

Thanks for any help/advice!

Cheers,
C

Ok I've got it figured out!!
I dare to even suggest that this method be implemented IMMEDIATELY in CRE 6.5, if it hasn't been fixed already!

If you've read the post above you're probably having trouble with organizing multiple linked products... this is a little hack/mod that I've implemented successfully. If you are not familiar with phpMyAdmin or diving into PHP code you will need outside help. I will outline the steps required then expand and explain the code of each step.

Prerequisites:
Using CRE Loaded Pro 6.4.1a (I'm sure others will work as well)
Inside your site's configuration, Set configuration -> Product Listing Display Order = "SORT ORDER"

Here we go!
Step 1. Add a new column inside the products_to_categories table to hold the sorting values of each item in each category.
Using phpMyAdmin or a similar database tool, access the products_to_categories table.
Add a new column named "product_sort_order"
Type: INT(20)
Null: YES

Step 2. Add a new function inside of admin/includes/functions/general.php
Open the file admin/includes/functions/general.php.
Add this function to the bottom, or wherever you'd like - it's just a simple cPath return function that we are going to use to retrieve the Parent category that we are in... I'm sure CRE already has a built-in method but I like to make my own.




Step 3. Modify the admin/categories.php page to pass the Parent category ID into the update sort order SQL Query
We are going to create a hidden input field that contains the parent category id, and pass it to the SQL query once the user submits the update form.

Open admin/categories.php
Above the closing </form> tag insert this code:



Step 4. Modify the SQL Query to save our sort order into the new column we created in Step 1.
Open admin/categories.php if you closed it.
Look for: case 'sort': This is the beginning of the $action switch, which tells CRE what to do.

FIND THIS LINE: (Line: 79 for me)

Below it, add this new SQL Query:


Quick note:
We could probably remove the first SQL query now but I kept it in there just in-case some other parts of the site used it.
OK, so this will now save our sorting order into the product_sort_order column, and it will only update the one that corresponds to the category you are working in... NOW we have to change the front-end to use this new column for sorting instead of the original sort_order column inside the products table!

Step 5. Make the admin/categories.php page use the new sorting column.
OK, so CRE is still programmed to use the sort_order column, but we want it to use our new one!

Open admin/categories.php if you closed it.
Scroll down to line: 3126, or find the product query that retrieves the main product array...
This is what the full statement looks like (we are going to change it)


LOOK closely at the ORDER BY part... it says p.sort_order... BAD!!!

Add: p2c.product_sort_order to both SELECT statements so that they look like this:


Then change both the ORDER BY clauses to this:


NICE! Now when you browse a category inside the admin panel it will order the products using the new sort order column...
Now we have to change the front-end too!!

LAST STEP!
This is pretty much identical to Step 5 except that we will modify one of the front-end pages to use the product_sort_order column.
Open index.php (the main one in the root directory, not the admin directory!)

Find the first (of 4) product listing queries...
It looks like this:


I didn't paste the whole thing because it's too long.
OK, so inside of that SELECT statement you can see: p2c.categories_id. JUST AFTER THAT ADD: ", p2c.product_sort_order" (without quotes of course) ... so the main SELECT statement will look like this now:



Add the same to the other $listing_sql queries ... there are four of them that I could see in that page, they all start with $listing_sql =, but have different conditions before that... so just to be safe I changed them all.

Almost done!!
Now adjust the query that the index page is using as defined by the "SORT ORDER" Case....

Look for this line:


Change it to THIS:



WHOA !!! You're all done... rock on.

Rock on, f'rsure ... but given there are several ways of sorting the display of products (name, quantity, model etc) why would you want to lockdown the display of linked products in every category this way? Wouldn't you just end up having to micromanage every linked product to get it displaying in the order you wanted - sounds anxiety-producing. Or is this just for a shop running a small number of skus, eg 10 ?

Also, what effect does this have on dbase query times (which are already pretty slow in the default cart)?

Simon

I would say that if you are managing sort order's at all it is likely because the store owner some advantage to doing so.

That being the case, being able to actually manage the sort order of every product in every category accurately is a definite improvement over being able to manage the sort order of MOST of the products in MOST categories.

Still not sure why that 'feature' was added in the first place, without being keyed into the link system in such a way as to disable linking , another viable approach to this problem...

David

Really? Sure I guess there are a couple out there who'd want to have Widget A listed 1st in one category but 5th in another, but wouldn't this be a headache to administrate? Also, aren't categories a bit old skool anyway, what with keyword tags and layered navigation? Check magento / prestashop for ways of assisting the old category bind with cleaner, more end-user friendly ways of helping customers in finding stuff.

Sort order nav sounds to me like an arbitrary 'owner' driven set of criteria (unless of course the sort orders are being derived from customer preference, then that'd be impressive.)

Simon

Don't short sell owner criteria, Simon. There are some very sharp sellers out there who do understand their users and can really increase their conversion, given enough control over what happens on the page.

Categorization remains important. Not to say that the new techniques you mention aren't important or helpful, but a well balanced approach offering multiple approaches to finding the right product is more important than the selection of any one or two techniques.

I resisted adding product sort order to CRE myself a number of times, the demand is higher than you might think, though not nearly as high as "SEO URL's". But, if you're going to implement something silly, you may as well do it well.

David

Heheh, ok fair enough.

Simon

Whoa, that got you guys talking! haha

We implemented this because in our store we only sort products based on their Sort Order, personally I think that's the only way it should be done.. but that's just my opinion, haha. Also, for us - there are an absolute TON of linked products... so my clients were just losing their minds because they would link products, change the sort order in one category, but then it would change the sort order of that item in a different category!! Of course, at first they didn't check or realize that this was happening so they wasted about 3 hours going through all of the categories and sorting them. This way, everything stays exactly as you put it

Not too sure about the database hits though... so far so good!