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!