Relationship on field_collection field (and adding a field from field_collection) results in views SQL syntax error

Calling this a duplicate of #1094260: Syntax error or access violation: 1064

Nope, I'm wrong. This is a good description of the issue I'm having.

If I create a view of nodes with have field_collection fields, and create relationships on any of them and then try to use fields within those field_collections, I get this error:

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 node_, node.created AS node_created, 'field_collection_item' AS field_data_fi' at line 1

Anyone have any luck with this? I am getting the same issue

This error gets weirder by the minute

I have 2 separate views and identical setup (except for field names) of two field collections (with 4 fields in them).

One works. The other throws this. 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 node_, node.created AS node_created, 'field_collection_item' AS field_data_fi' at line 1

The relationship to expose the containing fields is setup the same way.
The settings of the views is the same.

I read on a related post that you need to configure something in the content type and field and field collections setup to allow it to be picked up by views correctly.
But its especially strange that it works for one not the other.

I have upgraded this to major, as it seems to be a bit of a deal breaker for field collections. I am going to root around on d.org some more and check with IRC. Will post back findings. I tried the dev version field_collection http://drupal.org/project/field_collection 2012-May-28 still no joy.

So found a solution. You need to have a relationship and contextual filter setup. It seems a little odd, it only worked when I disabled and enabled it.
This gets rid of the PHP error. E.g. field collection = body copy and images. Which contains, 4 fields.

So setup the relationship to be content - body copy and images.

Then go to fields. Add - x 4 fields inside that field collection (now exposed via the relationship).

This might work. But didnt for me at first. So I removed the relationship (leaving the field collection fields in place) and re-added it and it worked again.

You can then filter this down to the relevant piece of content by using a contextual filter - e.g. node ID.

Don't be alarmed, when you do this it will remove the preview (unless you have specified a specific Node ID) Check your page and you should be good.
Suggest then clearing cache.
Turns out the aforementioned SQL state errors are generic debugging ones. So apologies for my earlier comment.

Downgraded to normal. Its strange but not as much as first thought.

Potentially helpful info for anyone fighting with this weird bug: Looking back over my settings, I found I had a Field Collection content type which, despite being set to use the Field Collection relationship, wasn't prepended with the (field_relationship_name) in the listings. Editting and saving it restored the relationship and removed the error.

Feels worryingly flakey

So, I can confirm this last comment, the "Relationship" field on the "Fields" item for any Field Collection field doesn't get saved at first. You can edit it, set the correct relationship and everything works fine.

it's just annoying to edit when you have many fields from field collections.

I can also confirm. I fixed it by simply opening each field and selecting "apply". This seems to workaround/resolve the issue for now.

Cheers,

CP