Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Issue moved from http://drupal.org/node/1223758#comment-7029238
Steps for reproducing it:
- Create a content type and add 2 or more product reference fields
- 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.
Just a gut feeling that this issue #1898998: Error when creating product display view perhaps has the same troubling root.
Comments
Comment #1
jsacksick CreditAttribution: jsacksick commentedThis seems to be a duplicate of #1898998: Error when creating product display view, closing this issue
Comment #2
FranciscoLuz CreditAttribution: FranciscoLuz commented@jsacksick
How did you figure this is a duplicate? You said here http://drupal.org/node/1898998#comment-7038642 you could not reproduce that issue.
I agree with you that both issues might be of the same nature and therefore either one should be set to duplicate but unless you can effectively assert the cause of both claims you can not reasonably say that they are duplicates.
Comment #2.0
FranciscoLuz CreditAttribution: FranciscoLuz commentedAdded a new step for reproducing the issue.
Comment #3
bojanz CreditAttribution: bojanz commentedThere is a bug in the Views UI that sometimes causes fields to "forget" which relationship they belong to.
This causes the error you are seeing.
Edit your fields one by one, make sure they have the correct relationship selected, then resave their settings.
Comment #4.0
(not verified) CreditAttribution: commented..
Comment #5
boban_dj CreditAttribution: boban_dj commentedI just want to confirm bojanz suggestion, when you add one field at the time it works fine.
Comment #6
knalstaaf CreditAttribution: knalstaaf commentedIn addition to #3 (and #5):
In order to avoid this error I had to create a necessary relation, and then add my fields one by one instead of searching and checking them from the list and then click "Add". So I had to click "Add", pick one desired field and hit "Add and configure" - this over and over again.
Apparently Views freaked out when all the fields were added all at once.
It's not limited to Commerce Kickstart I believe. It may be a general issue.