VirtueMart Forum

VirtueMart 2 + 3 => Virtuemart Development and bug reports => Topic started by: balai on September 26, 2018, 12:20:14 pm

Title: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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.
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Studio 42 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
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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?
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: GJC Web Design 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)){
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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?
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Milbo 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.
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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?
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Milbo 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.
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Milbo on October 25, 2018, 23:01:45 pm
Also to test http://dev.virtuemart.net/attachments/download/1146/com_virtuemart.3.4.3.9973_package_or_extract.zip
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai 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 )
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Milbo on November 01, 2018, 14:25:04 pm
As I wrote to you, the model is using userstate. So you must change that yourself.
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: balai on December 04, 2018, 12:49:10 pm

Code: [Select]
As I wrote to you, the model is using userstate. So you must change that yourself.
You cannot reset the state variable if that variable exists in the input. userstate first checks the variable in the $app->input
That leads me to do alchemies like clearing the variable in the $app->input, which may have implications to other parts of the code.

Why don't you restrict the custom_id filtering per view?
Title: Re: [VM 3.0.4.0.99] Bug - Ambiguous column product_sku
Post by: Milbo on December 05, 2018, 12:26:02 pm
hm? you can set the state of the model yourself. Or is the data protected?