1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **

[dennisg]

Here it is

[ruleragency]

Hello,
Same Problem Here.
After the update i cannot search,filter products or change page on back-end virtuemart products page.

Anyone figured it out?

PHP  7.4.12
Joomla:  Joomla! 3.9.23 Stable [ Amani ] 24-November-2020 15:00 GMT
Database Version:  5.5.5-10.3.17-MariaDB
VirtueMart 3.8.6 10373

[roby]

Same problem  I think Child but i don't have child category

[Jumbo!]

Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code Select Expand

                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code Select Expand

if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

[dennisg]

Thank you Jumbo! that did the trick!

Looking forward to the next release :-)

[ruleragency]

Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code Select Expand

                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code Select Expand

if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

[Cococoder]

Jumbo! Thanks it worked for me.

If it doesn't work maybe try logigng out the admin panel and log back in again?

[Jumbo!]

Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code Select Expand

                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code Select Expand

if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

Then you must have changed the wrong codes. Category filtering works fine now.

Modified file attached.

[EsSa55]

Thanks, Jumbo

Products now open in the backend.

However, Product Price, image(s), Category(ies) and Manufacturer are now empty.

TIA

[igormoor]

Work fine. Thanks.

This is permanent solution or it will be some update soon?

[ruleragency]

Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code Select Expand

                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code Select Expand

if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

Then you must have changed the wrong codes. Category filtering works fine now.

Modified file attached.

Thank you for the file. I uploaded the file in administrator/components/com_virtuemart/models, I cleared all browser history and cookies and I still have the same problem after the update.
Any ideas?

[aLioneL]

Thank you Jumbo !!!!!!!!!!!
...tu m'as sauvé 

[Jumbo!]

Thanks, Jumbo

Products now open in the backend.

However, Product Price, image(s), Category(ies) and Manufacturer are now empty.

TIA

That's a different issue. It appears that the database was not updated correctly during the update and the data were lost.

[shyntaxx]

Thank you very much that did the trick.

I'm not sure if its related but I have one more problem here that is when I click "New" on the Virtuemart product page I get a HTTP ERROR 500 after a little while.

I'm able to clone or edit though but unable to add a new product.

[vale_berto]

Hi Jumbo,
thank you very much .. now it is working.