[SOLVED] 1054 - Unknown column 'ld.product_desc' error after search command

[luckysoft]

Joomla 3.4.4 with VM 3.0.10

After i upgraded my VM from 3.0.2 to the latest version the search option gives me an SQL error. When I leave the search box empty and hit the search button everything works fine but when i put something in the search box and hit the button the following SQL error is shown: (searched for the word aed)

Unknown column 'ld.product_desc' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` FROM `jos_virtuemart_products` as p INNER JOIN `jos_virtuemart_products_en_gb` as l using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `jos_virtuemart_product_manufacturers`.`virtuemart_product_id` WHERE ((`ld`.product_desc LIKE "%aed%" OR `l`.product_desc LIKE "%aed%") AND ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0") AND ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0") AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND `jos_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = 12 AND p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY product_name ASC LIMIT 0, 100

This is what i allready have done:

• Limited the avalable search fields in VM to only "Article description"
• Excecuted the "Install tables or if necessary update them" command from VM
• Tryed to install a thirth party search box but same result
• Changed the template to the default Vivid template (same error)

My shop is a single language shop, only English (en-GB) is selected.

The shop before the update : http://www.aedsupermarkt.nl/webshop/

The shop after the update : http://www.aedsupermarkt.nl/webshop-na-update/

I really hope that someone can point me in the right direction because this is driving me crazy.... 

[GJC Web Design]

I can't get this error even on a multi lang shop but it is something to do with your language config

the `ld` comes from

if($app->isSite() and VmConfig::$defaultLang!=VmConfig::$vmlang and !VmConfig::get('prodOnlyWLang',false)){
                  $filter_search[] =  '`ld`.'.$searchField . ' LIKE ' . $keywords_plural;

but I'm not sure what these configs are

do u have a default lang configured? (although I can't "unset" mine)

[Milbo]

You can force VM to switch off the fallback language with prodOnlyWLang, check http://docs.virtuemart.net/manual/general-concepts/206-hidden-configurations.html

But this shouldnt be needed. You say it is a single language shop. Enable the vmdebug in the config and check the reported languages. The question is, why this is different?

VmConfig::$defaultLang!=VmConfig::$vmlang and it is also not consistent, else it would try to join the other language table. I rewrote a bit the model, but still it should not happen, so I think the vmdebug output could explain the mystery

[luckysoft]

Thanks for your reply, i think we are getting somewere

The default language in VM is set to English (en-GB) there are no other languages set

My joomla language is set for Dutch, both website and admin I think the combination of those two (VM and Joomla) causes the problem. When i set the website language in Joomla to English the search function works fine!! But now al kinds of buttons and other things are translated to English

So i try to set both VM and Joomla website and Admin to Dutch but now all the categories and products are gone both on the website as in the virtuemart admin The search functions works now but has no results (as there are no products...)

With VMDebug enabled and all settings to dutch this is what i get: vmdebug $siteLang: nl-NL self::$vmlangSef: self::$_jpConfig->lang nl_nl DefLang nl_nl

With only VM set to en-GB this is what i get: vmdebug $siteLang: en-GB self::$vmlangSef: self::$_jpConfig->lang en_gb DefLang nl_nl

[GJC Web Design]

yes .. all correct -- what you should be doing is installing the Dutch VM language and translating and entering your products in VM Dutch

as u assume you now have a mixture and you will see all your #_virtuemart_products_nl-nl etc tables will be empty

try duplicating your vm en-GB or inserting the contents into the nl-nl tables

[luckysoft]

I just did

First I have exported all the data in the xxx_en_gb tables and then added dutch to the VM language config and removed the en_GB, after that I have changed en_gb to nl_nl in all the SQL dumps and imported them in the database again.

Now al the nl_nl database tables have data in it and the search seems to work oke !!

These are the database tables I have changed:

xxxxx_virtuemart_categories_nl_NL
xxxxx_virtuemart_manufacturercategories_nl_NL
xxxxx_virtuemart_manufacturers_nl_NL
xxxxx_virtuemart_paymentmethods_nl_NL
xxxxx_virtuemart_products_nl_NL
xxxxx_virtuemart_shipmentmethods_nl_NL
xxxxx_virtuemart_vendors_nl_NL

Looks like you pointed me in the right direction! Thank you !!