[solved] Unknown column 'pc.ordering' in 'where clause'

[TeleEnte]

For some reasons the VM Search always get the result: not found. I checked the bugs in administrator modus and this occured, I hope you can help, thank you!

        vmdebug self::$_jpConfig->lang de_de
        vmdebug vmTime: loadConfig db no: 0.0040121078491211
        vmdebug Start used Ram 10.75M
        vmdebug exeSortSearchListQuery error in class VirtueMartModelProduct sql: Var1:

        Unknown column 'pc.ordering' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS  l.`virtuemart_product_id` FROM `gxhi_virtuemart_products_de_de` as l JOIN `gxhi_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `gxhi_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
                  LEFT JOIN `gxhi_virtuemart_categories_de_de` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `gxhi_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `gxhi_virtuemart_product_manufacturers`.`virtuemart_product_id`
                  LEFT JOIN `gxhi_virtuemart_manufacturers_de_de` as m ON m.`virtuemart_manufacturer_id` = `gxhi_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id`  LEFT JOIN `gxhi_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id`  LEFT JOIN `gxhi_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `gxhi_virtuemart_product_shoppergroups`.`virtuemart_product_id`
                  LEFT  OUTER JOIN `gxhi_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `gxhi_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`product_name` LIKE "%holz%" OR `p`.created_on LIKE "%holz%" OR `p`.product_sku LIKE "%holz%" OR `product_s_desc` LIKE "%holz%" OR `product_desc` LIKE "%holz%" OR `category_name` LIKE "%holz%" OR `category_description` LIKE "%holz%" OR `mf_name` LIKE "%holz%" OR `product_price` LIKE "%holz%" OR `product_special` LIKE "%holz%" OR `product_sales` LIKE "%holz%" OR `product_availability` LIKE "%holz%" OR `p`.product_available_date LIKE "%holz%" OR `product_length` LIKE "%holz%" OR `product_weight_uom` LIKE "%holz%" OR `p`.modified_on LIKE "%holz%" OR `product_unit` LIKE "%holz%" OR `p`.virtuemart_product_id LIKE "%holz%" OR `pc.ordering` LIKE "%holz%") AND  p.`published`="1"  AND  ( s.`virtuemart_shoppergroup_id`= "2"  OR s.`virtuemart_shoppergroup_id` IS NULL  ) )  group by p.`virtuemart_product_id`  ORDER BY `p`.modified_on ASC LIMIT 0, 10

        vmdebug $this->category
        vmdebug vmTime: VirtuemartControllerCategory Finished task : 0.35025191307068
        vmdebug End used Ram 19.75M
        vmdebug Peak memory peak 19.75M

        vmError: exeSortSearchListQuery Unknown column 'pc.ordering' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `gxhi_virtuemart_products_de_de` as l JOIN `gxhi_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `gxhi_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `gxhi_virtuemart_categories_de_de` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `gxhi_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `gxhi_virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `gxhi_virtuemart_manufacturers_de_de` as m ON m.`virtuemart_manufacturer_id` = `gxhi_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `gxhi_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` LEFT JOIN `gxhi_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `gxhi_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `gxhi_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `gxhi_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`product_name` LIKE "%holz%" OR `p`.created_on LIKE "%holz%" OR `p`.product_sku LIKE "%holz%" OR `product_s_desc` LIKE "%holz%" OR `product_desc` LIKE "%holz%" OR `category_name` LIKE "%holz%" OR `category_description` LIKE "%holz%" OR `mf_name` LIKE "%holz%" OR `product_price` LIKE "%holz%" OR `product_special` LIKE "%holz%" OR `product_sales` LIKE "%holz%" OR `product_availability` LIKE "%holz%" OR `p`.product_available_date LIKE "%holz%" OR `product_length` LIKE "%holz%" OR `product_weight_uom` LIKE "%holz%" OR `p`.modified_on LIKE "%holz%" OR `product_unit` LIKE "%holz%" OR `p`.virtuemart_product_id LIKE "%holz%" OR `pc.ordering` LIKE "%holz%") AND p.`published`="1" AND ( s.`virtuemart_shoppergroup_id`= "2" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY `p`.modified_on ASC LIMIT 0, 10

[Milbo]

Tja ohne Versionsangabe,.... 2.0.20b? Was macht die 2.0.21c?

[Blowfish]

Hi,

leider habe ich dasselbe Problem im Debug Modus:
JDatabaseMySQLi::query: 1054 - Unknown column 'pc.ordering' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `z6l2y_virtuemart_products_de_de` as l JOIN `z6l2y_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `z6l2y_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `z6l2y_virtuemart_categories_de_de` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `z6l2y_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `z6l2y_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `z6l2y_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `z6l2y_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`pc.ordering` LIKE "%dasd%") AND p.`published`="1" AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` ASC LIMIT 0, 20

Ohne Debug Modus kommt einfach nichts zurück.
Habe die aktuelle Version 2.0.24c. Hatte mit der 2.0.24 dasselbe Problem.
Search Plugin und Search Modul sind 2.0.18 aus dem aktuellsten aio Package für das 2.0.24c release.

Die Tabelle enthält aber das Feld:
+------------------------+----------------------+------+-----+---------+----------------+
| Field                  | Type                 | Null | Key | Default | Extra          |
+------------------------+----------------------+------+-----+---------+----------------+
| id                     | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| virtuemart_product_id  | int(1) unsigned      | NO   | MUL | 0       |                |
| virtuemart_category_id | smallint(1) unsigned | NO   |     | 0       |                |
| ordering               | int(11)              | NO   |     | 0       |                |
+------------------------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


Wenn ich das Statement direkt in mysql ausführe meint er dennoch, dass er die Spalte nicht kennt;
mysql> SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `z6l2y_virtuemart_products_de_de` as l JOIN `z6l2y_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `z6l2y_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `z6l2y_virtuemart_categories_de_de` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `z6l2y_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `z6l2y_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `z6l2y_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `z6l2y_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`pc.ordering` LIKE "%dasd%") AND p.`published`="1" AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` ASC LIMIT 0, 20;
ERROR 1054 (42S22): Unknown column 'pc.ordering' in 'where clause'


Was kann ich tun?

[Milbo]

Vielleicht den Table updater ausführen? unter tools?
(maybe use the tableupdater)

[Blowfish]

I used the tool:

"Installiere Tabellen
oder falls notwendig
aktualisiere diese "

in vm admin. But it doesn´t help. Search is not working...

[Milbo]

die letzte version laden 2.0.25g bzw 2.0.26, drüber installieren und dann in die config, ordering auswählen. speichern. Dann sollte es gehen. Deutsches forum ist hier http://forum.virtuemart.de/forum.php

[Blowfish]

Leider auch mit der 2.0.26 kein Erfolg...

VM Version: 2.0.26
Produktsuche: 2.0.18a

Habe das aktuellste 2.0.26 Package inkl. dem aio installiert.
Habe im Konfiguration --> Konfiguration --> Einstellung Produktsortierung  gespeichert und auch die standard Werte umgestellt.

Leider ohne Erfolg. Weitere Ideen?

[Blowfish]

After updating VM again with the latest Version 2.0.26 and remove of the Productsearch module before update - now it works!

Thanks Milbo for Support!

[VopoloS]

You have an error somewhere in the VM code
If you sort by name and there are subcategories, this bug appears. If you change the sort order of the category, the error disappears.
VM version 3.2.4
Screenshot: http://prntscr.com/goc7aw

[Milbo]

because you did not set the standard field as available field.