[ACCEPTED]-SQL Conditional / Case Joining / Polymorphic Associations?-polymorphic-associations
You can't make the join table conditional, so 12 in this case you would have to join events 11 to both users and organisations and use 10 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 9 may consider creating an owners table, which 8 contains both users and organisations, with 7 a structure like (id, type, org_id, name, ...). This 6 would only require a single join, but may 5 complicate other areas of your schema, eg. user 4 membership of an organisation.
An alternative 3 method would be to union the users and organisations 2 tables together and then join once from 1 events.
Ownerhas columns common to all owner-subtypes.PersonandOrganizationhave columns specific to each one.
0
How about moving ownership information out 6 of Events into two join tables, EventsUsers and EventsOrganisations (each of 5 which has just two columns, FKs to Events and 4 the apppropriate owning-object table) ? Then 3 you can UNION two queries each of which joins 2 through the join table to the owning-object 1 table.
A bit old, but I think it would be useful, this 3 version performs better in my scenario than 2 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 1 build a json of entire related entity
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.