Setting up a complex query in MySQL [closed]

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.

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.