SQLAlchemy filter query with multiple table outerjoin

Question

I have following three tables.

User:
[
   user_id
   first_name
   last_name
]

Picture:
[
   filename
   picture_type // eg: painting, photograph
   painter
]

Comment
[
   user_id
   filename
   comment
]

I am trying to query all filenames that was not reviewed by current user.

following line returns all the filenames that was reviewed and commented in the given picture_type

session.query(Picture.filename).outerjoin(Comment).filter(
                                       Picture.filename == Comment.filename,
                                       Picture.picture_type == 'photograph'
                                                        ).all()

, and the following line returns all the filenames in the in the given picture_type

session.query(Picture.filename).outerjoin(Comment).filter(
                                       Picture.picture_type == 'photograph'
                                                        ).all()

I was expecting following line would return filenames not reviewed in the in the given picture_type, but it returns an empty list

session.query(Picture.filename).outerjoin(Comment).filter(
                                       Picture.filename != Comment.filename,
                                       Picture.picture_type == 'photograph'
                                                        ).all()

Am I doing something wrong here? What am I missing here?

Answer 1

I researched Exclusive Join and figured out the problem.

It should have been None == Comment.filename, not Picture.filename != Comment.filename.

I fixed the code like below code and it is working now.

session.query(Picture.filename).outerjoin(Comment).filter(
                                   None == Comment.filename,
                                   Picture.picture_type == 'photograph'
                                                    ).all()

The problem was the resulting table of left outer join would not have any row that meet left_field != right_field condition. The resulting rows would meet conditions left_field == right_field or None == right_field since the missing value in the right table would be marked as null in the resulting table.

Below link gave me good learnings about this subject.

http://www.datasavantconsulting.com/roland/sql_excl.html

Thanks all for trying to help.

However, I will still be open to better suggestions!!!

Answer 2

i think your code have syntax error. are you missed a dot ? you wrote:

session.query(Picture.filename)outerjoin(Comment).filter(
                                   Picture.filename == Comment.filename,
                                   Picture.picture_type == 'photograph'
                                                    ).all()

but it should be like this:

session.query(Picture.filename).outerjoin(Comment).filter(
                                   Picture.filename == Comment.filename,
                                   Picture.picture_type == 'photograph'
                                                    ).all()

other sections also has this problem