Node: distinct error
| Project: | Views Alpha Pager |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | douggreen |
| Status: | needs work |
I recently upgraded from 4.7 to 5.5. Just about everything is working on my site, except the Alpha Pager, which doesn't seem to be seeing the first character of the sort criteria for profiles. Instead, I get only the "All" choice (which displays the list with "All" for the alpha pager and a numeric pager to page through all - it correctly sorts the list.
I also get the following error: user warning: Unknown column 'profile_last_name_value' in 'group statement' query: SELECT DISTINCT LEFT(UPPER(profile_last_name.value), 1) FROM node node LEFT JOIN users_roles users_roles ON node.uid = users_roles.uid INNER JOIN users users ON node.uid = users.uid LEFT JOIN profile_values profile_last_name ON users.uid = profile_last_name.uid AND profile_last_name.fid = '2' LEFT JOIN profile_values profile_full_name ON users.uid = profile_full_name.uid AND profile_full_name.fid = '6' WHERE (node.type IN ('usernode')) AND (users_roles.rid = '3') GROUP BY node.nid, profile_last_name_value ORDER BY LEFT(UPPER(profile_last_name.value), 1) in /home/ivaw/public_html/test/includes/database.mysql.inc on line 172.
I've also tried using a non-profile field (Node: ID or Node: Created time for example) just to check, but as long as I'm using Profiles, it gives the same type of error
I'm using this with Views 5.x-1.6 (I've tried VAP 5.x-1.2 with the same result). This worked fine in 4.7. Any ideas?
#1
The table profile_ table names probably changed from 4.7 -> 5.x. Please run update.php. If it's still not working, then recreate your view.
#2
I've done both - starting from scratch, and running update.php, but neither fixed the problem. After testing each component, it seems that the issue is with Node: Distinct.
I've tried it on non-profile fields, and the error happens there, as well. This is the error I'm getting with a new view - its very simple, just node title, node-type "page" with alpha pager sorted on node: title. I'm attaching an exported version in case that's helpful.
user warning: Unknown column 'node_title' in 'group statement' query: SELECT DISTINCT LEFT(UPPER(node.title), 1) FROM node node WHERE (node.type IN ('page')) GROUP BY node.nid, node_title ORDER BY LEFT(UPPER(node.title), 1) in /home/ivaw/public_html/test/includes/database.mysql.inc on line 172.
#3
changing title
#4
I've had problems with the DISTINCT filter, separate from VAP, and I try to avoid using it. However, it is a valuable filter. Do you have the problem with the numeric pager or only with the alpha pager?
#5
This happens only with the alpha pager - with numeric I don't get an error and the results come out fine. How do you do without the distinct filter? I'm building a view of profiles, and without it, I get many multiple copies of each profile.
#6
I've added a fix for the DISTINCT clause. Please check-out the latest version and test.
I'm leaving the patch as CNR because the fix for DISTINCT is still not good enough, in that the regex simply looks for WHERE and ORDER clauses, and blindly substitutes them. This is not going to work with subqueries (#143888). Something better like the regex used in the 6.x db_rewrite_sql is needed (see #151910).
#7
#8
That did the trick - thanks for the help and for all your work on this module!
#9
Hi there,
I'm still having problem with this. The error I'm getting is
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 'DISTINCT(node.nid), node_title ORDER BY LEFT(TRIM(LEADING 'AND ' FROM (TRIM(LEAD' at line 1 query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, node_data_field_image_cache.field_image_cache_fid AS node_data_field_image_cache_field_image_cache_fid, node_data_field_image_cache.field_image_cache_title AS node_data_field_image_cache_field_image_cache_title, node_data_field_image_cache.field_image_cache_alt AS node_data_field_image_cache_field_image_cache_alt, uc_products.sell_price AS uc_products_sell_price FROM node node LEFT JOIN content_field_image_cache node_data_field_image_cache ON node.vid = node_data_field_image_cache.vid LEFT JOIN uc_products uc_products ON node.vid = uc_products.vid WHERE (node.status = '1') AND (node.type IN ('product')) AND (LEFT(TRIM(LEADING 'AND ' FROM (TRIM(LEADING 'THE ' FROM (TRIM(LEADING 'A ' FROM (TRIM(LEADING '\'' FROM (TRIM(LEADING '"' FROM (TRIM(LEADING ' ' FROM (UPPER(node.title))))))))))))), 1) = 'E') GROUP BY DISTINCT(node.nid), node_title ORDER BY LEFT(TRIM(LEADING 'AND ' FROM (TRIM(LEADING 'THE ' FROM (TRIM(LEADING 'A ' FROM (TRIM(LEADING '\'' FROM (TRIM(LEADING '"' FROM (TRIM(LEADING ' ' FROM (UPPER(node.title))))))))))))), 1), node_title ASC LIMIT 0, 12 in /home/public_html/includes/database.mysql.inc on line 172.Can anyone help me with this?
Thanks
#10
Nevermind, I found a fix here: http://drupal.org/node/130242