SQLAlchemy query returns no data if a database field is empty

Question

I have 2 tables: User and Groups

The class declaration goes like this:

class Groups(Base):
    __tablename__ = 'groups'

    group_id = Column(Integer, primary_key=True)
    group_name = Column(Unicode(32))
    user = relationship('User')

class User(Base):
__tablename__ = 'user'

user_id = Column(Integer, primary_key=True)
name = Column(Unicode(64))
email = Column(Unicode(64))
group_id = Column(Integer, ForeignKey('groups.group_id'))

So a group ID can have multiple user_id's attached to it.

Now, I'm trying to query the above tables like this:

user_list_incomplete1 = DBSession.query(User.name, User.user_id, Groups.group_name).filter(User.group_id == Groups.group_id).order_by(User.user_id).all()

The query works for those users which have a group id declared but returns nothing (empty list) if that field is empty.

How should I write the query in order to obtain the data even for those User rows that don't have a group id?

Karim Tabet · Accepted Answer · 2016-10-24 10:16:04Z

2

The relationship you defined will result in an inner join by default. What you want here is an outer join

Something like:

user_list_incomplete1 = DBSession.query(User.name, User.user_id, Groups.group_name)\
    .outerjoin(Groups)\
    .filter()... etc

Make sense? An outer join will show all records of both tables regardless if there is a foreign key match.

answered Oct 24, 2016 at 10:16

Karim Tabet

1,8671 gold badge17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bogdan Rosciuc Over a year ago

Removing the filter_by() clause and keeping the outerjoin(Groups) gives me the right result. Thanks karimtabet!

Karim Tabet Over a year ago

No worries. It is worth noting that although my answer provides a solution to your question. It is not actually a normalised solution. A normalised table should not allow for null values (ie group_id). Ideally, you would have an association table consisting of foreign keys to Groups and Users. But that's another story ;)

Bogdan Rosciuc Over a year ago

In my 'Group' table i have mentioned a foreign key to the 'User' table. The only point where the group_id field is empty is in the 'User' table and that's at the initialization of the user. After that the admin will be able to assign him to a already existing group. Thanks for the advice anyway :)

Collectives™ on Stack Overflow

SQLAlchemy query returns no data if a database field is empty

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related