Sequelize: left join multiple tables to support searching and ordering(1)

This is a basic case for one-to-one relationship using left join multiple tables. Let’s start from describing the use case in a scenario:

Say your Sales team has a contact database stored all the customers’ contact, and each contact has its owner who is a Sales agent in your team.

So when you look up the whole contact list,

1. You would like to see the corresponding agent.
2. You would also like to search those contacts based on an agent’s name
3. Or You would like to sort all contacts using agent’s name.

Contacts table

Sales agents table

First, we have to join the contacts table with agents table using the one-to-one relationship (belongsTo()) from Sales agent ID to Sales agent.

Then, to query out from this joined table, it has to specify the attributes to query with, and it’s pretty tricky!!

You have to declare a distinct column to be the unique id to avoid duplications(seems to be a Sequelize issue). And project every desired contacts table column into the attributes array. Then also project the desired agents table column with a desired column name (like ‘agent_display_name’). Finally, make it include the Agent model of Agents table with empty attributes and required: false (left join)

Then you should be able to query out the contacts table with a view joined with corresponding agent’s name like the below.

To be continued