Nested grouping

[milovanderlinden]

Hello, first of all, thanks again for sequelize. Loving it more every day! I enjoy touching the limits, hopefully making the library even more awesome for all to use.

I am doing a findAll on my main object (Site) that includes Levels, that include Buildings. I am counting the levels and creating a centroid from the Buildings so I can plot an indicator symbol on the map. I have this in an existing version of my opendispatcher project, but I am in the process of migrating everything to sequelize, angular and leafletjs (I do not mind a little challenge. This is an example of how my application plots Sites on the map. The symbols clearly indicate that there are more Buildings and more Levels present on a given site.

I have written the findAll as mentioned above like this:

var srid = req.query.srid || 4326;
    models.Site.findAll({
      group: [
        'Site.id', 'Site.name', 'Site.title', 'Site.checked', 'Site.assistance', 'Levels.id'
      ],
      attributes:[
        'id',
        'name',
        'title',
        'checked',
        'assistance',
        [models.sequelize.fn('COUNT', models.sequelize.col('Levels.id')), 'levels'],
        [models.sequelize.fn('ST_AsGeoJSON',
        models.sequelize.fn('ST_CENTROID',
          models.sequelize.fn('ST_UNION',
          models.sequelize.fn('ST_TRANSFORM',
            models.sequelize.col('"Levels.Buildings.geometry"'),srid))), 15, 2
        ),
        'the_geom']
      ],
      include: [{
        model: models.Level,
        include: {model: models.Building, attributes: []},
        attributes: []
      }]
    }).then(function(sites) {
      res.json(sites);
    });
  })

I also tried to group on the Level level, but that made no difference. This generates the following query:

SELECT 
  "Site"."id", 
  "Site"."name", 
  "Site"."title", 
  "Site"."checked", 
  "Site"."assistance", 
  COUNT("Levels"."id") AS "levels", 
  ST_AsGeoJSON(ST_CENTROID(ST_UNION(
    ST_TRANSFORM("Levels.Buildings"."geometry", 4326))), 15, 2) AS "the_geom" 
FROM "opendispatcher"."Sites" AS "Site" 
LEFT OUTER JOIN "opendispatcher"."Levels" AS "Levels" 
  ON "Site"."id" = "Levels"."SiteId" AND "Levels"."deletedAt" IS NULL 
LEFT OUTER JOIN "opendispatcher"."Buildings" AS "Levels.Buildings" 
  ON "Levels"."id" = "Levels.Buildings"."LevelId" AND "Levels.Buildings"."deletedAt" IS NULL 
WHERE "Site"."deletedAt" IS NULL 
GROUP BY "Site"."id", "Site"."name", "Site"."title", "Site"."checked", "Site"."assistance";

Resulting in (sorted top results):

 id        | Name                  | Alternative Name | Checked | Count
------------------------------------------------------------------------
1416216862 | Palmstraat 5, Schaijk | Camping          |  f      | 404
1392897187 | Zeelandsedijk 10d105  | Volkel           |  t      | 388

But that is not what I want, the count is clearly a count of all the individual buildings, and I need the buildings to be aggregated on Levels first, and then joined to Sites:

SELECT 
  "Site"."id", 
  "Site"."name", 
  "Site"."title", 
  "Site"."checked", 
  "Site"."assistance", 
  COUNT("Levels"."id") AS "levels", 
  ST_AsGeoJSON(ST_CENTROID(ST_UNION(ST_TRANSFORM("Levels"."geometry", 4326))), 15, 2) AS "the_geom" 
FROM "opendispatcher"."Sites" AS "Site" 
LEFT OUTER JOIN (
select "Levels"."id",
"Levels"."SiteId",
"Levels"."deletedAt",
ST_UNION(ST_TRANSFORM("Levels.Buildings"."geometry", 4326)) as "geometry"
 from "opendispatcher"."Levels" AS "Levels" 

LEFT OUTER JOIN "opendispatcher"."Buildings" AS "Levels.Buildings" 
  ON "Levels"."id" = "Levels.Buildings"."LevelId" AND "Levels.Buildings"."deletedAt" IS NULL
Where "Levels"."deletedAt" IS NULL
GROUP BY "Levels"."id", "Levels"."SiteId"
) as "Levels"
ON "Site"."id" = "Levels"."SiteId" AND "Levels"."deletedAt" IS NULL 
WHERE "Site"."deletedAt" IS NULL 
GROUP BY "Site"."id", "Site"."name", "Site"."title", "Site"."checked", "Site"."assistance";

That should result in:

 id        | Name                  | Alternative Name | Checked | Count
------------------------------------------------------------------------
1366380608 | Eikendonk, Vught      | Serviceflat      |  f      | 14
1366380012 | Zijlstraat HwD        | Stichting        |  f      | 3

Now, I understand this is complex. And I do not ask you to solve this for me, but is there anyone that has an idea on how to best approach this dillema?

Thanks!

Milo

[milovanderlinden]

For now I solved it using literal which gives me the required result:

models.Site.findAll({
      group: [
        'Site.id', 'Site.name', 'Site.title', 'Site.checked', 'Site.assistance',// 'Levels.id'
      ],
      attributes:[
        'id',
        'name',
        'title',
        'checked',
        'assistance',
        [models.sequelize.literal('(SELECT COUNT(*) FROM opendispatcher."Levels" lvl where lvl."SiteId" = "Site"."id")'), 'levels'],
        [models.sequelize.literal('(SELECT ST_ASGEOJSON(ST_CENTROID(ST_UNION(ST_TRANSFORM("geometry", 4326))), 15, 2) from opendispatcher."Levels" lvl ' +
          'LEFT OUTER JOIN "opendispatcher"."Buildings" bdg ' +
          'ON lvl."id" = bdg."LevelId" AND bdg."deletedAt" IS NULL AND lvl."SiteId" = "Site"."id")'), 'the_geom']
        //[models.sequelize.fn('COUNT', models.sequelize.col('Levels.id')), 'levels'],
        //[models.sequelize.fn('ST_AsGeoJSON',
        //models.sequelize.fn('ST_CENTROID',
        //  models.sequelize.fn('ST_UNION',
        //  models.sequelize.fn('ST_TRANSFORM', models.sequelize.col('"Levels.Buildings.geometry"'),srid))), 15, 2
        //),
        //'the_geom']
      ],
      //include: [{
      //  model: models.Level,
      //  include: {model: models.Building, attributes: []},
      //  attributes: []
      //}]
    }).then(function(sites) {
      res.json(sites);
    });

[milovanderlinden]

And slightly faster after adding some indices and rewriting the join to a simple where:

var srid = req.query.srid || 4326;
    models.Site.findAll({
      group: [
        'Site.id', 'Site.name', 'Site.title', 'Site.checked', 'Site.assistance',// 'Levels.id'
      ],
      attributes:[
        'id',
        'name',
        'title',
        'checked',
        'assistance',
        [models.sequelize.literal('(SELECT COUNT(*) FROM opendispatcher."Levels" lvl where lvl."SiteId" = "Site"."id")'), 'levels'],
        [models.sequelize.literal('(SELECT ST_ASGEOJSON(ST_CENTROID(ST_UNION(ST_TRANSFORM("geometry", 4326))), 15, 2) ' +
          'from opendispatcher."Levels" lvl, "opendispatcher"."Buildings" bdg where lvl."id" = bdg."LevelId" ' +
          'AND bdg."deletedAt" IS NULL AND lvl."SiteId" = "Site"."id")'), 'the_geom']
        //[models.sequelize.fn('COUNT', models.sequelize.col('Levels.id')), 'levels'],
        //[models.sequelize.fn('ST_AsGeoJSON',
        //models.sequelize.fn('ST_CENTROID',
        //  models.sequelize.fn('ST_UNION',
        //  models.sequelize.fn('ST_TRANSFORM', models.sequelize.col('"Levels.Buildings.geometry"'),srid))), 15, 2
        //),
        //'the_geom']
      ],
      //include: [{
      //  model: models.Level,
      //  include: {model: models.Building, attributes: []},
      //  attributes: []
      //}]
    }).then(function(sites) {
      res.json(sites);
    });

[milovanderlinden]

This solution works for me. But I guess you guys may want to investigate further.

[mickhansen]

Really advanced aggregates can be hard to express in an ORM.

[janmeier]

+1 on that, not sure we could every come up with a good syntax for expression that advanced aggregates