Be my hero and solve this SQL query question

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.

Re: Be my hero and solve this SQL query question

On Feb 3, 7:44 pm, "brad" wrote:
> 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.

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;

Re: Be my hero and solve this SQL query question

On Feb 4, 8:51 am, "strawberry" wrote:
> On Feb 3, 7:44 pm, "brad" wrote:
>
>
>
> > 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.
>
> 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;


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.