OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Query with 3 tables, cartesian product warning

  • Thread starter Thread starter Gautzilla
  • Start date Start date
G

Gautzilla

Guest
My database contains a fixed amount of recordings and each user must rate each recording (each individual rating is stored in the ratings table).

Database representation

I'm trying to write an SQLAlchemy query that returns all users who haven't rated all recordings. I have 4 recordings with IDs 1, 2, 3, 4 and 4 users with IDs 1, 2, 3, 4 and the following ratings:

Code:
{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 recording 2 and user 3 hasn't rated a single recording). I thought I could achieve this by filtering the users by recordings that don't appear in the ratings for that specific user:

Code:
results = session.query(User).filter(
        ~Recording.id.in_(
            select(Rating.recording_id).where(Rating.user_id == User.id)
            )
        ).all()

It seems to work but I get a warning:

Code:
SAWarning: SELECT statement has a cartesian product between FROM element(s) "recordings" and FROM element "users".  Apply join condition(s) between each element to resolve.
  ).all()

I tried to join the tables at the beginning of the query session.query(User).join(Rating).join(Recording).filter but then the program doesn't run because of an error:

Select statement returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually

How do I solve this issue?
<p>My database contains a fixed amount of recordings and each user must rate each recording (each individual rating is stored in the ratings table).</p>
<p><a href="https://i.sstatic.net/Cb7Xp2Lr.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/Cb7Xp2Lr.png" alt="Database representation" /></a></p>
<p>I'm trying to write an SQLAlchemy query that returns all users who haven't rated all recordings. I have 4 recordings with IDs 1, 2, 3, 4 and 4 users with IDs 1, 2, 3, 4 and the following ratings:</p>
<pre><code>{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}
</code></pre>
<p>I want my query to return the users with IDs 2 and 3 (as user 2 hasn't rated recording 2 and user 3 hasn't rated a single recording). I thought I could achieve this by filtering the <strong>users</strong> by <strong>recordings</strong> that don't appear in the <strong>ratings</strong> for that specific user:</p>
<pre><code>results = session.query(User).filter(
~Recording.id.in_(
select(Rating.recording_id).where(Rating.user_id == User.id)
)
).all()
</code></pre>
<p>It seems to work but I get a warning:</p>
<pre><code>SAWarning: SELECT statement has a cartesian product between FROM element(s) "recordings" and FROM element "users". Apply join condition(s) between each element to resolve.
).all()
</code></pre>
<p>I tried to join the tables at the beginning of the query <code>session.query(User).join(Rating).join(Recording).filter</code> but then the program doesn't run because of an error:</p>
<blockquote>
<p>Select statement returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually</p>
</blockquote>
<p>How do I solve this issue?</p>
 

Latest posts

A
Replies
0
Views
1
AgencyAnalytics
A
S
Replies
0
Views
1
Stacker Media
S
C
Replies
0
Views
1
CC.Talent
C
Top