Typescript sequelize only returns aggregation function, without any other fields

Viewed 367

I've got a nest project that connects to a postgres DB. Right now I have two tables, one called "account" and the other called "agent". Account has a one to many relationship with Agent, as defined below.

@Table({
  schema: "public",
  name: {
    singular: "accounts",
    "plural": "accounts"
  },
  tableName: "accounts",
  timestamps: false
})
export class Accounts extends Model<Accounts> {

  @Column({field: "account_id", primaryKey: true})
  accountId: number

  @Column({field: "account_name"})
  accountName: string

  @Column({field: "created_at"})
  createdAt: Date

  @Column({field: "last_modify_date"})
  lastModifyDate: Date

  @Column
  notes: string

  @HasMany(() => Agent)
  agents: Agent[]
}

@Table({schema: "public",
  name: {
    singular: "agent",
    "plural": "agents"
  },
  tableName: "agents",
  timestamps: false
})

export class Agent extends Model<Agent> {
  @Column({field: "agent_id", primaryKey: true})
  agentId: number

  @Column({field: "last_update_time"})
  lastUpdateTime: Date

  @Column({field: "agent_name"})
  agentName: string

  @Column
  timezone: string

  @Column
  language: string

  @ForeignKey(() => Accounts)
  @Column({field: "account_id"})
  accountId: number
  }

The database was predefined so I had to use explicit field names.

Running on Nest, I have an "AccountService" which is meant to retrieve accounts as well as run some aggregations on my data, so for example I want to be able to get an account by its ID, as well as determine the most recent change any of its agents has had, so basically, in sql, I want to join the accounts table with an aggregation of select max(last_update_time), account_id from agents group by account_id.

I wrote the following query in sequelize:

const retrievedAccount = await this.accountModel.findByPk(accountId, {
          attributes: [
            [fn('max', col('last_update_time')), 'latest_update_time'],
            'Accounts.account_id',
            'Accounts.account_name',
            'Accounts.created_at',
            'Accounts.last_modify_date',
            'Accounts.notes'
          ],
          include: [{
            attributes: [],
            model: Agent
          }],
          group: ['Accounts.account_id',
            'Accounts.account_name',
            'Accounts.created_at',
            'Accounts.last_modify_date',
            'Accounts.notes']
        });

And it works, except that my return value, despite me asking in my attributes for all the columns in Accounts, I only get the "latest_update_time" in the dataset:

Here's the resulting retrievedAccount.toJSON():

{ "latest_update_time": "15:19:44.147562" }

What am I doing wrong here?

Thanks!

0 Answers
Related