Support hyphens in profile names

I'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.

I have enough to do without reading support requests that require clicking to somewhere else.

merlinofchaos, 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.

Just 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).

Technically 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.

Note 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.

Here.

Simple patch, all the changes are good, looks great!

Hmm, 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.

#9: profile-no-dash-497936-9.patch queued for re-testing.

Well, 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?

Here'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

The invalid query from groups.drupal.org's signup list:

SELECT signup_log.sid AS sid,
   users_signup_log.name AS users_signup_log_name,
   users_signup_log.uid AS users_signup_log_uid,
   signup_log.signup_time AS signup_log_signup_time,
   users_signup_log_profile_values_full-name.value AS users_signup_log_profile_values_full-name_value,
   users_signup_log_profile_values_profile_organization.value AS users_signup_log_profile_values_profile_organization_value,
   users_signup_log_profile_values_profile_organization.uid AS users_signup_log_profile_values_profile_organization_uid
 FROM signup_log signup_log 
 INNER JOIN users users_signup_log ON signup_log.uid = users_signup_log.uid
 INNER JOIN node node_signup_log ON signup_log.nid = node_signup_log.nid
 LEFT JOIN profile_values users_signup_log_profile_values_full-name ON users_signup_log.uid = users_signup_log_profile_values_full-name.uid AND users_signup_log_profile_values_full-name.fid = '1'
 LEFT JOIN profile_values users_signup_log_profile_values_profile_organization ON users_signup_log.uid = users_signup_log_profile_values_profile_organization.uid AND users_signup_log_profile_values_profile_organization.fid = '4'
 WHERE node_signup_log.nid = 90579
   ORDER BY signup_log_signup_time ASC

And a valid version where I just replace - with _ in the alias for the table.

SELECT signup_log.sid AS sid,
   users_signup_log.name AS users_signup_log_name,
   users_signup_log.uid AS users_signup_log_uid,
   signup_log.signup_time AS signup_log_signup_time,
   users_signup_log_profile_values_full_name.value AS users_signup_log_profile_values_full_name_value,
   users_signup_log_profile_values_profile_organization.value AS users_signup_log_profile_values_profile_organization_value,
   users_signup_log_profile_values_profile_organization.uid AS users_signup_log_profile_values_profile_organization_uid
 FROM signup_log signup_log 
 INNER JOIN users users_signup_log ON signup_log.uid = users_signup_log.uid
 INNER JOIN node node_signup_log ON signup_log.nid = node_signup_log.nid
 LEFT JOIN profile_values users_signup_log_profile_values_full_name ON users_signup_log.uid = users_signup_log_profile_values_full_name.uid AND users_signup_log_profile_values_full_name.fid = '1'
 LEFT JOIN profile_values users_signup_log_profile_values_profile_organization ON users_signup_log.uid = users_signup_log_profile_values_profile_organization.uid AND users_signup_log_profile_values_profile_organization.fid = '4'
 WHERE node_signup_log.nid = 90579
   ORDER BY signup_log_signup_time ASC

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.

This is probably totally wrong in a variety of ways, but it works for my case...

I 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.

Seems 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:

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.

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.

It 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.

greggles: 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. :/

It's still broken, but I got it: the order of the first two arguments in the str_replace is backwards.

DOH. Good grief and I looked up str_replace to make sure I had that right and still did not have it right. :/

This is just a re-roll of the earlier patch, but with the _ and - reversed. Needs further testing.

Tested 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.

Testing patch in #23.

After applying the patch, clearing the views cache, removing the broken field and adding the field again it all works.

Committed to all branches.

It 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.

Not 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:

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 '-department_value, COUNT(users.uid) AS num_records FROM users users LEFT J' at line 1 query: SELECT profile_values_profile-department.value AS profile_values_profile-department_value, COUNT(users.uid) AS num_records FROM users users LEFT JOIN profile_values profile_values_profile-department ON users.uid = profile_values_profile-department.uid AND profile_values_profile-department.fid = '6' GROUP BY profile_values_profile-department_value ORDER BY profile_values_profile-department_value ASC in /srv/www/htdocs/cci/trunk/sites/all/modules/views/includes/view.inc on line 791.

NigelCunningham -> Is this covered in the fix you described? Did you or anyone else roll a patch for it?

@tmaclean
Did you tested the views 2.x-dev version of views?
There this issue is fixed already.

That'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.

<?php
$table_name = 'profile_values_' . str_replace('-', '_', $field->name);
?>

@dereine: Yes, 2.x-dev. The fix is not in there.

You seem to be wrong about this. See the screenshot.

Take care that you clear you cache and rebuild you views.

Your 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

OH what a fail. True.

Commited to 6.x-2.x

Thanks. Confirmed fixed.