CODEWITHSUNDEEP
pythonsqlalchemyforeign-key-relationship
Jesse Dhillon
Jesse Dhillon

Reputation: 7997

How do I specify a relation in SQLAlchemy where one condition requires a column to be null?

Not sure what the correct title for this question should be. I have the following schema:

I am trying to create the following relation between Matters and WorkItems

Matter.unbilled_work_items = orm.relation(WorkItem,
  primaryjoin = (Matter.id == WorkItem.matter_id) and (WorkItem.line_item_id == None),
  foreign_keys = [WorkItem.matter_id, WorkItem.line_item_id],
  viewonly=True
)

This throws:

AttributeError: '_Null' object has no attribute 'table'

That seems to be saying that the second clause in the primaryjoin returns an object of type _Null, but it seems to be expecting something with a "table" attribute.

This seems like it should be pretty straightforward to me, am I missing something obvious?

Update

The answer was to change the primaryjoin line to:

primaryjoin = "and_(Matter.id == WorkItem.matter_id, WorkItem.line_item_id == None)"

Upvotes: 3

Views: 2915

Answers (2)

Jesse Dhillon
Jesse Dhillon

Reputation: 7997

Apart from the _Null issue, this requires a left outer join to do correctly. I've decided that unbilled_work_items should be a property that executes a query and returns the result.

# like this

@property
def unbilled_work_items(self):
  return Session.object_session(self).query.filter(...).all()

Upvotes: 1

ThiefMaster
ThiefMaster

Reputation: 318568

Try using and_ as and is not overloaded:

and_((Matter.id == WorkItem.matter_id), (WorkItem.line_item_id == None))

Upvotes: 6

Related Questions