Basically i have this kind of database:
user: id username password name surname photo: id url upvotes user_id(foreign_key) pair: id user_id(foreign_key) photo1_id(foreign_key) photo2_id(foreign_key)
My application is kind of photo contest where users uploading their photos and choose the one they like. User can only make a choice between two random photo but with one condition – pairs can not be repeated for that user.
So i made a table where i collect all pairs that users already seen but i do not know how to effectively SELECT two random rows of photo table with condition that they are not in pair table.
Maybe i should restructure my database for that task but i cant think of how to do it.
Advertisement
Answer
This is not necessarily efficient, but you can do:
select p1.id, p2.id
from photos p1 cross join
photos p2 left join
pairs pp
on pp.user_id = ? and
pp.photo1_id = p1.id and
pp.photo2_id = p2.id
where pp.photo1_id is null
order by rand()
limit 1;
In practice, you might find it faster to generate a random pair and just check if it exists. Unless a user has compared a significant proportion of the pairs, you will only have to do this a handful of times.