Max Williams Max Williams - 1 year ago 151
MySQL Question

Mysql: count and group unique emails in associated tables by polymorphic join

The relevant part of my schema (Mysql 5.6.24) is thus:

table: training_event_invitees
registered (tinyint)
invitee_id (id)
invitee_type (varchar)

table: users
id (integer)
email (varchar)
school_id (integer)

table: contacts
id (integer)
email (varchar)
school_id (integer)

table: schools
id (integer)
email (varchar)


I want to try to do the following: get all the training_event_invitees that have registered set to 1, get the associated school, user and contact records, and then group them by school_id, and return the school id and the count of unique email addresses from that school.

training_event_invitees has a two-column foreign key, using invitee_id and invitee_type: invitee_type would be either "School", "User" or "Contact", and references the id field from the corresponding table.

So, algorithmically, it's something like

- get all the registered training_event_invitees
- get all of the associated user, contact and school records
- group these by users.school_id, contacts.school_id or schools.id
- count the number of distinct emails in each group


So, it should return an array like
[
[1234, 6],
[3407, 2]
]

where 1234 and 3407 are values of school_id and 6 and 2 are the count of distinct emails.

I can break this down into a few steps, but there must be a one-hit way to do it. Can anyone help?

Answer Source

One method is to combine the two tables using left join, and then doing the aggregation:

select coalesce(u.school_id, c.school_id) as school_id,
       count(distinct coalesce(u.email, c.email)) as num_emails
from training_event_invitees tei left join
     users u
     on u.id = tei.invitee_id and tei.invitee_type = 'user' left join
     contacts c
     on c.id = tei.invitee_id and tei.invitee_type = 'contact'
where tei.registered = 1
group by coalesce(u.school_id, c.school_id);

EDIT:

To include the school, follow the same logic:

select coalesce(u.school_id, c.school_id, s.id) as school_id,
       count(distinct coalesce(u.email, c.email, s.email)) as num_emails
from training_event_invitees tei left join
     users u
     on u.id = tei.invitee_id and tei.invitee_type = 'user' left join
     contacts c
     on c.id = tei.invitee_id and tei.invitee_type = 'contact' left join
     schools s
     on s.id = tei.invitee_id and tei.invitee_type = 'school'
where tei.registered = 1
group by coalesce(u.school_id, c.school_id, s.id);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download