News:

Looking for documentation? Take a look on our wiki

Main Menu

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

Started by luckysoft, September 29, 2015, 10:03:29 AM

Previous topic - Next topic

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)

Quote
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....  :P

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)
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

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
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

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
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

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 !!