2

I am testing a basic aggregation function using counts from Sequelize and here's my type Counts:

type Creserve {
    id: ID!
    rDateStart: Date!
    rDateEnd: Date!
    grade: Int!
    section: String!
    currentStatus: String!
    user: User! 
    cartlab: Cartlab! 
}
type Counts {
    section: String!
    count: Int
}
type Query {
    getBooking(id: ID!): Creserve!
    allBookings: [Creserve]
    getBookingByUser(userId: ID): Creserve
    upcomingBookings: [Creserve]  
    countBookings: [Counts]
}

I am using countBookings as my query for aggregate functions and here's my resolver for the query:

countBookings: async (parent, args, {models}) => 
    {
      const res = await models.Creserve.findAndCountAll({
        group: 'section',
        attributes: ['section', [Sequelize.fn('COUNT', 'section'), 'count']]
      });
        return res.rows;
  },

The query that it outputs is this:

Executing (default): SELECT "section", COUNT('section') AS "count" FROM "Creserve" AS "Creserve" GROUP BY "section";

And tried this query in my psql shell and it's working fine:

 section | count
---------+-------
 A       |     2
 R       |     2

However, when I tried querying countBookings in my GraphQL Playground, section is returned but not the count:

    {
  "data": {
    "countBookings": [
      {
        "section": "A",
        "count": null
      },
      {
        "section": "R",
        "count": null
      }
    ]
  }
}

Is there something I missed out? Or is this a bug? This is the answer I tried following to with this example: https://stackoverflow.com/a/45586121/9760036

Thank you very much!

edit: returning a console.log(res.rows) outputs something like this:

   [ Creserve {
    dataValues: { section: 'A', count: '2' },
    _previousDataValues: { section: 'A', count: '2' },
    _changed: {},
    _modelOptions:
     { timestamps: true,
       validate: {},
       freezeTableName: true,
       underscored: false,
       underscoredAll: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: null,
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: [],
       indexes: [],
       name: [Object],
       omitNull: false,
       hooks: [Object],
       sequelize: [Sequelize],
       uniqueKeys: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       raw: true,
       attributes: [Array] },
    __eagerlyLoadedAssociations: [],
    isNewRecord: false },
  Creserve {
    dataValues: { section: 'R', count: '2' },
    _previousDataValues: { section: 'R', count: '2' },
    _changed: {},
    _modelOptions:
     { timestamps: true,
       validate: {},
       freezeTableName: true,
       underscored: false,
       underscoredAll: false,
       paranoid: false,
       rejectOnEmpty: false,
       whereCollection: null,
       schema: null,
       schemaDelimiter: '',
       defaultScope: {},
       scopes: [],
       indexes: [],
       name: [Object],
       omitNull: false,
       hooks: [Object],
       sequelize: [Sequelize],
       uniqueKeys: {} },
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       raw: true,
       attributes: [Array] },
    __eagerlyLoadedAssociations: [],
    isNewRecord: false } ]

Here's for res.count:

Executing (default): SELECT "section", COUNT('section') AS "count" FROM "Creserve" AS "Creserve" GROUP BY "section";
[ { count: '2' }, { count: '2' } ]
James David Bradly Carballo
  • 23
  • 5

1 Answers1

0

Problem

Actually you are doing everything right here... but what is happening here is the sequlize doesn't return plain object... It always returns the data in form of instance like that

[ Creserve {
    dataValues: { section: 'A', count: '2' },
    _previousDataValues: { section: 'A', count: '2' },
    _changed: {},
    _modelOptions:
     { timestamps: true,

Solution

I am not sure but there is no other way instead of looping and makes response to json object...

const array = []
res.rows.map((data) => {
  array.push(data.toJSON())
})
return array
Ashh
  • 44,693
  • 14
  • 105
  • 132
  • I added console.log for res.rows and res.count and it seems to output as to how to wanted me to change it. How do you return both? Sorry I am just new to javascript I am still currently learning. And both counts are returning a string, not an integer. – James David Bradly Carballo May 17 '18 at 03:04
  • There. Completed the response. The fields in res.rows are inside the DataValues. – James David Bradly Carballo May 17 '18 at 03:24
  • Here's the output: Executing (default): SELECT "section", COUNT('section') AS "count" FROM "Creserve" AS "Creserve" GROUP BY "section"; { section: 'A', count: '2' } – James David Bradly Carballo May 17 '18 at 04:26
  • Hey I tried it and playground read the response. Is there a way to append objects in an array from that response? Or convert it to an array? ``` const array = [{ section: "A", count: 2 }, { section: "R", count: 2 }]; ``` response: "data": { "countBookings": [ { "section": "A", "count": "2" }, { "section": "R", "count": "2" } ] } } – James David Bradly Carballo May 17 '18 at 13:22
  • Yes that response came from the GraphQL Playground so it did read the array you gave. And I found out that doing a findAndCountAll then doing a function count in the attributes is already redundant that's why it does a count in both res.rows and res.count. – James David Bradly Carballo May 17 '18 at 13:52
  • `{ const array = []; const res = await models.Creserve.findAndCountAll({ group: 'section', attributes: ['section', [Sequelize.fn('COUNT', 'section'), 'count']] }).then(response => { return array.push(response.rows[0].toJSON()); }) }` So i tested how to make the response into an array and I know it's a bit messy of a code but I'll just make a forEach later but yeah I guess. Thanks. Do you have any suggestions on how to make this more efficient? – James David Bradly Carballo May 17 '18 at 14:18
  • Cool that's better than the for loop I currently do now. Thanks a lot – James David Bradly Carballo May 18 '18 at 02:24