News:

Looking for documentation? Take a look on our wiki

Main Menu

[VM 3.0.4.0.99] Bug - Ambiguous column product_sku

Started by balai, September 26, 2018, 12:20:14 PM

Previous topic - Next topic

balai

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:
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:
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.
$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

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

QuoteYou 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

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

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 https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 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
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

balai

Quotewasn'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

Quote from: balai on September 26, 2018, 12:20:14 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.
$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.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

balai

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

I think you mean this line

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.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Milbo

Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

balai

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:
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

As I wrote to you, the model is using userstate. So you must change that yourself.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

balai


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?

Milbo

hm? you can set the state of the model yourself. Or is the data protected?
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/