feral yacht
Hi everyone,
This is the following of my question from yesterday, which I still have trouble with.
Shortly: i have the db represented in the attached picture. The db contains a fixed ammount of recordings, and each user must rate each recording (each individual rating is stored in the ratings table).
I'm trying to write a SQLAlchemy query that returns all users who still haven't rated all recordings.
e.g.: I have:
4 recordings, with IDs 1, 2, 3, 4
4 users with IDs 1,2,3,4
and the following ratings:
{user_id: 1, recording_id: 1} {user_id: 1, recording_id: 2} {user_id: 1, recording_id: 3} {user_id: 1, recording_id: 4}
{user_id: 2, recording_id: 1} {user_id: 2, recording_id: 3} {user_id: 2, recording_id: 4}
{user_id: 4, recording_id: 1} {user_id: 4, recording_id: 2} {user_id: 4, recording_id: 3} {user_id: 4, recording_id: 4}
I want my query to return the users with IDs 2 and 3, as user 2 hasn't rated the recording 2 and the user 3 hasn't rated a single recording.
I thought I could achieve this by:
for each **user **in the **users **table, looking at each **recording **from the **recordings **table, and return the **user **if the **ratings **table didn't contain a **rating **with Rating.user_id == User.id and Rating.recording_id == Recording.id
I'm pretty new to SQL in general, so I struggled to reach the following query that unfortunately always return an empty list...
results = session.query(User).filter(
select(Recording).exists().where(
~select(Rating).exists().where(
Rating.recording_id == Recording.id and Rating.user_id == User.id
)
)
).all()
I think I need some help from people who actually know what they're doing with SQL queries!! 😉
upbeat fractal
