Be my hero and solve this SQL query question

[brad]

Scenario:
Voting system where one chooses one item over another. My votes table
has a winner_id and a loser_id. I need to determine what the popular
match ups are.

This is my query so far:
SELECT winner_id, items1.name as name1, loser_id, items2.name as
name2, count(*) as popularity
FROM votes
INNER JOIN items items1 ON items1.id=winner_id
INNER JOIN items items2 ON items2.id=loser_id
GROUP BY winner_id, loser_id
ORDER BY popularity
DESC LIMIT 5

The problem is this query considers "Item A and Item B" to be
different from "Item B and Item A" where I need them to be considered
as the same thing.

Is it possible to write a query that can do this or a better way to
structure the tables to make the first query work? I'm totally lost,
I don't even know where to look! Please help!

Thanks in advance,
Brad.

[strawberry]

Post by brad
Voting system where one chooses one item over another. My votes table
has a winner_id and a loser_id. I need to determine what the popular
match ups are.
SELECT winner_id, items1.name as name1, loser_id, items2.name as
name2, count(*) as popularity
FROM votes
INNER JOIN items items1 ON items1.id=winner_id
INNER JOIN items items2 ON items2.id=loser_id
GROUP BY winner_id, loser_id
ORDER BY popularity
DESC LIMIT 5
The problem is this query considers "Item A and Item B" to be
different from "Item B and Item A" where I need them to be considered
as the same thing.
Is it possible to write a query that can do this or a better way to
structure the tables to make the first query work? I'm totally lost,
I don't even know where to look! Please help!
Thanks in advance,
Brad.

I'm no hero but if you had a structure like this:

votes(voter_id*,item_id*,vote)

*=primary key

so resulting in something like this:

voter_id | item_id | vote
1 1 1
1 2 -1
2 1 1
2 2 -1
3 1 -1
3 3 1
4 1 1
4 2 -1
5 3 1
5 2 -1

then a query like this (untested) would show you common pairs:

SELECT count( votepair ) score, votepair
FROM (

SELECT GROUP_CONCAT( A.item_id, ',', B.item_id ) votepair
FROM votes A
LEFT JOIN votes B ON B.voter_id = A.voter_id
AND B.item_id > A.item_id
GROUP BY A.voter_id
)x
GROUP BY votepair
ORDER BY score DESC;

[brad]

Post by strawberry

Post by brad
Voting system where one chooses one item over another. My votes table
has a winner_id and a loser_id. I need to determine what the popular
match ups are.
SELECT winner_id, items1.name as name1, loser_id, items2.name as
name2, count(*) as popularity
FROM votes
INNER JOIN items items1 ON items1.id=winner_id
INNER JOIN items items2 ON items2.id=loser_id
GROUP BY winner_id, loser_id
ORDER BY popularity
DESC LIMIT 5
The problem is this query considers "Item A and Item B" to be
different from "Item B and Item A" where I need them to be considered
as the same thing.
Is it possible to write a query that can do this or a better way to
structure the tables to make the first query work? I'm totally lost,
I don't even know where to look! Please help!
Thanks in advance,
Brad.

votes(voter_id*,item_id*,vote)
*=primary key
voter_id | item_id | vote
1 1 1
1 2 -1
2 1 1
2 2 -1
3 1 -1
3 3 1
4 1 1
4 2 -1
5 3 1
5 2 -1
SELECT count( votepair ) score, votepair
FROM (
SELECT GROUP_CONCAT( A.item_id, ',', B.item_id ) votepair
FROM votes A
LEFT JOIN votes B ON B.voter_id = A.voter_id
AND B.item_id > A.item_id
GROUP BY A.voter_id
)x
GROUP BY votepair
ORDER BY score DESC;

You are a genius AND a hero! That worked perfectly!

The only thing of note is that votepair was being returned as a BLOB
so I had to set the group_concat_max_length environment variable to
less than 512 to get it to return as VARCHAR, like so:
SET GLOBAL group_concat_max_len=500;

I never would've come up with that solution in a million years!
Thanks so very much.

Brad.