Get number of associated elements in Sequelize-CodePudding

So I have a many-to-many relationship with Sequelize. This code gives me an array of all the categories associated with the Post. It works to get this data. However, if I would like to make that list of categories into just a single key value pair of how many categories instead of the categories. How could I do that?

return models.Post.findAndCountAll({
        limit: limit,
        offset: offset,
        include: [{
            model: models.Category,
            as: 'categories',
            required: false,
        }],
    })

For example this is the current output:

{
  "id": 1,
  "name": "foo",
  "categories": [
    {
      "id": 1,
      "name": "bar"
    }
  ]
}

The desired output:

{
  "id": 1,
  "name": "foo",
  "categories": 10
}

EDIT: As suggestions for fixing this I tried doing this:

return models.Post.findAndCountAll({
        group: ['post.id'],
        attributes: { 
            include: [[db.sequelize.fn("COUNT", db.sequelize.col("categories.id")), "categoriesCount"]] 
        },

        limit: limit,
        offset: offset,
        include: [{
            model: models.Category,
            as: 'categories',
            required: true,
            attributes: []
        }],
        raw: true,
        subQuery: false
    })

But that just gives me the error:

{
    "message": "invalid reference to FROM-clause entry for table \"post\""
}

This is basically what I want to get back, i wrote it in SQL and tried it:

SELECT
    cp.category_id as category_id,
    p.name as post_name,
    COUNT(p.id) as num_categories
FROM
    category c,
    category_post cp
JOIN
    post p ON p.id = cp.category_id
WHERE
    p.id = cp.post_id AND
    p.created_at >= '2022-01-26' and p.created_at <= '2022-05-02'
GROUP BY
    cp.category_id,
    post_name
ORDER BY
    num_categories DESC

Generated SQL with Sequelize:

Executing (default): SELECT "post"."id", count("post"."id") AS "count" FROM "post" AS "post" INNER JOIN ( "category_post" AS "categories->categoryPost" INNER JOIN "category" AS "categories" ON "categories"."id" = "categories->categoryPost"."category_id") ON "post"."id" = "categories->categoryPost"."post_id" GROUP BY "post"."id";
Executing (default): SELECT "post"."id", "post"."name", COUNT("categories"."id") AS "categoryCount", "categories->categoryPost"."id" AS "categories.categoryPost.id", "categories->categoryPost"."category_id" AS "categories.categoryPost.category_id", "categories->categoryPost"."post_id" AS "categories.categoryPost.post_id" FROM "post" AS "post" INNER JOIN ( "category_post" AS "categories->categoryPost" INNER JOIN "category" AS "categories" ON "categories"."id" = "categories->categoryPost"."category_id") ON "post"."id" = "categories->categoryPost"."post_id" GROUP BY "post"."id" LIMIT 3 OFFSET 0;

In my Post model:

static associate(models) {
      this.belongsToMany(models.Category, {
        through: models.CategoryPost,
        as: 'posts',
        foreignKey: 'category_id',
        onDelete: 'CASCADE'
      })
    }

In my Category model:

static associate(models) {
      this.belongsToMany(models.Post, {
        through: models.CategoryPost,
        as: 'categories',
        foreignKey: 'post_id',
        onDelete: 'CASCADE'
      })
    }

CodePudding user response：

If Post is the parent and Category is the child and you want to find the number of categories for a given post... you can u se the following way..

return models.Post.findAll({
        attributes: { 
          include: [[Sequelize.fn("COUNT", Sequelize.col("categories.id")), "cetegoryCount"]] 
        },
        include: [{
            model: models.Category,
            as: 'categories'
        }],
    })

CodePudding user response：

group in Postgres usually have some issues(Aggregate funcction issues).

Alternatively, you can use OVER PARTITION BY syntax which usually works in this situation.

const posts = await models.Post.findAndCountAll({
    attributes: { 
        include: [[db.sequelize.literal('(COUNT("categories"."id") OVER (PARTITION BY "post"."id")::int)'), 'categories']] 
    },
    limit: limit,
    offset: offset,
    include: [{
        model: models.Category,
        as: 'categories',
        required: true,
        attributes: [],
        through: {
            attributes: []
        }
    }],
    raw: true,
    subQuery: false
})

This should return something like this.

{
  "result": {
    "count": 2,    // This count is for post
    "rows": [
      {
        "id": 1,
        "name": "post",
        "categories": 2,
        ...
      }
    ]
  }
}