1054 Unknown column 'ps.sku' in 'WHERE'

[itn-ms]

Hi all,

I don't know since when, but I get the error: "1054 Unknown column 'ps.sku' in 'WHERE'" when I try to search for a product.
Here is a screenshot of the Call Stack: https://imgur.com/a/AX9X937
When I go to Extensions: Database in the Joomla-Configuration it says for Phoca Cart that the Database-Version is 5.0.1 and the Manifest-Version is 5.0.2 - however it also says that there are no problems - the repair doesn't change anything.

Has anyone an idea where this issue is coming from?

My Joomla version is 5.3.0 and Phoca 5.0.2

Thanks you for your help

Marco

[Benno]

Hi,
How do you search? With the Phoca Cart Search Module (J4) or with the Phoca Cart Finder - Product Plugin (J5) or with a third-party extension like e.g. n3t PhocaCart Search Module ?

Kind regards,
Benno

[itn-ms]

Hi Benno,

I use the Phoca Cart Search Module for J5 which comes bundled in the Phoca Cart Modules Package (Joomla 5).
The exact version installed is 5.0.0Beta61.

[Jan]

Hi,

try you run n3t debug plugin on your site to see the whole SQL query where this problem occurs:

https://n3t.bitbucket.io/extension/n3t-debug/

And check if in your database, in your table #__phocacart_product_stock there is column sku:



If the columns are missing there, this is really strange, as such columns were added in version 3.5.4

administrator/components/com_phocacart/update/sql/mysql/3.5.4.sql

Jan

[itn-ms]

Hi Jan,

thanks for your advice.

I managed to "fix" it: When I turn on the option "Search Custom Fields" the error disappears. However I had only one Custom Field which was there for testing purposes, then removed it, deactivated the option and the error is coming again.
So as long as I have it activated the search seems to function normally - which is fine for me.

However I still installed n3t Debug; the SQL of the error is the following:

Code: Select all

SELECT a.id, a.image, a.unit_amount, a.unit_unit, a.sku, a.ean, a.upc, a.type, a.points_received, a.price_original, a.stock, a.stock_calculation, a.min_quantity, a.min_multiple_quantity, a.stockstatus_a_id, a.stockstatus_n_id, a.date, a.sales, a.featured, a.external_id, a.external_link, a.external_text, a.price, a.gift_types, m.id as manufacturerid, a.title AS title, a.alias AS alias, a.description AS description, m.title AS manufacturertitle, m.alias AS manufactureralias, (SELECT im.image FROM ke6sw_phocacart_product_images im WHERE im.product_id = a.id ORDER BY im.ordering LIMIT 1) as additional_image, GROUP_CONCAT(DISTINCT c.id) AS catid, GROUP_CONCAT(DISTINCT c.title) AS cattitle, GROUP_CONCAT(DISTINCT c.alias) AS catalias, a.catid AS preferred_catid, t.id as taxid, t.tax_rate as taxrate, t.calculation_type as taxcalculationtype, t.title AS taxtitle, t.tax_hide as taxhide, at.required AS attribute_required, MIN(ppg.price) as group_price, MAX(pptg.points_received) as group_points_received, AVG(r.rating) AS rating FROM ke6sw_phocacart_products AS a  LEFT JOIN ke6sw_phocacart_product_categories AS pc ON pc.product_id =  a.id  LEFT JOIN ke6sw_phocacart_categories AS c ON c.id = pc.category_id  LEFT JOIN ke6sw_phocacart_manufacturers AS m ON m.id = a.manufacturer_id  LEFT JOIN ke6sw_phocacart_attributes AS at ON a.id = at.product_id AND at.id > 0 AND at.required = 1  LEFT JOIN ke6sw_phocacart_item_groups AS ga ON a.id = ga.item_id AND ga.type = 3  LEFT JOIN ke6sw_phocacart_item_groups AS gc ON c.id = gc.item_id AND gc.type = 2  LEFT JOIN ke6sw_phocacart_reviews AS r ON a.id = r.product_id AND r.id > 0  LEFT JOIN ke6sw_phocacart_taxes AS t ON t.id = a.tax_id    LEFT JOIN ke6sw_phocacart_product_price_groups AS ppg ON a.id = ppg.product_id AND ppg.group_id IN (SELECT group_id FROM ke6sw_phocacart_item_groups WHERE item_id = a.id AND group_id IN (1) AND type = 3)  LEFT JOIN ke6sw_phocacart_product_point_groups AS pptg ON a.id = pptg.product_id AND pptg.group_id IN (SELECT group_id FROM ke6sw_phocacart_item_groups WHERE item_id = a.id AND group_id IN (1) AND type = 3) WHERE  a.published = 1 AND  c.published = 1 AND c.type IN (0, 1) AND  c.access IN (1, 5) AND  a.access IN (1, 5) AND  (ga.group_id IN (1) OR ga.group_id IS NULL) AND  (gc.group_id IN (1) OR gc.group_id IS NULL) AND (((a.title LIKE '%metall%' OR a.alias LIKE '%metall%' OR a.title_long LIKE '%metall%' OR a.metatitle LIKE '%metall%' OR a.metakey LIKE '%metall%' OR a.metadesc LIKE '%metall%' OR a.description LIKE '%metall%' OR a.sku LIKE '%metall%' OR a.ean LIKE '%metall%' OR ps.sku LIKE '%metall%' OR ps.ean LIKE '%metall%'))) GROUP BY a.id ORDER BY pc.ordering ASC LIMIT 9

It seems that the ps alias of ps.sku never gets defined - so it throws an error.
When I activate the option "Search Custom Fields" the ps alias gets defined:

Code: Select all

LEFT JOIN ke6sw_phocacart_product_stock AS ps ON a.id = ps.product_id 

and there's no problem, the rest of the SQL looks the same.

I also checked my database, the column sku is in #__phocacart_product_stock.

Do you have any idea why my system behaves like that?
I also setup a test system, where the search just functions normally without having Seach Custom Fields activated, so I still wondern why.

Best regards and thanks in advance
Marco

[Jan]

Hi, thank you for your extended info, I found the problem, now we are talking about it with Phoca Cart developers how to avoid this. So this will be fixed.

Thank you, Jan

[Jan]

It should be OK in newest Beta version:
https://github.com/PhocaCz/PhocaCart/re ... .3Beta.zip

If you can, please test and let me know.

Thank you, Jan

[itn-ms]

I tested the version you provided and it fixed the issue.
Thank you very much

Marco

[Jan]

Ok, great to hear it, thank you for testing.

Jan