PRODUCTS Sorting in summary doesn't work

[artesse]

Hello!

When I set sorting option at any another value except Random, I see this error:

SELECT c.* FROM cms_module_products c INNER JOIN cms_module_products_product_categories cc ON cc.product_id = c.id INNER JOIN cms_module_products_categories cs ON cs.id = cc.category_id WHERE c.status = 'published' AND cs.name IN ('moto') ORDER BY product_name in LIMIT 0, 5
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 5' at line 1

If sortby='random' it works. But I need sort by name or date...
Please, can you help?

[Coldman]

Please post your system info so we can help you.
Without any info about module versions etc etc we can't help you

[janb]

...and the whole tag you are using. {Products sortby="product_name" ... }
Btw. Try to add sortorder="asc"

JanB

[mfal55]

I get the same error.  I just upgraded to the latest version of Products (2. and upgraded CGExtensions to 1.18.7 and CGSimpleSmarty to 1.4.4.  I went to change the sort order within Product Preferences > General Settings to Status and it broke my page giving the error:

SELECT c.* FROM cms_module_products c INNER JOIN cms_module_products_product_categories cc ON cc.product_id = c.id INNER JOIN cms_module_products_categories cs ON cs.id = cc.category_id WHERE c.status = 'published' AND cs.name IN ('Containment') ORDER BY product_name in LIMIT 0, 100000
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 100000' at line 1

I went to put it back to Product Name and it was still broken... tried every other sort item - they all broke except for Random.  Random is the only one that works.

I'm calling the Products module via the Calendar module's Event Display Template with this code:

Code: Select all

{assign var=prodset1 value=$event.fields.Products1}
{Products category="$prodset1" detailpage="exhibits"}

with event.fields.Products1 being the name of a category.


I was using 1.6.5 and then upgraded to 1.7.1 in case that was causing the issue and I still get the same error.

Seems to be a bug in Products?

[janb]

Try this:

Code: Select all

{assign var=prodset1 value=$event.fields.Products1}
{Products category=$prodset1 detailpage="exhibits"}

JanB

[mfal55]

I wish I could say that did it, but no, doesn't work.

[janb]

Are you getting the same error with manual entered category?

[mfal55]

If I swap out the variable and put in an actual category name, I still get the same result - a broken page.

[janb]

Hi

I have done some tests and everything is working fine.
One rare thing from the error:

ORDER BY product_name in LIMIT 0, 100000

Should be ORDER BY product_name asc

I don't find anything in the code that should generate this query.

Have you tried to turn on debugging in config.php?
If not, turn it on and look for other MySQL errors in the output.

JanB

[mfal55]

I turned debug on... must be honest in saying I don't know what I'm looking at here.  There are a number of notices, referencing other plugins that all work fine normally.  I still get the same:

SELECT c.* FROM cms_module_products c INNER JOIN cms_module_products_product_categories cc ON cc.product_id = c.id INNER JOIN cms_module_products_categories cs ON cs.id = cc.category_id WHERE c.status = 'published' AND cs.name IN ('Containment') ORDER BY product_name in LIMIT 0, 100000
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 100000' at line 1

These are some of the notices:

Notice: Constant MEDIACENTER_PREF_NEWMEDIACENTER_TEMPLATE already defined in /Applications/MAMP/htdocs/clg/modules/MediaCenter/MediaCenter.module.php on line 39

Notice: Undefined variable: imDimensions in /Applications/MAMP/htdocs/clg/plugins/function.sectionimage.php on line 90

Notice: Undefined variable: sectiontitle in /Applications/MAMP/htdocs/clg/plugins/function.sectiontitle.php on line 47
2010 Exhibits and News

Notice: Undefined index: installed in /Applications/MAMP/htdocs/clg/plugins/prefilter.precompilefunc.php on line 77

Notice: Undefined index: installed in /Applications/MAMP/htdocs/clg/plugins/postfilter.postcompilefunc.php on line 77

Notice: Undefined index: installed in /Applications/MAMP/htdocs/clg/plugins/prefilter.precompilefunc.php on line 77

Notice: Undefined index: installed in /Applications/MAMP/htdocs/clg/plugins/postfilter.postcompilefunc.php on line 77

The weird thing is that now within the Calendar detail template, I can't get it to display correctly at all... even with the sort set to Random.  It works with it set at Random if I place it in a regular site template or on a page (but still breaking with any sort setting other than Random).  I get this fatal error when set to Random when calling from the Calendar template as I originally posted it:

Fatal error: Call to undefined method stdClass::GetModulePath() in /Applications/MAMP/htdocs/clg/lib/autoloader.php on line 43

I have no idea if that's related at all... Let me know if I should look for something specific?

[janb]

..and no other MySQL errors?

What happens when you manually define sortby and sortorder?

Code: Select all

{Products category="Containment" detailpage="exhibits" sortby="product_name" sortorder="asc"}

..and maybe you should do the tests from a content page for now, just make sure eliminating other error sources.

JanB

[mfal55]

Ok, these are the results:

1. When the code is placed in a site template or page, with sortby and sortorder manually defined it works - in all cases, not just Random.  Not using the manually defined code results in a broken page... so something with the admin sort feature isn't working.

2. When I went back to input the same code into the Calendar Event template, the page breaks.  Placing it into an actual Calendar event, it brings as well. 

It's so strange because up until I upgraded, it worked within Calendar just fine.  What do you think is preventing it from working?

[mfal55]

I should also confirm, that's CG Calendar, not Calendar...

[janb]

Hi

I don't really know why.
Have you tried this with the default detail template and a default page template?

I have not tested this within CGCalendar but since you getting the error when running this on a content page as well i don't think it will make any difference.

I'm running exactly the same versions as you and encountering no problems at all.

JanB

[mfal55]

Thanks for your help.... yeah, I just put the call to products on a default detail page and it consistently breaks the page.    Not sure what the deal is, but thanks for your help.