QTPro - stock table sort order

[Rosyweb]

I've been trying to find an answer to this for some time.  The QTPro add on has a stock table. 

 

This is in the file modules/qtpro_stock_table.php  

 

It appears to be ordering it by the table PRODUCTS_STOCK  and the field products_stock_attributes.

 

 

Adding different Order by to the other queries doesn't seem to change anything.

 

The problem with the products_stock_attributes field in the table PRODUCTS_STOCK is that it is a varchar field and so sorts lexically. 

 

So for example I am getting soirting results that show:

1000

before

53

 

In addition the field has 2 hyphens and a comma.  So I tried on the query a very complicated Order by to first separate the 2 bits of info in the products_stock_attributes field and order them separately (1st in my shop is colour, 2nd is size), then the Cast as Signed integer to order:

 

// Get the stocklevels
$products_stock_query=tep_db_query("SELECT products_stock_attributes, products_stock_quantity
                                  FROM " . TABLE_PRODUCTS_STOCK . "
                                  WHERE products_id=" . (int)$HTTP_GET_VARS['products_id'] ."     
                                  ORDER BY SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,'-')+1) ASC, SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,',')+3) ASC,
                                  CAST(products_stock_attributes as SIGNED INTEGER) ASC");

 

 

It doesn't work.  Cast as signed integer still leaves i.e.

1000

before

200

 

Any ideas QTPro experts out there?    Has anyone else tried to get the QTPro table sorting correctly?      I have the picklist in the right oder in pad_base. 

 

many thanks

 

[Rosyweb]

Wow - that took a long time but I found a way to sort it correctly in the end and will post it here in case anyone else tries to do this. 

 

So in modules/qtpro_stock_table.php, the sort order is determined by the $products_stock_query=tep_db_query  and not the other queries. 

 

The field I needed to get to sort in natural sort order not lexically is products_stock_attributes.  Applying the usual ways to convert a varchar field to natural sort order in the ORDER BY part of the query didn't seem to work.  i.e.

LPAD
CAST(field as SIGNED INTEGER) ASC
convert(field, decimal) ASC");

 

none of these worked because products_stock_attributes has hypens and commas in it as well.  They didn't even work if I got rid of the hypens, because I had to keep the comma as this separates the product attribute data (size, colour).  They didn't work when I split the field into 2 strings in the ORDER BY part of the query. 

 

So I split it into 2 strings in the SELECT part of the query and gave each alias names, then sorted it in ORDER BY by the alias names.  Added LPAD to these in the ORDER BY part to force natural sort order (this time it works as there are only numerics in the strings). 

 

Here's what worked for me:

 

// Get the stocklevels
$products_stock_query=tep_db_query("SELECT SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,',')-1, 4) AS attNum1,
                                  SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,',')+3, 4) AS attNum2, products_stock_attributes, products_stock_quantity
                                    FROM " . TABLE_PRODUCTS_STOCK . "
                                  WHERE products_id=" . (int)$HTTP_GET_VARS['products_id'] ."
                                  ORDER BY LPAD(attNum1, 10, 0) DESC, LPAD(attNum2, 10, 0) ASC");

[Rosyweb]

Ok a year later, but a slight amendment.  The 1st part of the split - creating attNum1 had never really worked correctly, so here's a version that does:

 

$products_stock_query=tep_db_query("SELECT SUBSTR(products_stock_attributes, 1, INSTR(products_stock_attributes,',')-1) AS attNum1,                                 SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,',')+3, 5) AS attNum2, products_stock_attributes, products_stock_quantity

                                                FROM " . TABLE_PRODUCTS_STOCK . "

                                                WHERE products_id=" . (int)$HTTP_GET_VARS['products_id'] ."

                                                ORDER BY attNum1 ASC, LPAD(attNum2, 10, 0) ASC");  

[Rosyweb]

Just adding this to bootstrap site - a number of years later!  I'm finding that LPAD on attNum1 is needed for it to work correctly in this version. 

$products_stock_query=tep_db_query("SELECT SUBSTR(products_stock_attributes, 1, INSTR(products_stock_attributes,',')-1) AS attNum1,
                                  SUBSTR(products_stock_attributes,INSTR(products_stock_attributes,',')+3, 5) AS attNum2, products_stock_attributes, products_stock_quantity 
  							      FROM products_stock 
								  WHERE products_id=" . (int)$_GET['products_id'] ."
								  ORDER BY LPAD(attNum1, 10, 0) DESC, LPAD(attNum2, 10, 0) ASC");