Please implement eager loading of nested associations #388

Closed
dankohn opened this Issue Dec 29, 2012 · 56 comments

Projects

None yet
@dankohn
Contributor
dankohn commented Dec 29, 2012

See http://guides.rubyonrails.org/active_record_querying.html#eager-loading-multiple-associations for how Rails does it.

In the code below, Variant.find can include Product, but what I'd like to be able to do is also include Brand through Product, Category through Product, and Partner through Brand and Product. In the meantime, I'm using the excellent sequelize.query to achieve the raw query I'm looking for.

var Sequelize = require("sequelize")
  , config = require(__dirname + "/config/config.js")
  , sequelize = new Sequelize(config.database, config.username, config.password, {
      dialect: config.dialect, host: config.host, port: config.port, omitNull: true})

var Partner = sequelize.import(__dirname + "/models/partner.js")
  , Category = sequelize.import(__dirname + "/models/category.js")
  , Brand = sequelize.import(__dirname + "/models/brand.js")
  , Product = sequelize.import(__dirname + "/models/product.js")
  , Variant = sequelize.import(__dirname + "/models/variant.js")

Partner.hasMany(Brand)
Brand.belongsTo(Partner)
Brand.hasMany(Product)
Product.belongsTo(Brand)
Category.hasMany(Product)
Product.belongsTo(Category)
Product.hasMany(Variant)
Variant.belongsTo(Product)

var rawSql = 'SELECT "Variants".*, "Products"."parentSku", "Products"."title", "Products"."description", \
"Products"."CategoryId", "Categories"."name" as "categoryCode", "Categories"."description" AS "Category", \
"Products"."BrandId", "Brands"."description" AS "Brand", "Brands"."PartnerId", "Partners"."name" FROM \
"Variants" LEFT JOIN "Products" ON ("Variants"."ProductId" = "Products"."id") LEFT JOIN "Categories" ON \
("Products"."CategoryId" = "Categories"."id") LEFT JOIN "Brands" ON ("Products"."BrandId" = "Brands"."id") \
LEFT JOIN "Partners" ON ("Brands"."PartnerId" = "Brands"."PartnerId") WHERE "Variants"."childSku" = ?'

var sql = Sequelize.Utils.format([rawSql, "puma-1234-red-large"])

sequelize.sync({ force: true }).success(function() {

  Partner.create({ name: 'saks' }).success(function(partner) {

    Brand.create({ name: 'Puma', description: 'Puma Sportswear', PartnerId: partner.id }).success(function(brand) {

      Category.create({ name: "mn-shirt", description: "Men's shirt" }).success(function(category) {

        Product.create({ title: "Cool t-shirt", description: "<div>Really handsome t-shirt</div>",
        parentSku: "puma-1234", CategoryId: category.id, BrandId: brand.id}).success(function(product) {

          Variant.create({ color: "Red", size: "Large", listPriceCents: "9999", salePriceCents: "4999",
          childSku: "puma-1234-red-large", ProductId: product.id }).success(function(variant) {

            Variant.find({ where: ['"Variants"."childSku" = ?', 'puma-1234-red-large'], include: ['Product']}).success(function(lookedUp) {

              console.log(JSON.stringify({'color' : lookedUp.color, 'parentSku' : lookedUp.product.parentSku, 
              'childSku' : lookedUp.childSku, 'BrandId' : lookedUp.product.BrandId, 'CategoryId' : lookedUp.product.CategoryId}))

              sequelize.query(sql).success(function(data){console.log(JSON.stringify(data))})

            })
          })
        })
      })
    })
  })
})

Here's the output

Executing: DROP TABLE IF EXISTS "Partners";
Executing: DROP TABLE IF EXISTS "Categories";
Executing: DROP TABLE IF EXISTS "Brands";
Executing: DROP TABLE IF EXISTS "Products";
Executing: DROP TABLE IF EXISTS "Variants";
Executing: CREATE TABLE IF NOT EXISTS "Partners" ("name" VARCHAR(255) NOT NULL UNIQUE, "id"   SERIAL , "createdAt" TIMESTAMP NOT NULL, "updatedAt" TIMESTAMP NOT NULL, PRIMARY KEY ("id"));
Executing: CREATE TABLE IF NOT EXISTS "Categories" ("name" VARCHAR(255) NOT NULL UNIQUE, "description" TEXT NOT NULL, "id"   SERIAL , "createdAt" TIMESTAMP NOT NULL, "updatedAt" TIMESTAMP NOT NULL, PRIMARY KEY ("id"));
Executing: CREATE TABLE IF NOT EXISTS "Brands" ("name" VARCHAR(255) NOT NULL UNIQUE, "description" TEXT NOT NULL, "id"   SERIAL , "createdAt" TIMESTAMP NOT NULL, "updatedAt" TIMESTAMP NOT NULL, "PartnerId" INTEGER, PRIMARY KEY ("id"));
Executing: CREATE TABLE IF NOT EXISTS "Products" ("title" VARCHAR(255) NOT NULL, "description" TEXT, "parentSku" VARCHAR(255) NOT NULL UNIQUE, "id"   SERIAL , "createdAt" TIMESTAMP NOT NULL, "updatedAt" TIMESTAMP NOT NULL, "BrandId" INTEGER, "CategoryId" INTEGER, PRIMARY KEY ("id"));
Executing: CREATE TABLE IF NOT EXISTS "Variants" ("color" VARCHAR(255), "size" VARCHAR(255), "listPriceCents" INTEGER NOT NULL, "salePriceCents" INTEGER NOT NULL, "childSku" VARCHAR(255) NOT NULL UNIQUE, "id"   SERIAL , "createdAt" TIMESTAMP NOT NULL, "updatedAt" TIMESTAMP NOT NULL, "ProductId" INTEGER, PRIMARY KEY ("id"));
Executing: INSERT INTO "Partners" ("name","createdAt","updatedAt") VALUES ('saks','2012-12-29 7:52:15.262000','2012-12-29 7:52:15.262000') RETURNING *;
Executing: INSERT INTO "Brands" ("name","description","createdAt","updatedAt","PartnerId") VALUES ('Puma','Puma Sportswear','2012-12-29 7:52:15.266000','2012-12-29 7:52:15.266000',1) RETURNING *;
Executing: INSERT INTO "Categories" ("name","description","createdAt","updatedAt") VALUES ('mn-shirt','Men''s shirt','2012-12-29 7:52:15.270000','2012-12-29 7:52:15.270000') RETURNING *;
Executing: INSERT INTO "Products" ("title","description","parentSku","createdAt","updatedAt","BrandId","CategoryId") VALUES ('Cool t-shirt','<div>Really handsome t-shirt</div>','puma-1234','2012-12-29 7:52:15.275000','2012-12-29 7:52:15.275000',1,1) RETURNING *;
Executing: INSERT INTO "Variants" ("color","size","listPriceCents","salePriceCents","childSku","createdAt","updatedAt","ProductId") VALUES ('Red','Large','9999','4999','puma-1234-red-large','2012-12-29 7:52:15.277000','2012-12-29 7:52:15.278000',1) RETURNING *;
Executing: SELECT "Variants".*, "Products"."title" AS "Products.title", "Products"."description" AS "Products.description", "Products"."parentSku" AS "Products.parentSku", "Products"."id" AS "Products.id", "Products"."createdAt" AS "Products.createdAt", "Products"."updatedAt" AS "Products.updatedAt", "Products"."BrandId" AS "Products.BrandId", "Products"."CategoryId" AS "Products.CategoryId" FROM "Variants" LEFT OUTER JOIN "Products" ON "Variants"."ProductId"="Products"."id" WHERE "Variants"."childSku" = 'puma-1234-red-large';
{"color":"Red","parentSku":"puma-1234","childSku":"puma-1234-red-large","BrandId":1,"CategoryId":1}
Executing: SELECT "Variants".*, "Products"."parentSku", "Products"."title", "Products"."description", "Products"."CategoryId", "Categories"."name" as "categoryCode", "Categories"."description" AS "Category", "Products"."BrandId", "Brands"."description" AS "Brand", "Brands"."PartnerId", "Partners"."name" FROM "Variants" LEFT JOIN "Products" ON ("Variants"."ProductId" = "Products"."id") LEFT JOIN "Categories" ON ("Products"."CategoryId" = "Categories"."id") LEFT JOIN "Brands" ON ("Products"."BrandId" = "Brands"."id") LEFT JOIN "Partners" ON ("Brands"."PartnerId" = "Brands"."PartnerId") WHERE "Variants"."childSku" = 'puma-1234-red-large'
[{"color":"Red","size":"Large","listPriceCents":9999,"salePriceCents":4999,"childSku":"puma-1234-red-large","id":1,"createdAt":"2012-12-29T13:52:15.277Z","updatedAt":"2012-12-29T13:52:15.278Z","ProductId":1,"parentSku":"puma-1234","title":"Cool t-shirt","description":"<div>Really handsome t-shirt</div>","CategoryId":1,"categoryCode":"mn-shirt","Category":"Men's shirt","BrandId":1,"Brand":"Puma Sportswear","PartnerId":1,"name":"saks"}]
@Meaglin
Meaglin commented Dec 30, 2012

This would be extremely nice to have, something like { "include": "Brands.Products" } to fetch all associated products.

@Meaglin
Meaglin commented Jan 8, 2013

Could this find a place anywhere on the road map ? :)

@sdepold sdepold added a commit that referenced this issue Jan 9, 2013
@sdepold sdepold correctly link to #388 b78ad76
@sdepold
Member
sdepold commented Jan 9, 2013

hmm having a roadmap is quite handy ^^

@cflynn07
cflynn07 commented Apr 9, 2013

+1 This would be absolutely fantastic

@mendrik
mendrik commented Apr 25, 2013

+1

@0xception

+1 This will make sequelizejs the goto orm.

@simianhacker

+1... This is the one feature I desperately need.

@Gedzis
Gedzis commented May 13, 2013

+1.. Its realy needed for ORM

@kimvanhees

+1 .... Would be a verry nice feature!

@Morgul
Morgul commented May 29, 2013

Just ran into this; still not sure how I'm going to proceed. I have a fixed set of additional models I need to pull, and it's all getting serialized to JSON anyway, so I think I'm just going to nest a few find queries, but it would definitely make my life easier if this feature was added. :)

@leeola
leeola commented Jun 1, 2013

I feel this is a needed feature as well.

That, and issue #668. Both issues are sort of required to make meaningful join queries, in my mind.

@sevastos
Contributor

👍 +1

@neetiraj

+1

@jijeshmohan

+1

@jpap
jpap commented Jul 8, 2013

+1

@durango durango referenced this issue Jul 9, 2013
Closed

State of the Union #749

33 of 45 tasks complete
@CyberCM
CyberCM commented Jul 18, 2013

+1

@hboylan
hboylan commented Jul 18, 2013

+1

@ptnplanet

I tried implementing this, but ran into more and more side effects and problems as I was doing so.

validateIncludedElement in dao-factory.js has to be changed to run a new prepareIncludedElement recursively on includes.

selectQuery in the query-generator.js files has to be customized to recursively run through includes and adding JOINs to the query and prepare nested column names multiple levels deep.

prepareJoinData in abstract/query.js has to handle returned nested column names like 'user.message.recipient.photo' and create nested objects { user: { message: { recipient: {} } } }. findTableNameInAttribute also has to be changed in this manner.

But, there are a lot of side-effects when doing so and I could not get it to work. The one that made me give up, was the grouping of nested objects to their parents. Thats why I proposed to refactor the whole association functionality (#745).

@durango
Member
durango commented Jul 20, 2013

@ptnplanet Granted I'm relatively new to the associations code, I believe I have a decent solution, the only problem is we have to make incredibly small baby steps in order to eventually reach this goal. Not much refactoring would have to happen, but a few key changes in how we execute the queries in the first place (more like ActiveRecord, and less like SequelizeJS). SequelizeJS is fully capable of performing these queries, it's just a matter of telling it to.

My only concern/area in which I get incredibly hazy.. is how to semantically call nested associations (we're not Ruby) all while being able to search, sort and limit the records. I'm open to suggestions.

@ricardograca
Contributor

@durango CakePHP has the tree behavior for returning nested data, but it is also capable of returning nested associations without using that behavior. In fact, that's how its finder methods work, and the find() method when used with the "first" or "all" options will actually limit the depth to 1 by default. They named "recursive" the property that controls how deep the finder methods will look for associations. I like how getting nested associations is how the finder works by default, but I'm sure not everybody will agree with this. Not sure if this solves the problem of how to name them in Sequelize though.

@durango
Member
durango commented Jul 20, 2013

@ricardograca I believe we avoid that problem by still making you call getX() but I could be wrong

@ricardograca
Contributor

@durango What problem?

@hboylan
hboylan commented Jul 20, 2013

In my case for model (Zone), which hasMany multiple times (Audio, Light, etc.).

var Zone  = models.zone
  , Audio = models.audio
  , Light = models.light;
Zone.hasMany(Audio).hasMany(Light);

Instead of being able to do this:

Zone.find(id, { include:[Audio, Light] ).success(function(zone){
  res.json(zone)
})

Which outputs something like:

{
  id:1,
  name:"Living Room",
  audio:[
    { id:1, name:"Front Speakers", volume:50 },
    { id:1, name:"Front Speakers", volume:50 },
    { id:2, name:"Rear Speakers", volume:20 },
    { id:2, name:"Rear Speakers", volume:20 }
  ],
  lights:[
    { id:1, name:"Recessed 1", level:60 },
    { id:2, name:"Recessed 2", level:100 },
    { id:1, name:"Recessed 1", level:60 },
    { id:2, name:"Recessed 2", level:100 }
  ]
}

I have to do this:

Zone.find(id).success(function(zone){
  Audio.findAll({ where:{ zoneId:zone.id }}).success(function(audio){
    Light.findAll({ where:{ zoneId:zone.id }}).success(function(lights){      
      res.json({
        id: zone.id,
        name: zone.name,
        audio: audio,
        lights: lights,
      })
    })
  })
})

To simply get:

{
  id:1,
  name:"Living Room",
  audio:[
    { id:1, name:"Front Speakers", volume:50 },
    { id:2, name:"Rear Speakers", volume:20 }
  ],
  lights:[
    { id:1, name:"Recessed 1", level:60 },
    { id:2, name:"Recessed 2", level:100 }
  ]
}

Which is really annoying, but in case you run into this issue that's how for now.

@lewisou
lewisou commented Aug 27, 2013

+1 i am urgent. When will this be on the master branch....

@enxtur
enxtur commented Nov 20, 2013

+1

@martnst
martnst commented Dec 4, 2013

+1

Q1: Has there any work been done one this yet? This issue is marked for version 1.7.0 which already has alpha as well as beta releases - but so fare there is no commit or pull request associated.

Q2: Could somebody please link to any work around in the mean time.

regards

@sdepold
Member
sdepold commented Dec 8, 2013

@mickhansen wants to do this soon.

@mickhansen
Member

Yeah it's on my list. Hoping to start working on it this week.

@sunh11373

+1

@mickhansen mickhansen was assigned Dec 9, 2013
@leahciMic

+1

@Shepless

+1 Any updates on this?

@johangirod

Would be awesome to have this feature !

This was referenced Dec 30, 2013
@mickhansen
Member

WIP: #1178

@mickhansen
Member

Fixed by #1178 and published in 1.7.0-beta5 / 2.0.0-beta5

@mickhansen mickhansen closed this Dec 30, 2013
@jbaumbach

@mickhansen Now I see the Sequelize DAO models in the "options" of the response, but it doesn't seem like the SQL query is any different. I was assuming that the responses would include the data from the additional join(s). Am I misunderstanding what this feature was supposed to do?

@mickhansen
Member

@jbaumbach what version are you on, and what does your find call look like? It's supposed to join nested associations, and return them obviously - Tests should reflect that ;)

@jbaumbach

@mickhansen We just merged 1.7.0-beta8 and are using Postgres. But we are on a private fork and manually merging in changes periodically. We may have missed something, we're checking...

@mickhansen
Member

@jbaumbach alright because it should be in there, i'm using it in one of my projects atleast ;)

@matzipan
matzipan commented Feb 2, 2014

I'm on 1.7.0-rc4 and I'm getting the following error: Sport (Sport) is not associated to SportsEvent!

With the object include syntax:

App.Models.SportsEvent.findAll({
                order: "starts_at ASC",
                limit: 5,
                include: [
                    {
                        model: App.Models.Sport,
                        as: "Sport"
                    },
                    {
                        model: App.Models.SportsLeague,
                        as: "SportsLeague"
                    },
                    {
                        model: App.Models.SportsEventParticipant,
                        as: "SportsEventParticipant",
                        include: [ App.Models.SportsParticipant ]
                    }
                ]
            })

With the regular syntax (not nested, everything works fine).

@matzipan
matzipan commented Feb 2, 2014

However, with this code

App.Models.SportsEvent.findAll({
                order: "starts_at ASC",
                limit: 5,
                include: [
                    App.Models.Sport,
                    App.Models.SportsLeague,
                    {
                        model: App.Models.SportsEventParticipant,
                        include: [ App.Models.SportsParticipant ]
                    }
                ]
            })

I'm getting Include malformed. Expected attributes: daoFactory, as!

So, using this code:

App.Models.SportsEvent.findAll({
                order: "starts_at ASC",
                limit: 5,
                include: [
                    App.Models.Sport,
                    App.Models.SportsLeague,
                    {
                        daoFactory: App.Models.SportsEventParticipant,
                        as: "SportsEventParticipants",
                        include: [ App.Models.SportsParticipant ]
                    }
                ]
            })

Works as expected, but does not eager load the nested model.

@mickhansen
Member

What does your association calls look like? If you use alias in the include it has to match the alias on the association call

@matzipan
matzipan commented Feb 2, 2014
 App.Models.SportsBet.belongsTo(App.Models.SportsEvent, {foreignKey: 'event_id'});
    App.Models.SportsBet.belongsTo(App.Models.SportsBetOdd, {foreignKey: 'bet_odd_id'});
    App.Models.SportsBetOdd.belongsTo(App.Models.SportsEvent, {foreignKey: 'event_id'});
    App.Models.SportsEvent.belongsTo(App.Models.Sport, {foreignKey: 'sport_id'});
    App.Models.SportsEvent.belongsTo(App.Models.SportsLeague, {foreignKey: 'league_id'});
    App.Models.SportsEvent.hasOne(App.Models.SportsEventOutcome, {foreignKey: 'sports_event_id'});
    App.Models.SportsEvent.hasMany(App.Models.SportsBetOdd, {foreignKey: 'event_id'});
    App.Models.SportsEvent.hasMany(App.Models.SportsEventParticipant, {foreignKey: 'event_id'});
    App.Models.SportsEventParticipant.belongsTo(App.Models.SportsParticipant, {foreignKey: 'participant_id'});
@mickhansen
Member

If you want to use 'as' in includes you have to use the same 'as' in your association calls.

@matzipan
matzipan commented Feb 2, 2014

I don't want to. I have to, because if I don't use it, it throws the error
I've just written.

Zisu Andrei

On 2 February 2014 18:52, Mick Hansen notifications@github.com wrote:

If you want to use 'as' in includes you have to use the same 'as' in your
association calls.

Reply to this email directly or view it on GitHubhttps://github.com/sequelize/sequelize/issues/388#issuecomment-33908470
.

@mickhansen
Member

No, you were using as in your includes WITHOUT using them in your associations, hence the error - As i've just explained twice now :)

@mickhansen
Member

If you want to include with as: 'SportsEvent' you need App.Models.SportsEvent.belongsTo(App.Models.Sport, {as: 'SportsEvent', foreignKey: 'sport_id'});

@matzipan
matzipan commented Feb 2, 2014

If you read: #388 (comment) attentively, I said
However, with this code

App.Models.SportsEvent.findAll({
                order: "starts_at ASC",
                limit: 5,
                include: [
                    App.Models.Sport,
                    App.Models.SportsLeague,
                    {
                        model: App.Models.SportsEventParticipant,
                        include: [ App.Models.SportsParticipant ]
                    }
                ]
            })

I'm getting Include malformed. Expected attributes: daoFactory, as!

@mickhansen
Member

I felt it was more prudent to fix your original issue.
Although if you're getting that error you must be on an older version, cause that error was removed in the version that added nested includes i think. What version are you on?

@matzipan
matzipan commented Feb 2, 2014
npm list | grep sequelize
├─┬ sequelize@1.7.0-rc4
├─┬ sequelize-postgres@1.7.0-beta.2
│ └─┬ sequelize@1.7.0-beta.2
@mickhansen
Member

IIRC npm will load the one closest to what you're using, so if you're using sequelize-postgres it will load an old version of sequelize.

@mickhansen
Member

The bundles have unfortunately not been updated as rigorously.

@matzipan
matzipan commented Feb 2, 2014

What's there to do apart from waiting until the bundles get updated?

@mickhansen
Member

Just use sequelize and node-postgres directly, bundles aren't required :)

npm uninstall sequelize-postgres
npm install sequelize
npm install pg
@matzipan
matzipan commented Feb 2, 2014

It's alive!

@mickhansen
Member

Great :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment