SQL Conditional / Case Joining / Polymorphic Associations?

Question

I'm trying to implement something similar to Ruby on Rails' polymorphic relationships. I have the following three tables :

Events Users Organisations

An event can be owner by either a user or an organisation, so my Events table includes the columns: owner_type and owner_id.

I can easily list all events that belong to either users or organisations through an inner join and where clause, however, is there a way to make the join table conditional based on the value of the owner_type column, allowing all events to be listed together, regardless of owner_type?

I hope that makes sense.

Any advice appreciated.

Thanks.

Answer 1

You can't make the join table conditional, so in this case you would have to join events to both users and organisations and use coalesce to merge the common fields (eg. name) together.

select e.id, coalesce(u.name, o.name) owner_name
from events e
left join users u on e.owner_id = u.id and e.owner_type = 'user'
left join organisations o on e.owner_id = o.id and e.owner_type = 'org'

However, you may consider creating an owners table, which contains both users and organisations, with a structure like (id, type, org_id, name, ...). This would only require a single join, but may complicate other areas of your schema, eg. user membership of an organisation.

An alternative method would be to union the users and organisations tables together and then join once from events.

Answer 2

• Owner has columns common to all owner-subtypes.
• Person and Organization have columns specific to each one.