VirtueMart Forum

VirtueMart 1.1.x [ Old version - no longer supported ] => Products, Prices, Tax and Categories VM 1.1 => Topic started by: digihome on August 25, 2011, 11:05:19 am

Title: edit sort order on related products
Post by: digihome 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?
Title: Re: edit sort order on related products
Post by: digihome on September 21, 2011, 16:00:47 pm
Nobody?
Title: Re: edit sort order on related products
Post by: blans 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.

Title: Re: edit sort order on related products
Post by: medbrat on January 19, 2012, 14:03:43 pm
have someone to do it? I need it very urgently, help please!!  :-[
Title: Re: edit sort order on related products
Post by: dmakris 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
Title: Re: edit sort order on related products
Post by: Webby25 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?
Title: Re: edit sort order on related products
Post by: lecer on October 15, 2013, 16:35:48 pm
Somebody know how to do that on VM 1.1.9 please ?