Views warning: Ambiguous query

I'm getting the same issue. It doesn't do it when the relationship is not added - so if I add the flexifield to the view it will display it as default ok. But if I want to individually add parts of the subfields by having a relationship through flexifield, then I get ambiguous query error.
It seems that whatever fields are appended to the view (I have type, nid, vid?) are not prepended with the table name as would be necessary. So the query is

SELECT nid, type, <tablename>.<someother query>..... FROM ...... WHERE ......

Hope this helps....

I have the same issue.
Fields are outputted empty.
With Relationships activated I get the "user warning: Column 'nid' in field (...)" error.

The patch mentioned in the Views bug #607418 doesn't appear to fix this problem in Flexifield

I was able to get some things working by forcing 'node.' to be appended to any fieldname that didn't have a tablename provided, but that's a bit kludgy.

Any progress on this? FlexiField is almost useless without possibility to use views on them. :-(

@ikeigenwijs: Did your website go live on time? How did you fix the issue with FlexiField and Views?

I have 'almost' finished website which needs to be live next week. Last issue is, that I desperately need to use FlexiField with Views [or any alternative solution].

Any clue?

Thanks!

totocool is correct - there are four tables in the database that are in play when using flexifield:

1. & 2. The node table and associated content type table that stores the cck data, including your flexifield field.
3. The flexifield data table - this is the "content type" you had to create in order to reference it when adding your flexifield. However, this flexifield "content type" is not really a node. The nid = nid of #1 and #2 above, and has no entry of its own in the node table.
4. The flexifield_items table. This is a curious table and I have no clue yet how it is used. There is no nid. The only field in this table is item_id. There is one row in this table for every row in the flexifield data table and the item_id, in fact, matches the vid in the flexifield data table.

The long and short is that you can't create a view with data in the "days off" table because it's not a real node and doesn't have a node table entry, as totocool pointed out. Nor can you filter on the content type node and create a relationship to the flexifield "node", because there is no node reference field in the flexifield "node". You could add the node_reference field to the flexifield content type, but then when a person is entering the content type, they have to also select the content type (the one they're creating) to each flexifield row they input. Rather confusing, to say the least.

The correct solution (based on what I've learned about Drupal thus far) is that the flexifield content type should be created as a real node, with its own node table entry and with a node reference linking it back to the content type using it. And this is going to have a ripple effect throughout the flexifield module because I'm guessing that the code currently does the table linking using nonstandard Drupal architecture. I'm willing to get into the guts of the module and fix this, but I am still fairly new to Drupal and have not figured out yet how to submit changes, so I would need help there.

Hope this clarifies, rather than confuses. For now, I'm using an "arbitrary php code" action to create a new, separate content type to which I'm writing the flexifield data after saving the node, with a node reference to content type holding the flexifield field, and will use that for creating my views. It's a quick fix that will get me where I need to be this week. :-)

Hope this clarifies rather than confuses.

Mama Goobs

Subscribing.

Although comment #8 says that the patch mentioned in the Views bug #607418 didn't work, I've noticed that a new patch has appeared in that bug, which does fix the problem for me. See http://drupal.org/node/607418#comment-2712770

Ok here's a solution. Unpack attached file to flexifield/views directory. Flush all caches ( or go to modules page ).
Create view based on node. Add flexifield relationship. Now you can add subfield ( remember to use relationship ) to display.
Works with normal fields and imagefields. Haven't tested with multi-subfields.
The disadvantage of this patch ( and probably any possible patch for this problem ) is that cck type used in flexifield can't be anymore used independently ( so must be used only as flexifield subtype ).

The problem was a little more complicated then only invalid nid column ( patch in comment #14 ).
Field handler from CCK adds 3 additional fields to query.
- node.nid ( required for optional link )
- node.type ( required to decide which table it should use ( the same field name can exist in multiple cck types ) )
- node.vid ( required for multi-fields )

The first problem is that by default views try to link those additional fields through relationship. Flexifield uses table flexifield_items for relationship. Views module require that there is 1-1 connection between node and flexifield_items. Due to nature of flexifields it's not possible to have 1-1 connection. We could force 1-1 by requiring that cck type can be used only in one flexifield field. Then adding some linking information to Views would solve problem with sql query.
So node.nid would have correct value, but not node.type. By definition node.type = cck type from main type. But cck field handler requires the name of cck type with our subfield. So again we have a problem. Also node.vid wouldn't be taken from correct table ( it should be taken from table with subfield ).

The solution proposed in attached files uses new field handler ( based on cck field handler ). It takes node.nid from node, creates fake type field ( with name of table containg subfield ), and takes vid from table with subfield.
The handler is attached to all cck types used in flexifield.

I hope it will work for you :).

New version with support for filefield-data field. To use simply unpack to flexifield/views directory.

Hi, I tried this and it got rid of the error message and worked for me... good work!

However, it would also be nice to have the ability to group on multiple values and to specify which values to show, as the regular flexifield display does. As it is, it displays each value as a separate row which unfortunately doesn't help in my case.

Still...I can appreciate the progress!

You can select which value to display by choosing proper delta in relationship. It can be also used to "simulate" grouping if the number of values is lower than 10 ( you need to enter relationships with delta from 1 to 10 - it's a pain in ... , but it works ).

Views handler is responsible for managing multiple values in relationship ( like grouping ), so it's really request for enhancement in Views module.

OK, thanks for the explanation -- I didn't notice the delta option -- sorry!

I tried your solution and it worked, as long as I only want to display one delta value. If I add two different delta value relationships, it only displays the value for the first delta value even though I have separate fields in the view relating to each delta value. So, the field pointing to the second delta value relationship displays a duplicate of the first delta value instead of displaying the second delta value.

Are you sure that you are choosing correct relationship for each field ? Try to change name for each relationship.

To be sure I'll describe how i've created view with 4 relationships ( deltas from 1 to 4 ) showing images from subfield.

1. Create relationship with delta 1 , name main-1
...
4. Create relationship with delta 4 , name main-4
5. Create image field from subtype , attach to relationship main-1
...
8. Create image field from subtype , attach to relationship main-4

This approach works on my site.

Yes, I did that, but I'm using CCK text fields instead of image fields.

However, mine may be complicated by the fact that I already have additional relationships defined. Currently, I'm using the job search module, which requires relationships between the applicants resume node and the job listing node to display properly in the view, so perhaps this is causing my issues.

For testing purposes, I'll try it with a simpler view and let you know if I get different results.

Hi again,

Just tried it on a simple view and I do get the correct delta values for more than one relationship. So, the problem was apparently with my complicated view, not your code. :)

Cheers!