The Views relationships issue

I'm guessing LIKE LOWER('%j%') is no good ?? I might just have to escape the data with PHP first. This was an attempt at pgsql compatibility but I think it's stuffed.

I should have this fixed on the weekend along with a bunch of these other issues.

This has been fixed in beta2

Which version of Content Profile? The beta3 from january, or the dev version from yesterday? I hear the dev one has a few major bug fixes.

5. Create a View that displays all users with the Employee Role showing the userid and the firstname fields

I can't do this step. I have created a firstname and lastname field in the profile type, and even put in some data on user's edit page. But when I make a user view I don't see these fields as possible options.

If I do it with a node view it works, and I don't get any of the errors you described even with empty searches and clicking back from the results etc...

Mind you I'm not using windows.

ok now I get the same error as you

Possible fix here, replace this file: /modules/finder_views/includes/finder_views_plugin_display_finder.inc
with this (must rename it to finder_views_plugin_display_finder.inc)

actually i need to test whether this will work if you don't have the content field in your actual view

this is really... difficult
I can get it working as long as the field you're finding is one of the fields already shown in the view.
If you are not using fields in your view, or don't have that field, it stuffs up. I can supposedly add fields in myself, but that just isn't working with relationships.

I've spent quite a lot of time on this, I can't figure it out.

The next release will have a fix that should work if you have the profile field in your view (which you do), otherwise you can do almost the same view as a node view of profile types, the user fields and profile fields will be available, without the relationship.

I am having the same issue. Has this been addressed in the more recent releases?

Nope :/
I am under the impression you can work around the problem by creating a new display in your view, and adding the problematic field as a 'field' just in that display. Then in finder you need to choose that display in all the relevant places. You could still use your original display for the results page so it would appear to be the same.

Some more information in a similar issue http://drupal.org/node/697608
Looks to be the same problem.

Thanks for linking this issue with mine. I hope they really are the same issue. I have tried getting this to work by actually having the field displayed, but I get the same problem. Just to be clear, I am adding a "relationship" with "node:content profile" in my view, and then adding a filter for that content profile field. At that point, the expected behavior would be for finder to be able to search that field, but I am getting errors of the general type

user warning: Column 'field_hospital_value' in field list is ambiguous query: execute /* myusernamehere: execute */ SELECT users.uid AS uid, field_hospital_value, nid, type, vid, realname.realname AS realname_realname FROM users users LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'personal_profile' LEFT JOIN users_roles users_roles ON users.uid = users_roles.uid AND users_roles.rid = 8 LEFT JOIN content_type_personal_profile node_data_field_hospital_value_0 ON node_users.vid = node_data_field_hospital_value_0.vid AND node_data_field_hospital_value_0.field_hospital_value = '20' LEFT JOIN content_type_personal_profile node_data_field_hospital_value_1 ON node_users.vid = node_data_field_hospital_value_1.vid AND node_data_field_hospital_value_1.field_hospital_value = '22' LEFT JOIN content_type_personal_profile node_data_field_hospital_value_2 ON node_users.vid = node_data_field_hospital_value_2.vid AND node_data_field_hospital_value_2.field_hospital_value = '21' LEFT JOIN content_type_personal_profile node_data_field_hospital_value_3 ON node_users.vid = node_data_field_hospital_value_3.vid AND node_data_field_hospital_value_3.field_hospital_value = '9' LEFT JOIN content_type_personal_profile node_data_field_hospital_value_4 ON node_users.vid = node_data_field_hospital_value_4.vid AND node_data_field_hospital_value_4.field_hospital_value = '10' LEFT JOIN realname realname ON users.uid = realname.uid WHERE (users.status <> 0) AND (users_roles.rid IS NULL) AND (node_data_field_hospital_value_0.field_hospital_value = '20' OR node_data_field_hospital_value_1.field_hospital_value = '22' OR node_data_field_hospital_value_2.field_hospital_value = '21' OR node_data_field_hospital_value_3.field_hospital_value = '9' OR node_data_field_hospital_value_4.field_hospital_value = '10') GROUP BY field_hospital_value, uid, nid, type, vid, realname_realname ORDER BY realname_realname ASC in /var/www/vhosts/mywebsitenamehere.com/httpdocs/drupal/sites/all/modules/views/includes/view.inc on line 769.

I have removed realname from all fields and filters. gmclelland, you have not weighed in in a while. Did you find a solution?

I'm having the same problem. Finder doesn't appear to like relationships.

To the guys with the content profile problem, can't you make a node-based view for the same purpose?? You can still have links to the user account that the node 'belongs' to, surely. I know that isn't a solution but maybe in some of your cases it might help?

danielb, yes you are correct. I was simply wanting to find values of firstname, lastname, and gender setup through content profile. By setting up view as node, I'm able to use finder by setting the following fields:

uid - exclude display (this field provided to output node field as link to user account)
first_name - exclude display
last_name - exclude display
gender - exclude display
global custom text - this is the only field that will show up in finder results. Settings are: [field_first_name_value] [field_last_name_value] in the text field and link path of user/[uid]. This will provide finder result of first and last names concatenated as link to the user account which works as desired.
One question however, The gender field I setup as a select dropdown with values of ||, male, female. The || option was so that male or female would not be shown till user selects. I would also like the finder to not show a value unless someone selects one from the dropdown. Is this possible? If so, what do I need to do?
Thanks!

By the way, the finder fields I setup had to be an actual field to work. I couldn't use the global field that concatenated other fields in my example or it would output errors. No biggie, but just thought I'd pass that along because it appears to be somewhat related to what was happening when setting up user view with content profile relation. So, by setting the finder to last_name worked because that was an original field setup in the node but the finder results still output what I was after which was firstname + lastname as link to user account.

If you post a new issue about your gender problems, I might be able to provide guidance. Not exactly on topic here.

I've made an attempt to get some feedback about this from the Views issue queue.
#723830: What is the correct way to get table/field aliases?

Marking as duplicate: #820134: Finder on fields from Relationships

Marking as duplicate #884008: MySQL error shown and no results displayed

I have the same issue and I can't setup the view as node-based because I have 2 content types for each user, so I think I need to have the user referring to both in the view.
Also because I want to show the user photo and name.

I am using 6.x-1.8 though.
Should I open a separate new issue for that version ?

I understand this issue can't be solved without some input from Views' developers, still I am stuck with a major obstable on a production site and I am trying to find a solution.
Firstly, the code mentioned in:
#723830: What is the correct way to get table/field aliases?
never gets execute in my setup/finder because $options['keywords'] is always empty.
I don't know what that is and I am not sure this helps, but I thought to mention it just in case.

Secondly, I looked at the SQL that fails to retrieve the values to populate the select widget in my test site:

SELECT users.uid AS uid, field_country_value, users.name AS users_name 
FROM users users 
LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'personal_info' 
LEFT JOIN users_roles users_roles ON users.uid = users_roles.uid AND users_roles.rid = 8 
WHERE (users.status <> 0) AND (users_roles.rid IS NULL) 
GROUP BY field_country_value, uid, users_name ORDER BY users_name ASC

I see two separate errors:
1) the value of the content type get truncated (it should be 'personal_information')
2) the JOIN to where the actual cck field is stored is missing.
I edited the SQL as follows and I got the (supposedly) expected result.

SELECT users.uid AS uid, node_cck.field_country_value, users.name AS users_name 
FROM users users 
LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'personal_information' 
LEFT JOIN content_type_personal_information node_cck ON node_users.nid = node_cck.nid
LEFT JOIN users_roles users_roles ON users.uid = users_roles.uid AND users_roles.rid = 8 
WHERE (users.status <> 0) AND (users_roles.rid IS NULL)
GROUP BY field_country_value, uid, users_name ORDER BY users_name ASC

I realize that the JOIN could be missing because it's difficult to extract that information from the view, but...

Now I will attempt to write a piece of code in the "rewrite option" to see if I can by-pass the issue.

I think we might wind up having to painstakingly go through views' code and get an understanding of how views relationships work.

Looks like we have a response from Merlin. I will need to replicate this problem so I can provide more info.

Postponed on #723830: What is the correct way to get table/field aliases?

A possible way to reproduce the error:

- install cck, content profile module (besides finder and views of course)
- add a new content type 'personal info' and tick the box Content Profile > Use this content type as a content profile for users
- add 1 or 2 fields to the new content type, let's say "country" (text - select list) and "personal bio" (text - multi-line)
- create a view 'search_user" of type "User" with a relation to "personal info"
- add filter for active users only
- add the 2 cck fields in the view
- add a page display with path 'list_user' (not sure this is necessary)
- create a new finder view with path 'search_user' that uses the search_user view
- tick "Form on page"
- add one element as a select list to search into country
- add one element as text to search into personal bio

When I navigate to the "search_user' URL I get the error:
user warning: Unknown column 'field_country_value' in 'field list' query: SELECT users.uid AS uid, field_country_value FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'personal_info' WHERE users.status <> 0 GROUP BY field_country_value, uid in /sites/all/modules/views/includes/view.inc on line 775.

The finder works in general, but the Country element shows no values to choose from.

I am sure there could be a simpler example, but this is the scenario I am battling with.

Thanks for the help

subscribe - http://drupal.org/node/1142470 marked as duplicate

It appears you can pass a relationship param to ensure_table(), I think this is how to solve the issue. I just don't know what data to pass in there or where to get it :/

In the file views/includes/handlers.inc there is a function add_table() where you can see the extra params like $relationship getting built and put in. I don't quite understand it yet, but I think there is something to this.

OMG I think I've figured it out, you put the 'alias' from the relationship object into the $relationship param of $view->query->add_table()...

Here's a working patch against 6.x-1.x
Relies on this issue too: #1210608: Remove intersect combinations, and improve AND conjunctions
I've committed it too, but I'll keep it here for reference for porting, further bug fixes, etc...

I believe this is resolved, if you discover any other bugs please start an issue.

There is a new setting where you configure relationships to get this working.

I have an issue similar to this in Drupal 7.