How do I specify a null relation in SQLAlchemy?

Question

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

• Matters have a one-many relationship to WorkItems.
• WorkItems have a one-one (or one-zero) relationship to LineItems.

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?

Answer 1

Try using and_ as and is not overloaded:

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

Answer 2

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()