I tried to get help in the forums, but no one has answered my simple question. Sorry, but this is one frustration I have with Drupal.
http://drupal.org/node/494048
---
Hi Folks-
I want to create an internal page view of site users ONLY for site administrators. I can do this fine, and I generate a table with the following column headers:
Uid User Roles City Province CO Last access Last login Edit E-mail
However, I want to see first names, not just usernames. So when I try to add ANY field from the profiles...
Profile: First Name
or
Profile: Last Name
I get errors like the following:
user warning: 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 '-first_value FROM users users LEFT JOIN location_instance location_instance O' at line 9 query: SELECT DISTINCT(users.uid) AS uid, users.name AS users_name, location.city AS location_city, location.province AS location_province, location.country AS location_country, users.access AS users_access, users.login AS users_login, users.mail AS users_mail, profile_values_profile_name-first.value AS profile_values_profile_name-first_value FROM users users LEFT JOIN location_instance location_instance ON users.uid = location_instance.uid LEFT JOIN location location ON location_instance.lid = location.lid LEFT JOIN profile_values profile_values_profile_name-first ON users.uid = profile_values_profile_name-first.uid AND profile_values_profile_name-first.fid = '4' ORDER BY uid DESC in /home/mysite.com/sites/all/modules/views/includes/view.inc on line 759.
Comment | File | Size | Author |
---|---|---|---|
#33 | profiles.png | 181.98 KB | dawehner |
#23 | 497936-profile-fieldnames-have-dashes-reversed.txt | 2.28 KB | crashtest_ |
#18 | 497936-profile-fieldnames-have-dashes.patch | 2.28 KB | merlinofchaos |
#17 | views-table_alias_underscores-497936-17.patch | 897 bytes | Agileware |
#16 | 497936_table_alias_underscores.patch | 808 bytes | greggles |
Comments
Comment #1
cburschkaI'm pretty sure that you cannot use hyphens in the name of a database column (or a table) like 'name-first'. Just use underscores.
Maybe Views or profile.module should validate names and not allow hyphen characters.
Comment #2
merlinofchaos CreditAttribution: merlinofchaos commentedI have enough to do without reading support requests that require clicking to somewhere else.
Comment #3
finedesign CreditAttribution: finedesign commentedArancaytar-
You're awesome. I renamed the profile fields using underscores and it works fine now. However, this is a serious usability issue. Look at the wording when entering views names!
This tells me that dashes are allowed. Somebody should take this out.
merlinofchaos-
I appreciate your efforts. However, if you would have just read the post, you would have understood the problem. You did not have to click on the link. I copied all the text from the link in the support request. I'm sorry for taking too much of your time and next time I'll forgo the link.
Forgive me to take your time further to say....
This is the very thing that is drawing folks away from Drupal. I'm sorry if I'm not a developer, but I do have questions. I'm sorry if I don't ask questions in the most perfect way. If I don't provide enough info, people complain. Here, if I provide too much info, people complain. Darned if I do, darned if I don't.
Working with Wordpress is a joy because the developer community is friendly and people actually answer forum questions. This is not a complaint at merlinofchaos. It's a cry for somebody to figure out how to make Drupal more user-friendly for non-developers.
Comment #5
rjleigh CreditAttribution: rjleigh commentedmerlinofchaos, I love views and all your work, and think you are the rock star of drupal development, but I have to disagree here.
The profile module, part of core, clearly states that the field names (they refer to it as 'form name') can have dashes or underscores.
I understand that dashes are a problem in SQL calls, and I was stumped by the SQL error for 15 mins, because it was in one field of many profile entries that someone else set up. I can't believe that there won't be countless others who would be stumped, and who wouldn't even have a clue to know why it happened.
This should at least be in the documentation, but even better to check the names and give a better error, like "profile form names can not have dashes - remove them in the profile 'form name' field". If for no other reason, than to remove the need for answering future support issues about it.
Comment #6
catchJust ran into this bug - if you create a profile field with hyphen in it, the field won't work when listed with views. Should be an easy enough validation fix + upgrade path to write, and possibly backportable to D6 (at least for new fields if not updating old ones).
Comment #7
cburschkaTechnically this doesn't break core as it is; profile.module simply doesn't expect its fid to be used as an SQL identifier.
However, it'd be nice if it were supported - otherwise Views would have to clean it up every time.
Comment #8
cburschkaNote that hyphens are currently permitted as per the help text, so this patch will either change a string or leave an outdated help text in place.
Comment #9
cburschkaHere.
Comment #10
catchSimple patch, all the changes are good, looks great!
Comment #11
catchHmm, but we should probably update Drupal 6 profile fields with hyphens to use underscores in D7, otherwise there'll be a validation error when saving existing fields which wasn't there before.
Comment #12
MichaelCole CreditAttribution: MichaelCole commented#9: profile-no-dash-497936-9.patch queued for re-testing.
Comment #13
gregglesWell, I just ran into this on drupal.org which has some profile fields that are really interesting in terms of their name. The "Full Name" field is "full-name" for example.
Trying to synch that in bakery down to groups.drupal.org means that some views on g.d.o that used this field are broken :(
Can we not also fix this in views for D6?
Comment #14
gregglesHere's a simple way to repeat the original bug for anyone intestered:
1. Create a profile field called "full-name"
2. Create a view that includes the uid and this field
3. Your query will contain a syntax error
Comment #15
gregglesThe invalid query from groups.drupal.org's signup list:
And a valid version where I just replace - with _ in the alias for the table.
That seems like something simple enough to fix in views. My initial scanning makes me think that ensure_my_table in handlers.inc could be the right place, but I didn't test that theory.
Comment #16
gregglesThis is probably totally wrong in a variety of ways, but it works for my case...
Comment #17
Agileware CreditAttribution: Agileware commentedI don't know if it is technically correct either but it fixes the problem for me too, which was that profile fields with hyphens in the form name would give SQL errors related to the table alias when used in views.
Here is the same patch that applies from the root of the views module.
Comment #18
merlinofchaos CreditAttribution: merlinofchaos commentedSeems like this patch will fix the problem without applying a shotgun to all table aliases.
Can someone confirm this patch works?
As an aside: My initial response was colored by the post:
When the opening sentence of the post is bagging on how people get support in the Drupal community, and then immediately provides a link elsewhere, my immediate reaction is to go spend my time somewhere else. A quick scan of the data didn't look very complete, and the post set the tone of annoyance.
I understand the original poster was annoyed, but you get more flies with honey than vinegar.
Comment #19
gregglesIt doesn't work for me. On the plus side, I've got a testing scenario setup now so I can test more things easily.
I agree with your comment about how this issue was opened and appreciate your explaining the sentiment so others can benefit from the perspective.
Comment #20
merlinofchaos CreditAttribution: merlinofchaos commentedgreggles: I forgot to mention, in order for my patch to work:
1) You'll need to clear views cache and
2) all existing (currently broken) profile fields will be misnamed.
Ugh. 2 is actually bad; they're only broken if they're filters. :/
Comment #21
gregglesIt's still broken, but I got it: the order of the first two arguments in the str_replace is backwards.
Comment #22
merlinofchaos CreditAttribution: merlinofchaos commentedDOH. Good grief and I looked up str_replace to make sure I had that right and still did not have it right. :/
Comment #23
crashtest_ CreditAttribution: crashtest_ commentedThis is just a re-roll of the earlier patch, but with the _ and - reversed. Needs further testing.
Comment #24
crashtest_ CreditAttribution: crashtest_ commentedTested this by creating 3 profile fields:
profile_test_one
profile_test_two
profile_full-name
Created two users.
Created two nodes with one node per user.
Created a view that pulled node title, node teaser, profile_test_one, profile_test_two, but not profile_full-name. The view displayed properly.
Applied the patch, verified that the view still displayed properly, then added profile_full-name to the view, saved, and verified that the view still displays properly.
Comment #25
Agileware CreditAttribution: Agileware commentedTesting patch in #23.
After applying the patch, clearing the views cache, removing the broken field and adding the field again it all works.
Comment #26
merlinofchaos CreditAttribution: merlinofchaos commentedCommitted to all branches.
Comment #28
Nigel CunninghamIt would be good to put a noticeable warning to people in the 2.13 release notes. I just upgraded to -dev and was bitten :)
Also (which is why I'm reopening), I'm not sure that the commit was right:
http://drupalcode.org/project/views.git/commitdiff/8e84e6cf65a4e0828065a...
shows the previous line being changed (a space inserted), and the line which should be changed is changing underscores into dashes instead of vice versa. The changelog doesn't show a subsequent correction ala comments 22 & 26.
Edit:
I've just done the fix for the str_replace. With that in place, there was no need to modify the view so no warning would be needed for users upgrading.
Comment #29
chromix CreditAttribution: chromix commentedNot to pollute this issue, but found a dash (-) in a field alias for a select list profile field, when it's added as a filter or an argument:
NigelCunningham -> Is this covered in the fix you described? Did you or anyone else roll a patch for it?
Comment #30
dawehner@tmaclean
Did you tested the views 2.x-dev version of views?
There this issue is fixed already.
Comment #31
chromix CreditAttribution: chromix commentedThat's odd... I just updated and I didn't see the fix. Either way, I just updated my copy of profile.views.inc at line 37.
Comment #32
Nigel Cunningham@dereine: Yes, 2.x-dev. The fix is not in there.
Comment #33
dawehnerYou seem to be wrong about this. See the screenshot.
Take care that you clear you cache and rebuild you views.
Comment #34
Nigel CunninghamYour screenshot proves the point - it's replacing '_' with '-', thereby making the error occur more rather than less.
http://php.net/manual/en/function.str-replace.php
Regards,
Nigel
Comment #35
dawehnerOH what a fail. True.
Commited to 6.x-2.x
Comment #36
Nigel CunninghamThanks. Confirmed fixed.