Error when creating product display view

This issue here #1909046: SQL Query syntax error when adding a Content: Product variations field into a view may or may not be related.

I just tried to reproduce the issue following the steps in #1 and everything went fine, copy/paste your views export here and I'll try to reproduce your issue, I can't do anything if I can't reproduce it.

Steps for reproducing it:

• Create a new content view
• Add a Relationship: Content: Referenced product
• Add a field Content: Product variations

// The Error Message you will get right after finishing configuring the Content: Product variations field.
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS commerce_product_field_data_field_product_, node.created AS node_created, '' ' at line 1"

// View SQL Query
SELECT commerce_product_field_data_field_product. AS commerce_product_field_data_field_product_, node.created AS node_created, '' AS field_data_field_product__entity_type
FROM 
node node
LEFT JOIN field_data_field_product field_data_field_product ON node.nid = field_data_field_product.entity_id AND (field_data_field_product.entity_type = 'node' AND field_data_field_product.deleted = '0')
LEFT JOIN commerce_product commerce_product_field_data_field_product ON field_data_field_product.field_product_product_id = commerce_product_field_data_field_product.product_id
WHERE (( (node.status = '1') AND (node.type IN  ('MY_CONTENT_TYPE')) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

/** 
  * I think I can see where the problem is but do not know how to fix it.
  *
  * By doing a manual MYSQL query and changing the following:
  */
//From
SELECT commerce_product_field_data_field_product. AS commerce_product_field_data_field_product_, ...

// To
SELECT commerce_product_field_data_field_product.product_id  AS commerce_product_field_data_field_product_product_id, ...

// The Query results are returned without any errors.

I just tried again, you don't need the relationship to add the product variations field, the error appears when selecting a relationship for that field indeed, but it's not needed... Try without the relationship and it should work.

Right, I see.

In my scenario I have a content type that has 2 product reference fields. I have to create 2 relationships in order to tell product variations field which one it should load.

You are right though, having only one product reference field into the content type and setting the variation field into views without a relationship works.

But having more than one product reference field into the content type and setting the variation field into views without a relationship WONT work either.

You MUST have only one product reference field into your content type other wise it WONT work.

Looks like you found your answers.

The original issue sounds odd because it mentions using "Product Display" as the base which is our Search API index, which doesn't have or support relationships. The right base table to use in that case would be "Node".