Update model instance with loaded aggregate columns

[DZ8540]

Hello there! 👋

I want to update model instance like this

export default class User extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public login: string

  @column({ serializeAs: null })
  public password: string

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime

  // Here is an aggregate column
  @column({ columnName: 'profiles_count' })
  public profilesCount: number

  @hasMany(() => Profile)
  public profiles: HasMany<typeof Profile>
}

which loaded aggregate column like this

await item.loadCount('profiles')

but it throws error like this

column "profiles_count" of relation "users" does not exist

I update model instance like this

// item is a user model instance
await item.merge(modelData).save()

How can i resolve this problem?

[MaximeMRF]

Hello @DZ8540

I have try the loadCount method on my model User

const user = await User.query()
        .where("id", auth.user!.id)
        .firstOrFail();
 await user.loadCount('profile');
 console.log(user.$extras)
 // { profile_count: 1 }

And here is my model:

export default class User extends BaseModel {
  @column({ isPrimary: true })
  public id: number;

  @column()
  public name: string;

  @hasOne(() => Profile)
  public profile: HasOne<typeof Profile>;

  @column()
  public email: string;

  @column({ serializeAs: null })
  public password: string;

  @column()
  public rememberMeToken: string | null;

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime;

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime;

  @beforeSave()
  public static async hashPassword(user: User) {
    if (user.$dirty.password) {
      user.password = await Hash.make(user.password);
    }
  }
}

When I console.log user.$extras it display me { profile_count: 1 } which is correct because I have one profile.

I hope my comment can help you, and if it's not true can you provide me more context ?

[DZ8540]

@MaximeMRF hey, thank you for your answer.

The problem is different, it is about an update model after load aggregate count.

[MaximeMRF]

Sorry for the confusion.

I tryed to update my model with a value that I have get with the loadCount method like you and update a column in the db with the value retuned by loadCount. And, it's work fine for me. Here is the piece of code that I used:

await user.loadCount('profile');
// display { profile_count: 1 }
console.log(user.$extras);
await user.merge({ profilesCount: user.$extras.profile_count }).save();

Here is my model:

export default class User extends BaseModel {
  @column({ isPrimary: true })
  public id: number;

  @column()
  public name: string;

  @hasOne(() => Profile)
  public profile: HasOne<typeof Profile>;

  // aggregate column for profile
  @column({ columnName: 'profiles_count' })
  public profilesCount: number;

  @column()
  public email: string;

  @column({ serializeAs: null })
  public password: string;

  @column()
  public rememberMeToken: string | null;

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime;

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime;

  @beforeSave()
  public static async hashPassword(user: User) {
    if (user.$dirty.password) {
      user.password = await Hash.make(user.password);
    }
  }
}

And migration:

export default class extends BaseSchema {
  protected tableName = "users";

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments("id").primary();
      table.string("name", 255).notNullable();
      table.string("email", 255).notNullable().unique();
      table.string("password", 180).notNullable();
      // aggregate column for profile
      table.integer("profiles_count").nullable();
      table.string("remember_me_token").nullable();

      /**
       * Uses timestampz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp("created_at", { useTz: true }).notNullable();
      table.timestamp("updated_at", { useTz: true }).notNullable();
    });
  }

  public async down() {
    this.schema.dropTable(this.tableName);
  }
}

[DZ8540]

@MaximeMRF you didn't define aggregate column, my model class defined it!

It need for me for use this column instead $extras

[thetutlage]

It should be a computed property. Checks docs for model computed properties

[DZ8540]

@thetutlage sounds like good, i'll try this later, thanks)

[rutonyru]

Hello,
I like this answer... but for my case : as I have a product VAT ID in my product, I would like to compute the price with vat. I have to load the VAT model, get the rate and compute. Is it possible in a computed properties ? What is the best solution (I'm using AdonisJS 6 :) )?
Do I have to open a new discussion for this ?
Thanks, Antoine