Default product order VM 2.0.14 & 2.0.16

[frex]

Joomla 2.5.8. VM 2.0.14 installed. I got the following issue, so installed VM 2.0.16 in the hope that it may have been fixed, but still having the same issue.

I have a client who would like products available soonest to display first. I have therefore set the default product sort order to 'Pruduct Available Date' on the configuration page. But then no products are displayed. In debug I get the following error:

vmError: exeSortSearchListQuery Column 'product_available_date' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `jos_virtuemart_products_en_gb` as l JOIN `jos_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_categories_en_gb` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `jos_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `jos_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` LEFT OUTER JOIN `jos_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id` WHERE ( p.`published`="1" AND (p.`product_in_stock` - p.`product_ordered` >"0" OR children.`product_in_stock` - children.`product_ordered` > "0") AND `pc`.`virtuemart_category_id` = 7 AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "2" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY product_available_date ASC LIMIT 0, 9

I have tried setting the default sort order to various other things. It works fine for things like 'Product Name', but I get similar errors for 'Product Price' and 'Product In Stock'.


Can anyone help me sort this out as I really need to be able to show products in 'Available Date' order.

Thanks,

Pete

[clanessa]

Hi
I think the problem is that the column "product_available_date" used in the order clause is present in more than 1 table joined in the query.
You have to specify the table the column belongs to; for example `p`.`product_avaialble_date`

[Milbo]

hmm check function shopfunctions.php in BE/helpers, search for line 1416, use for 'product_available_date' this `p`.product_available_date

If someone tells me which fields also make problems. I tested most of them for the release, but seems not all.

[frex]

Hi Milbo,

Many thanks for that. Works perfect 

Thanks again,

Pete

[Leon van der Zanden]

Hi Milbo,

I have a similar problem, but changing the PHP product.php did not help:
vmError: exeSortSearchListQuery Unknown column 'pc.ordering' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `u2dvs_virtuemart_products_nl_nl` as l JOIN `u2dvs_virtuemart_products` AS p using (`virtuemart_product_id`) group by p.`virtuemart_product_id` ORDER BY `pc`.ordering DESC LIMIT 0, 10

The problem apears when trying to open the articles the first time after entering the backend. When I first open the categories and then click on the 'show articles' colomn no error appears. And, then the problem looks disapeared. But after logging off and on in the backend reproduces the problem...

What can be wrong?

[Milbo]

Please write your virtuemart version.

[Leon van der Zanden]

Joomla 2.5.8 and VM 2.0.16

[Milbo]

Sorry, I cannot reproduce it,... did you stored your config at least one time? or test
http://dev.virtuemart.net/attachments/download/549/com_virtuemart.2.0.16a_extract_first.targz.zip
http://dev.virtuemart.net/attachments/download/550/com_virtuemart.2.0.16a_extract_first.zip

[Leon van der Zanden]

I am now running 2.0.18a  but still have:
vmError: exeSortSearchListQuery Unknown column 'pc.ordering' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `u2dvs_virtuemart_products_nl_nl` as l JOIN `u2dvs_virtuemart_products` AS p using (`virtuemart_product_id`) group by p.`virtuemart_product_id` ORDER BY `pc`.ordering DESC LIMIT 0, 10

When clicking the products button.

EDIT: I switched towards debugging: at once all worked fine. I switched back and still everything now works fine...Strange!?

[makik]

I have the same problem.
Joomla 2.5.8 VM 2.0.18a

vmError: exeSortSearchListQuery Nieznana kolumna 'ordering' w order clause SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `j25_virtuemart_products_pl_pl` as l JOIN `j25_virtuemart_products` AS p using (`virtuemart_product_id`) group by p.`virtuemart_product_id` ORDER BY ordering DESC LIMIT 0, 20

With VM 2.0.14 is ok - upgrade to VM 2.0.18a - error

[leFlea]

Turning debug on and off worked for me too! Strange, but good!

I have the same problem.
Joomla 2.5.8 VM 2.0.18a

vmError: exeSortSearchListQuery Nieznana kolumna 'ordering' w order clause SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `j25_virtuemart_products_pl_pl` as l JOIN `j25_virtuemart_products` AS p using (`virtuemart_product_id`) group by p.`virtuemart_product_id` ORDER BY ordering DESC LIMIT 0, 20

With VM 2.0.14 is ok - upgrade to VM 2.0.18a - error

[frex]

I have now got products displaying by 'product_available_date' as default in both frontend and backend. But, the products are displayed ASC in frontend and DESC in backend. Is there a way to get them both to display ASC? I have tried a few things and can change the fontend to DESC (even though I don't need to!), but I can't find how to change the backend to ASC.

Also, more importantly. Now that I have started to add more products. In the backend, as soon as try and go to the second page of products in the product overview list, it switches back to ordering by product id instead of product available date. Is there any way to stop this?

[Gipa]

Hi Milbo

Using Joomla 2.5.9, Virtuemart 2.0.18a.

I'm having problems with blank pages when trying to view product detail pages.
When in debugging mode I get this error:

JDatabaseMySQLi::query: 1054 - Unknown column 'pc.ordering' in 'order clause' SQL=SELECT `l`.`virtuemart_product_id`, `l`.`product_name` FROM `stxph_virtuemart_products` as `p` JOIN `stxph_virtuemart_products_da_dk` as `l` using (`virtuemart_product_id`) JOIN `stxph_virtuemart_product_categories` as `pc` using (`virtuemart_product_id`) LEFT JOIN `stxph_virtuemart_product_shoppergroups` as `psgr` on (`psgr`.`virtuemart_product_id`=`l`.`virtuemart_product_id`) WHERE `virtuemart_category_id` = 33 and `slug` < "beckers-svanemaerket-traemaling-perfekt-vinduesmaling-halvblank-45-3-liter" AND ( psgr.`virtuemart_shoppergroup_id`= "1" OR psgr.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`= 1 ORDER BY `pc.ordering` DESC LIMIT 0,1


As you can see, I would like to order by the field "ordering".
I tried changing the ordering (including changing it to SKU, Price and Product Available Date), but I still get error messages every time.

I can send you the error messages for the other three if it helps you (you asked to know which other fields caused problems).
But I really need the "Ordering" order to work - could you please tell me what to do...?

I looked at /administrator/components/com_virtuemart/models/product.php, and line 380-384 says:
case 'pc.ordering':
               $orderBy = ' ORDER BY `pc`.`ordering` ';
               $joinCategory = TRUE;
               break;
... like it should.

/administrator/components/com_virtuemart/helpers/shopfunctions.php line 1413-1420 says:
      $filterArray = array('product_name', '`p`.created_on', '`p`.product_sku',
         'product_s_desc', 'product_desc',
            'category_name', 'category_description', 'mf_name',
         'product_price', 'product_special', 'product_sales', 'product_availability', '`p`.product_available_date',
         'product_height', 'product_width', 'product_length', 'product_lwh_uom',
         'product_weight', 'product_weight_uom', 'product_in_stock', 'low_stock_notification',
          '`p`.modified_on',
            'product_unit', 'product_packaging', '`p`.virtuemart_product_id', 'pc.ordering');

... like it should, I think?

I really hope you can help.     

[Gipa]

Ok, I solved it somehow...     

I packed up everything with Akeeba and set up a copy of the site with another webhost.
Then I went to view a category on the new site, and I was told that /components/com_virtuemart/views/productdetails/tmpl/default.php had an extra } in line 137 that needed to be removed. I did that, and now everything works!

Maybe it's a host problem or a PHP version problem. (I know that the first host has PHP 5.2, but I can't make them update that before the new site is ready. Don't want to kill the exisiting site by mistake before the new site is ready to take over.       )

Simply removing the } from the old site does nothing - so I think this blank page issue might be a PHP 5.2 problem? But I'm guessing...     

Maybe this info can help others...