Author Topic: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku  (Read 700 times)

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
[VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« on: September 26, 2018, 12:20:14 pm »
I am loading the admin products view using a modal window in another view (custom field) and facing the following issues:

1. Products do not show.
Seems like the products query is using the parent window's custom_id param:
Code: [Select]
WHERE ( ( (pf.`virtuemart_custom_id`="34" ) ) )
2. After a search for a product (when a custom_id exists in the parent window), the following sql error comes out:
Code: [Select]
Column 'product_sku' in where clause is ambiguous
Looking at your query in the sortSearchListQuery function, it turns out that:
a. Some fields are included more than once in the where clause, with different format.
Code: [Select]
$this->valid_search_fields;
0 = "product_name"
1 = "product_sku"
2 = "`l`.`slug`"
3 = "product_s_desc"
4 = "`l`.`metadesc`"
6 = "`p`.product_sku"
8 = "product_desc"
9 = "category_name"
10 = "category_description"
11 = "mf_name"

b. Some of the fields do not contain the table name or the alias, becoming ambiguous.

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3466
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #1 on: September 26, 2018, 17:50:58 pm »
I think sortSearchListQuery is trying to use product> product_sku and customfield product_sku.
You may have the search for custom fields active?
Note that I did not check exactly, it's only that seems to be the most logical

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #2 on: September 27, 2018, 11:11:59 am »
Quote
You may have the search for custom fields active?
How can i check that?

In any case, i think that the fields should prefixed by their table and be included only once in the query

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #3 on: October 19, 2018, 15:11:19 pm »
Can somebody from the devs explain what's the purpose of using the virtuemart_custom_id in views where no products are displayed?

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8433
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #4 on: October 19, 2018, 17:12:57 pm »
wasn't it introduced in the model to provide customs filtering in searches etc

e.g.


      if (!empty($this->searchcustoms) or !empty($this->virtuemart_custom_id)) {
         $joinCustom = TRUE;

         if (!empty($this->searchcustoms)){
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 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
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #5 on: October 22, 2018, 10:54:38 am »
Quote
wasn't it introduced in the model to provide customs filtering in searches etc

Probably.
This should not limit it's scope in specific views in the front-end?

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9726
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #6 on: October 22, 2018, 14:33:33 pm »

Looking at your query in the sortSearchListQuery function, it turns out that:
a. Some fields are included more than once in the where clause, with different format.
Code: [Select]
$this->valid_search_fields;
0 = "product_name"
1 = "product_sku"
2 = "`l`.`slug`"
3 = "product_s_desc"
4 = "`l`.`metadesc`"
6 = "`p`.product_sku"
8 = "product_desc"
9 = "category_name"
10 = "category_description"
11 = "mf_name"


I could not reproduce it, but found some product_sku without the p. prefix. So I added them. Maybe it helps already to store the config again, because the form sets the field to search for. So when you stored this setting a long time ago with product_sku, storing again will set it to p.product_sku.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #7 on: October 23, 2018, 21:22:07 pm »
Thank you Max. If you have a patch i can test it.

Though my other problem is that i cannot load the products view in a modal (that's how a solution works), within a custom field view in the backend.
The products sql query joins the custom fields table, when a custom_id is detected.
Shouldn't this limited only to the front-end and in specific views?

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9726
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #8 on: October 24, 2018, 08:38:51 am »
I think you mean this line
Code: [Select]
if (!empty($this->searchcustoms) or !empty($this->virtuemart_custom_id)) {

There is a new filter in the BE, which filters products by the selected customfield, which is imho very handy.

Quote
1. Products do not show.
Seems like the products query is using the parent window's custom_id param:

I dont think so, imho I wrote it as "state", means that the model uses a request as long you do not change it. So in your case, you must just unset the custom_id for example using in the modal url "&virtuemart_custom_id=" or so.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9726
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

balai

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1343
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #10 on: November 01, 2018, 12:58:36 pm »
From my tests, the issue with the ambiguous and duplicate fields seems solved.

Though the 1st and most important issue is still there. The products in the modal are using the virtuemart_custom_id of the parent window
Some debug info:
Code: [Select]
echo 'view:', vRequest::getCmd('view');
//products
echo 'virtuemart_custom_id:', vRequest::getInt('virtuemart_custom_id');
//0
print_r($this->virtuemart_custom_id);
//Array ( [0] => 29 )

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9726
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
« Reply #11 on: November 01, 2018, 14:25:04 pm »
As I wrote to you, the model is using userstate. So you must change that yourself.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/