[VM 3.0.4.0.99] Bug - Ambiguous column product_sku

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

Code Select Expand

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 Expand

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 Expand

$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]

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]

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)){

[balai]

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]

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 Expand

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

[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

Code Select Expand


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.

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.

[Milbo]

Also to test http://dev.virtuemart.net/attachments/download/1146/com_virtuemart.3.4.3.9973_package_or_extract.zip

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

Code Select Expand

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.

[balai]

Code Select Expand

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?