SQL conditional / random join / polymorphic associations?

Question

I am trying to implement something similar to the Ruby on Rails polymorphic relationship. I have the following three tables:

Organization User Events

An event can be owned by either a user or an organization, so there are columns in my Events table: owner_type and owner_id.

I can easily list all events belonging to either users or organizations through an internal 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?

Hope this makes sense.

Any advice is appreciated.

Thanks.

Answer 1

You cannot make the join table conditional, so in this case you will need to join the events for both users and organizations and use a joint join to combine common fields (for example, 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 might consider creating an owner table that contains both users and organizations, with a structure like (id, type, org_id, name, ...). This will require only one connection, but may complicate other areas of your circuit, for example. user membership in the organization.

An alternative method would be to combine the joins of user and organization tables, and then to join the events once.

Answer 2

• Owner has columns that are common to all subtypes of the owner.
• Person and Organization have columns specific to each of them.

Answer 3

How do I move ownership information from Events to two join tables, EventsUsers and EventsOrganisations (each of which has only two columns, FKs to Events and a table of the appropriate owner)? Then you can UNION two queries, each of which connects through the connection table to the table of the owning object.