SQL Conditional / Case Joining / Polymorphic Associations?

Question

Hi,

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

How about moving ownership information out of Events into two join tables, EventsUsers and EventsOrganisations (each of which has just two columns, FKs to Events and the apppropriate owning-object table) ? Then you can UNION two queries each of which joins through the join table to the owning-object table.

Answer 3

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