Sequelize.js: join tables without associations

Question

Is there a way to join tables that don't have associations defined using include in sequelize? This is not a duplicate of this. I am talking about tables that are not associated at all but having columns that I want to join on.

Example:

select * from bank
left outer join account 
    on account.bank_name = bank.name

The above query will return all records in table bank regardless of the existence of an account record where the specified constraints apply.

In sequelize this would look something like the following, if models bank and account were associated on account.bank_name = bank.name:

bank.findAll({
    include: [{
        model: account,
        required: false,
    }]
})

However, what if the models are not associated? Is there a way to write my own custom on section or equivalent:

bank.findAll({
    include: [{
        model: account,
        required: false,
        on: {
            bank_name: Sequelize.col('bank.name')
        }
    }]
})

I vaguely remember reading something about this but I cannot seem to find that doc anywhere now. If you can point to the correct section in the docs it would be greatly appreciated.

Arnaud

Hi, any success finding the answer. Actually I am also facing a similar situation. – Catcher

same. Can't seem to get my head around the fact that something like this can be missing – Boffa

Answer 1

To anyone who end up googling here: there is a workaround, not sure it was available back then, but as of 5.21.7 it works. you can pass association object to include parameter:

const res = await bank.findAll({
        include: [
            {
                model: account,
                association: new HasMany(bank, account, {/*options*/}),
            },
        ],
    })

HasMany is an association constructor, which can be taken from Sequelize.Associations, or sequelize instance. there are also other association constructors there. For TS you will need to cast manually since that property not in types.

UPD: for BelongsToMany you need to call _injectAttributes() on association prior usage, and I didnt check others apart from HasMany.

Adela

Hey @Karabur. Thanks for your answer! Could you provide some links to docs please (or a short excerpt)? It's been a while since I had to deal with Sequelize-js so you probably have a better idea how things work now. – Arnaud

There are no docs on that, I've found that looking to sequelize code. But there is more simple and better way, you can define association and explicitly tell sequelize to not create associations in db by using constraints: false in association options.sequelize.org/master/class/lib/… this way you define associations in a code but not in db so you will be able to write include statements in a regular way, to let sequelize generate joins, but it will not create real foreign keys in db. Works in v5, dont know about v6 – Adela

According to docs, constraints is supposed to do the following: Should on update and on delete constraints be enabled on the foreign key. I set constraints: false and was able to use the hasOne association to perform includes without actually creating the foreign keys in the database. Still, the documentation says something different about the use for constraints – Paternalism

I just want to build on the answer since I ran into a similar requirement. I don't know if it's due to TypeScript or sequelize version 6, but instead of new HasMany(...) I had to use this.hasMany(OtherModel, {/*options*/} – Wittman

@Adela thx a million man ... saved my day !!!! constraints: false is the magic pill :) – Polysyllabic

I wasn't sure how to use this (what options to specify), but eventually succeeded with: association: new BelongsTo(db.tableA, db.tableB, {foreignKey: 'account', targetKey: 'account', constraints: false}). no FK was created – Kartis

Updated link for @Adela 's comment: sequelize.org/master/class/src/… – Buccinator

Answer 2

It seem that while it is possible to define a custom on condition, it is not possible to include relations without defining associations first. This is implied by the documentation wording for findAll method (search for options.include on page):

A list of associations to eagerly load using a left join. Supported is either { include: [ Model1, Model2, ...]} or { include: [{ model: Model1, as: 'Alias' }]} or { include: ['Alias']}. If your association are set up with an as (eg. X.hasMany(Y, { as: 'Z }, you need to specify Z in the as attribute when eager loading Y).

The docs on options.include[].on are even more terse:

Supply your own ON condition for the join.

I ended up solving my problem using Postgres views as a workaround. It is also possible to inject a raw query and bypass sequelize limitations but I would use that only as a development / prototyping hack and come up with something more robust / secure in production; something like views or stored procedures.

Arnaud

But how can we construct the array of children for child tables, since we get repeated results. – Catcher

@Catcher not sure exactly what you mean by 'repeated results', do you want to continue the discussion in Stack Overflow Chat (actually, not sure how that works either)? – Arnaud

I get the following result: [ { "userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 11, "Category.name": "Category1", "Category.userID": 1}, { "userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 12, "Category.name": "Category2", "Category.userID": 1 } ] But I want to display the result as follows: [ { "userTime": "14:00", "id": 1, "name": "Jack", Category: [ { id: 11, name: "Category1", userID: 1 }, { id: 12, name: "Category2", userID: 1 } ] } ] – Catcher

Yea, that's the problem with something like raw queries; data is going to be returned as a flat table. You'll have to hack your own conversion to JS objects or make the query / view / stored proc return json(b) values that match your expected structure. It's pretty much a mess; ok for prototyping but for production you'll need a better solution that matches the "bigger picture" of your project whatever it may be. – Arnaud

@AlexanderF. I ended up solving my problem using Postgres views as a workaround. were you able to use findAndCountAll on the view with where clause (filtering and pagination). If so, would it be possible for you to share some snnipet I was trying to achieve it from 2 days and tried almost all documented methods. Thanks – Lamartine

Answer 3

Using Sequelize 6 (current stable version), you don't need anything but the on property of the include[] object to get a proper JOIN from Sequelize. That's quite useful, because Sequelize doesn't yet allow foreign keys with multiple fields and a literal on is the only way to go, AFAIK. (prove me wrong, please)

import { Op, literal } from "sequelize"
...
function innerJoinWithMyModel() {
  // Notice that the alias is used for the tables, and not for the fields!!
  return {
    model: MyModel,
    required: true,
    on: literal(
      "Alias1.field1 = Alias2.field2 AND Alias1.field3 = Alias2.field4",
    ),
  }
}
...
function paginatedResults(...) {
...
    if (needsJoin) {
      config.include = [
        {
          ...innerJoinWithMyModel(),
          where: {
            [Op.and]: conditions,
          },
        },
      ]
    }
...
  return model.findAndCountAll({
    ...config,
    offset: ...,
    limit: ...,
  })
}

Evania

this does not work. getting an exception: name: 'SequelizeEagerLoadingError' message: 'tableA is not associated to tableB!' – Kartis

Answer 4

1st time posting on stackoverflow so bear with me~

(Sequelize v7) I needed to do an INNER JOIN on tables ShipmentDevice and Shipment on the shipment_id property. This gave me the needed results:

let list = await ShipmentDevice.findAll({
          where: where_clauses,
          include: [
            {
              model: Shipment,
              association: new BelongsTo(
                ShipmentDevice, Shipment,
                {
                  targetKey: "id",
                  foreignKey: "shipment_id",
                  constraints: false
                }),
              required: true
            },
          ]
        });

Basically:

model - the table you want to do the JOIN with

association - to define the sequelize association (i used Belongs to)

constraints: false - as i understand it this is used to avoid creating the foreign key in the database itself

required: true - if not specified, it wasnt doing the INNER JOIN, it was doing LEFT OUTER JOIN

This was the resulting script that was run:

SELECT ShipmentDevice...Shipment.... FROM shipment_devices AS ShipmentDevice INNER JOIN shipments AS Shipment ON ShipmentDevice.shipment_id = Shipment.id WHERE where_clauses;

Schizont