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.

Fraxinella

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.

Nawrocki

+1, what I was thinking, you could also add any columns from alias U and/or O into the select list if necessary – Windward

Answer 2

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

Basilio

Answer 3

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.

Vasilikivasilis

Answer 4

A bit old, but I think it would be useful, this version performs better in my scenario than multiple joins

select e.id,
    case when e.owner_type = 'person' then
        (
            select p.name from person p
            where p.id=e.owner_id
        )
    else
        (
            select o.name from organization o
            where o.id=e.owner_id
        )
    end entityName,
e.owner_type 
from events e

in postgres you could even build a json of entire related entity

Middlemost