Views are broken in D7 [#1012296]

I get an error when trying to view /user/1/friends

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '.uidd = '1') OR (users.uidINSELECTf.uidFROMflag_friendfWHEREf.friend_uidd = '1')' at line 2: SELECT COUNT(*) AS expression FROM (SELECT users.uid AS uid, 1 AS expression FROM {users} users LEFT JOIN {flag_friend} flag_friend ON users.uid = flag_friend.uid WHERE (( (users.status <> :db_condition_placeholder_0) )AND( (users.uidINSELECTf.friend_uidFROMflag_friendfWHEREf.uidd = :db_condition_placeholder_1) OR (users.uidINSELECTf.uidFROMflag_friendfWHEREf.friend_uidd = :db_condition_placeholder_2) )) GROUP BY uid) subquery; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => 1 ) in views_plugin_pager->execute_count_query() (line 141 of /Users/jbitner/Sites/drupal-7.x-dev/sites/all/modules/views/plugins/views_plugin_pager.inc).

Which totally makes no sense to me... all this debug info is crammed together...

Comment	File	Size	Author
#3	updated patch	1.9 KB	jyee
#2	flag_friend_handler_argument_numeric.inc_.patch	1.87 KB	jyee

Comments

Comment #1

sirkitree commented 2 January 2011 at 00:10

Title:

Views are broken

» Views are broken in D7

Making it more obvious to the casual browsers of issues.

Comment #2

jyee commented 20 January 2011 at 21:57

Status	File	Size
new	flag_friend_handler_argument_numeric.inc_.patch	1.87 KB

I've attached a patch to includes/flag_friend_handler_argument_numeric.inc that resolves the errors above.
apparently add_where() is stripping spaces from the $field arg, so the patch simply moves the rest of the subselect to the $value arg

There's still a problem where the "View All Friends" tab doesn't display friends.
The "Awaiting Friend Approvals" and "Friend Requests" tabs appear to be working as intended.

edit: see following post

Comment #3

jyee commented 4 February 2011 at 23:37

Status:

Active

» Needs review

Status	File	Size
new	updated patch	1.9 KB

ah... nevermind the previous post and patch (spoke too soon).

This patch resolves the error message and also presents a working all friends view.

Edit: the patch also has a leftover comment and call to devel's dd() that should probably be removed... sorry for not cleaning up after myself.

Comment #4

jon pugh

he/him

English

Newburgh, NY

commented 17 March 2011 at 22:04

Worked for me.

Comment #5

sirkitree commented 18 March 2011 at 01:24

Status:

Needs review

» Fixed

Thank you! http://drupalcode.org/project/flag_friend.git/commitdiff/3863c323ac90c87...

Comment #6

Anonymous (not verified) commented 18 March 2011 at 16:30

Status:

Fixed

» Active

Well the patch does not work for me! The error message is gone, but the "View All Friends" view display is empty. I double checked that the user has friends. Digging in the query I found out that the "… users.uid = 'IN (…" does not return a value while "… users.uid IN (…" does. Getting rid of the = and the ' ' around the subselect does the trick for me. I tried to fix this in the argument_numeric.inc file, but with little luck. It seams views add_where() function puts the = and the ' ' in by default.

SELECT users.picture AS users_picture, users.uid AS uid, users.name AS users_name, users.mail AS users_mail, flag_friend.uid AS flag_friend_uid
FROM {users} users
LEFT JOIN {flag_friend} flag_friend ON users.uid = flag_friend.uid
WHERE (( (users.status <> '0') )AND( (users.uid = 'IN (SELECT f.friend_uid FROM {flag_friend} f WHERE f.uid = 71)') OR (users.uid = 'IN (SELECT f.uid FROM {flag_friend} f WHERE f.friend_uid = 71)') ))
GROUP BY uid
LIMIT 10 OFFSET 0

Comment #7

sirkitree commented 18 March 2011 at 20:08

Hrm, yeah - seems like we need a different way to construct this. To be clear, the way we have it now, the WHERE that is generated is this:

... 
WHERE (( (users.status <> '0') )
AND( 
  (users.uid = 'IN (SELECT f.friend_uid FROM flag_friend f WHERE f.uid = 37)') 
  OR 
  (users.uid = 'IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 37)') )) 
...

created by:

...
    else {
      $operator = empty($this->options['not']) ? '=' : '!=';
      $this->query->add_where($group, 'users.uid', "IN (SELECT f.friend_uid FROM {flag_friend} f WHERE f.uid $operator $this->argument)");
      $this->query->add_where($group, 'users.uid', "IN (SELECT f.uid FROM {flag_friend} f WHERE f.friend_uid $operator $this->argument)");
    }

but what we really want is this:

...
WHERE (( (users.status <> '0') )
AND( 
  (users.uid IN (SELECT f.friend_uid FROM flag_friend f WHERE f.uid = 37)) 
  OR 
  (users.uid IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 37)) )) 
...

which was generated in D6 by:

...
    else {
      $operator = empty($this->options['not']) ? '=' : '!=';
      $this->query->add_where($group, 'users.uid', "IN (SELECT f.friend_uid FROM {flag_friend} f WHERE f.uid $operator $this->argument)");
      $this->query->add_where($group, 'users.uid', "IN (SELECT f.uid FROM {flag_friend} f WHERE f.friend_uid $operator $this->argument)");
    }

So how do we do this in D7?