Duplicate friend listing in Views2
| Project: | Flag Friend |
| Version: | 6.x-1.x-dev |
| Component: | Views integration |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
We have a view that doesn't seem to work properly and I'm stumped as to how to get this to do the right thing. What we want to do is show a user's friends, based on the UID pulled from the url. So far, we have it kind of working, but we've got a really odd problem where a friend of the user shows up multiple times in the listing if that friend is friends with anyone else. Here's the setup in Views:
1) Argument: "Flag friend: Friends of" -- set to pull the user id from the URL (this works)
2) Relationships: "Flag Friend: friend" By Any User, Flag Type "Friend"
We're not "Including only Flagged Content" because that comes up empty every time
3) Fields: User Picture, User Name, Flag Links (We take the name and links so we can build a link to the user's page, since there's no "link to this user" option under the User Picture).
The only filter we've tried is to set the Flag friend: Relationship Created time to "not empty"
The SQL that Views is generating is as follows:
SELECT users.uid AS uid,
users.name AS users_name,
flag_friend.uid AS flag_friend_uid,
users.picture AS users_picture
FROM users users
LEFT JOIN flag_content flag_content_users ON users.uid = flag_content_users.uid AND flag_content_users.fid = 4
LEFT JOIN flag_friend flag_friend ON users.uid = flag_friend.uid
WHERE (flag_friend.created IS NOT NULL)
AND ((users.uid IN (SELECT f.friend_uid FROM flag_friend f WHERE f.uid = 7)) OR (users.uid IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 7)))Now, here's what's interesting. In the current test case, there are only three entries in the flag_friend table:
fid friend_uid created
7 1 1254343139
1 13 1254341943
1 3 1254319493In this configuration, User 7 Shows User 1 as a friend two times. If I delete the one where friend_uid is '13' then user 7 only shows user 1 as a friend once. I've seen as many as 8-10 instances of the same user showing up on another user's list of friends, and it corresponds to how many times that user's id shows up in the fid and friend_id columns combined.
So, how do i build a view that only pulls a user's friends and no more? I've tried every variation I can come up with, with no luck. Ideas?
#1
By the way - In addition to trying to set it up myself from scratch, I've tried importing the views code directly from flag_friend into a new view, and I get exactly the same result.
#2
Update, and I think this is a bug so I'm changing the category:
We completely removed and reinstalled both flag and flag_friend. In testing this further using the view that comes with flag_friend I find that the duplicates are always coming from the uid column. In a new scenario, the table looks like this:
uid friend_uid created13 3 1254428876
13 7 1254428851
10 7 1254428732
7 3 1254428603
and again with the argument supplying the user whose friends we want to see:
Argument = 3 returns 7, 13, 13
Argument = 7 returns 3, 10, 13, 13
Argument = 10 returns 7
Argument = 13 returns 3, 7 <- This is correct
So it seems that when the argument is a
friend_uid, and that user is friends with someone who appears multiple times in the uid column, we get duplicate entries. But when the argument is auid, and one of those friends appears multiple times in the friend_uid column, the results are correct.We're desperate to figure this out, so any help would be really appreciated. We were supposed to launch the site today, but have had to hold off because of this problem.
(EDIT: changed the 'fid' references to uid. oops)
#3
Looks like what is happening here is that you are getting other flags than just friend. Each flag (friend, bookmark, etc) has an fid. So the fact that you're getting multiple fids means you are pulling in more than just the friend flag.
#4
ack. sorry - in my tables above, I accidentally typed 'fid' when i meant 'uid.' What's showing there is the actual flag_friend table, so it should read:
uid friend_uid created13 3 1254428876
13 7 1254428851
10 7 1254428732
7 3 1254428603
As I said, i'm seeing this even when I import the default view from flag_friend.views_default.inc into a new view.
(I edited the post above so that it shows uid, not fid)
#5
Can you post an export of your view, I'm not seeing this happen in the view that comes with flag_friend.
Also, you don't have to import the view that comes with flag_friend, it will show up under your views listings be default and will already be enabled.
#6
Right - I'm only importing it into a new view to test that I didn't screw up the default view. I had made some modifications to the default view within the Views UI, but it was a while back, and I wanted to test with a fresh view to be sure it wasn't something I had done.
huh. I tried pasting the view into this field as I've done before but apparently it's too big. It's ending up in the database on d.o because it's there when i hit the 'edit' link, but it's not displaying. So, I'll attach it as a text file.
#7
Here's the view.
You can ignore the last display: 'CP_Pending' -- it's experimental and I'm not using it. Right now, the one i'm dealing with is the block "Current user's Friends block"
#8
I notice this has something with panel_pane in it - are you by chance trying to view this view in a panel? Or just the normal view with the normal path's provided?
#9
Actually, the one at the end is the only panel_pane, that's the experimental display that we're not using for anything. The primary display that's a problem right now is the Block display. That said, even with the default views, we get the same thing. it doesn't work in preview mode in Views or in the actual rendered page.
#10
I found this query in flag_friend_get_friends(), which works correctly, at least as far as getting the correct rows:
$result = db_query("SELECT * FROM {flag_friend} WHERE uid = %d OR friend_uid = %d", $uid, $uid);If I drop
SELECT * FROM flag_friend WHERE uid = 3 OR friend_uid = 3into phpMyAdmin, I get the correct results: 13 and 7.The SQL being generated in the view is:
SELECT users.uid AS uid,users.name AS users_name,
users.picture AS users_picture
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 = 3)) OR (users.uid IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 3)))
If I drop that into phpMyAdmin, it returns 7, 13 and 13. I'm stumped because that SQL looks right to me.
#11
I think I may have found the problem, but I'm confused about something. In the view, "Distinct" is checked as being on, but in the resulting SQL, the DISTINCT statement is not there. If I run the generated query in phpMyAdmin as:
SELECT DISTINCT users.uid AS uid,users.name AS users_name,
users.picture AS users_picture
FROM users users
LEFT JOIN flag_friend flag_friend ON users.uid = flag_friend.uid
WHERE (users.status <> 0)
AND ((users.uid = (SELECT f.friend_uid FROM flag_friend f WHERE f.uid = 3)) OR (users.uid IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 3)))
It now returns the correct results. So for some reason, it seems that the Views version is not using the DISTINCT statement even though it's set to, and MySQL clearly has no problem with it in this case.
I've tried setting up a brand new view, and Distinct isn't working there either. I know there are issues where this sometimes doesn't work, but there doesn't seem to be a good explanation as to why. I've opened an issue in Views on this (http://drupal.org/node/597170), but i'm wondering if in your working scenario Distinct is being set or not?
#12
hrm. not sure why the distinct is not coming through. the where's are set in the argument handler (flag_friend_handler_argument_numeric.inc) but I don't think they would be overriding the distinct in any way... hrm.
#13
We must go live this week, so I've worked around this by handling the display of friends in a custom module, using a query that contains the DISTINCT statement. We'd love to be able to do this all in views though, for consistency with flagged and pending user listings, which do seem to work correctly.
Hopefully, a solution will come up at some point. Thanks for taking the time to look into this, though.
#14
I've changed the title to reflect the problem here. Also marking #607672: duplicated friend as a duplicate
#15
So this is really due to how the argument is set up. Really it should be two left joins on the flag_friend table WHERE uid = %d for one and WHERE friend_uid = %d for the other.
It would make the Views argument so much more robust
#16
What you want is a query like this
SELECT u.uid FROM users u LEFT JOIN flag_friend f1 ON f1.uid = u.uid LEFT JOIN flag_friend f2 ON f2.friend_uid = u.uid WHERE (f1.friend_uid = %d OR f2.uid = %d)#17
subscribe
#18
I tried the distinct option in views and it give's me now the correct query. Before using distinct I had the same problems as in this thread...
#19
The problem arises for pending friends view page too.
It's surely a duplicated view problem, 'cause if I delete one entry, the other one is erased too (i.e., anyhow the entry is unique).
#20
Please don't change the version indicator. If it happens in a version previous to -dev, best practice is to upgrade to -dev to see if it still happens and simply report that it's happening in rc2 as well. -dev is where we actually make changes. Thanks.
#21
There is an ongoing problem with drupal core and distinct. Maybe this bug has nothing todo with this module but it has with views and core...
#22
no it has to do with how the argument is handled see my comment in #16
#23
I'm completely new to drupal, so this might be a silly answer, but I was having the same problem, and when I changed the view type to user, not node it cleared up the problem - no need to use the duplicate setting at all. Hope that helps.
#24
This issue occurs on a fresh install once Views are enabled. Seems to repeat the username as per the users position in the array. Example: Friend #5 would have their name repeated 5 times, Friend 6 repeated 6 times, and friend 7 repeated 7 times.