CODEWITHSUNDEEP
postgresqlsequelize.js
bob_cobb
bob_cobb

Reputation: 2269

Sequelize: Returning a single COUNT entry instead of every row in a nested include

I'm trying to grab the total number of rows (count) from a nested include in Sequelize. I've followed countless questions on here, but can't seem to get it right.

Models:

Relationships:

 // Brand
 Brand.hasOne(models.BrandProfile, {
   foreignKey: 'brand_id',
   as: 'brand_profile'
 })

 // BrandProfile
 BrandProfile.belongsTo(models.Brand, {
   foreignKey: 'brand_id',
 })
 BrandProfile.hasMany(models.BrandPageView, {
   foreignKey: 'brand_id',
   as: 'brand_page_views'
 })

 // BrandPageView
 BrandProfile.belongsTo(models.BrandProfile, {
   foreignKey: 'brand_id',
 })

Now when I try to normally run my query like so:

const { count, rows } = await Brand.findAndCountAll({
  include: [
    {
      model: BrandProfile,
      as: 'brand_profile',
      include: [
        {
          model: BrandPageView,
          as: 'brand_page_views',
        },
      ],
    },
  ],
})

It returns the following:

{
  id: 1,
  created_at: '2020-12-26T20:42:19.930Z',
  updated_at: '2020-12-29T20:46:58.918Z',
  deleted_at: null,
  name: 'Test brand',
  slug: 'test-brand',
  status: 'disabled',
  brand_profile: {
    created_at: '2020-12-26T20:42:19.931Z',
    about: [ [Object], [Object], [Object], [Object] ],
    cleaned_about: null,
    subtitle: 'subtitle test',
    photo: '1609477287152.jpeg',
    photo_config: { scale: '1.00' },
    id: 1,
    updated_at: '2021-01-01T05:01:27.414Z',
    brand_id: 1,
    brand_page_views: [
      [Object], [Object],
      [Object], [Object],
      [Object], [Object],
      [Object]
    ]
  },
}

As you can see, brand.brand_profile.brand_page_views has a list of all objects. Now I just want to return the count, so I'm using the following query:

const { count, rows } = await Brand.findAndCountAll({
  include: [
      {
        model: BrandProfile,
        as: 'brand_profile',
        include: {
          model: BrandPageView,
          as: 'brand_page_views',
          attributes: [],
        },
      },
    ],
    attributes: {
      include: [
        [
          Sequelize.fn('COUNT', Sequelize.col('brand_profile.brand_page_views.brand_id')),
          'brand_page_views_count',
        ],
      ],
    },
    group: ['brand.id']
})

I get this error:

 'missing FROM-clause entry for table "brand"'

The SQL it outputs is:

SELECT "Brand"."id", "Brand"."created_at", "Brand"."updated_at", "Brand"."deleted_at", "Brand"."name", "Brand"."slug", "Brand"."status", COUNT("brand_profile->brand_page_views"."brand_id") AS "brand_profile.brand_page_views.count", "brand_profile"."created_at" AS "brand_profile.created_at", "brand_profile"."about" AS "brand_profile.about", "brand_profile"."cleaned_about" AS "brand_profile.cleaned_about", "brand_profile"."subtitle" AS "brand_profile.subtitle", "brand_profile"."photo" AS "brand_profile.photo", "brand_profile"."email" AS "brand_profile.email", "brand_profile"."photo_config" AS "brand_profile.photo_config", "brand_profile"."id" AS "brand_profile.id", "brand_profile"."updated_at" AS "brand_profile.updated_at", "brand_profile"."brand_id" AS "brand_profile.brand_id" FROM "brands" AS "Brand" LEFT OUTER JOIN "brand_profile" AS "brand_profile" ON "Br2021-01-05 01:32:26 [sequelize] INFO   Executing (default): SELECT "Brand"."id", "Brand"."created_at", "Brand"."updated_at", "Brand"."deleted_at", "Brand"."name", "Brand"."slug", "Brand"."status", COUNT("brand_profile->brand_page_views"."brand_id") AS "brand_profile.brand_page_views.count", "brand_profile"."created_at" AS "brand_profile.created_at", "brand_profile"."about" AS "brand_profile.about", "brand_profile"."cleaned_about" AS "brand_profile.cleaned_about", "brand_profile"."subtitle" AS "brand_profile.subtitle", "brand_profile"."photo" AS "brand_profile.photo", "brand_profile"."email" AS "brand_profile.email", "brand_profile"."photo_config" AS "brand_profile.photo_config", "brand_profile"."id" AS "brand_profile.id", "brand_profile"."updated_at" AS "brand_profile.updated_at", "brand_profile"."brand_id" AS "brand_profile.brand_id" FROM "brands" AS "Brand" LEFT OUTER JOIN "brand_profile" AS "brand_profile" ON "Brand"."id" = "brand_profile"."brand_id" LEFT OUTER JOIN "brand_page_views" AS "brand_profile->braand"."id" = "brand_profile"."brand_id" LEFT OUTER JOIN "brand_page_views" AS "brand_profile->brand_page_views" ON "brand_profile"."id" = "brand_profile->brand_page_views"."brand_id" WHERE "Brand"."id" = 1 GROUP BY "brand"."id";
nd_page_views" ON "brand_profile"."id" = "brand_profile->brand_page_views"."brand_id" WHERE "Brand"."id" = 1 GROUP BY "brand"."id";

I've honestly tried this so many ways, added brand_profile->brand_page_views.brand_id, brand_profile.brand_id to the group, but to no avail.

Upvotes: 0

Views: 506

Answers (1)

Anatoly
Anatoly

Reputation: 22803

Sequelize was not designed to support a wide variety of SQL aggregations. So in your case you should use sequelize.literal with a subquery to count child records:

attributes: {
      include: [
        [Sequelize.literal('(select COUNT(*) from brand_page_views where brand_page_views.brand_id=brand_profile.id)'),
          'brand_page_views_count'],
      ],
    },

Don't forget to remove group: ['brand.id']

Upvotes: 1

Related Questions