Author Topic: edit sort order on related products  (Read 5008 times)

digihome

  • Beginner
  • *
  • Posts: 2
edit sort order on related products
« on: August 25, 2011, 11:05:19 am »
Hi,

I am having som troubles with related products. Is it possible, without to much "hacking", to get the related products to show up in the order you place them in in the administration panel?

Right now, it just looks like it has some default ID sorting order... I have tried to change the SQL statement, but has not got it right yet...

Anybody knows how to do it?

digihome

  • Beginner
  • *
  • Posts: 2
Re: edit sort order on related products
« Reply #1 on: September 21, 2011, 16:00:47 pm »
Nobody?

blans

  • Jr. Member
  • **
  • Posts: 80
Re: edit sort order on related products
« Reply #2 on: November 16, 2011, 10:59:56 am »
I would like to do the same thing. I think it's up to us digihome...

When I find a solution I'll let you know.

Multilingual VM Webshop
Joomla 1.5.26
VM 1.1.9
Joomfish 2.1.7
Artio JoomSEF 3.8.2
CSVi VM 3.8.1

VM2 has the power of Magento and the usability of Opencart. I just can't get it to do what I managed to do with VM1 for so long...

medbrat

  • Beginner
  • *
  • Posts: 1
Re: edit sort order on related products
« Reply #3 on: January 19, 2012, 14:03:43 pm »
have someone to do it? I need it very urgently, help please!!  :-[

dmakris

  • Beginner
  • *
  • Posts: 1
Re: edit sort order on related products
« Reply #4 on: March 07, 2012, 00:27:36 am »
Hi,

I wanted to do the same thing and after searching a lot over the web i found no solution, so i decided to do it on my own.

Here what you have to do:

first you need to create a function in order to get the different product id's from the field related_products

run in your favorite sql the following:

Step 1:
Code: [Select]
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Step2:
edit : /modules/mod_related_products/mod_related_products.php

there is around line 40 where the sql query is being created like this

Code: [Select]
$q = "SELECT product_sku, related_products FROM #__{vm}_product,#__{vm}_product_relations ";
$q .= "WHERE #__{vm}_product_relations.product_id='$product_id' AND product_publish='Y' ";
$q .= "AND FIND_IN_SET(#__{vm}_product.product_id, REPLACE(related_products, '|', ',' )) LIMIT 0, $max_items";

just add the following lines to the query

Code: [Select]
$q .= "order by  field(#__{vm}_product.product_id,SPLIT_STR(#__{vm}_product_relations.related_products,'|',1),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',2),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',3),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',4)) ";
$q .= "LIMIT 0, $max_items";

the full query should look like this

Code: [Select]
$q = "SELECT product_sku, related_products FROM #__{vm}_product,#__{vm}_product_relations ";
$q .= "WHERE #__{vm}_product_relations.product_id='$product_id' AND product_publish='Y' ";
$q .= "AND FIND_IN_SET(#__{vm}_product.product_id, REPLACE(related_products, '|', ',' )) ";
$q .= "order by  field(#__{vm}_product.product_id,SPLIT_STR(#__{vm}_product_relations.related_products,'|',1),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',2),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',3),";
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',4)) ";
$q .= "LIMIT 0, $max_items";

i hope i helped you because for me was a pain not to have these products in the order i wanted.

let me know how it goes

Note: Just keep in mind that if you increase the $max_items from the module and say you ask for 5 related products you need to add one more line before LIMIT 0,$max_items :
$q .= "SPLIT_STR(#__{vm}_product_relations.related_products,'|',5)) ";
Mainly split_str splits the field according to | and get 1,2,3,4 as many fields are delimited by |.

So before changing the sql make sure how many products you need to show.

thanks

Webby25

  • Beginner
  • *
  • Posts: 8
Re: edit sort order on related products
« Reply #5 on: April 20, 2012, 09:27:38 am »
Hi dmakris,

what do you mean with "favorite sql"? In which file must I type in the code?

lecer

  • Beginner
  • *
  • Posts: 22
Re: edit sort order on related products
« Reply #6 on: October 15, 2013, 16:35:48 pm »
Somebody know how to do that on VM 1.1.9 please ?