Implement insert & update for ActiveModel

[billy1624]

To avoid manually exec the insert ActiveModel. In below, we cannot simply save it because this is a junction table and the composite primary key should be set during insert.

sea-orm/tests/relational_tests.rs

Lines 562 to 569 in bfaa7d4

	let bobby_cakes_bakers = cakes_bakers::ActiveModel {
	cake_id: Set(chocolate_cake_res.last_insert_id as i32),
	baker_id: Set(baker_bobby_res.last_insert_id as i32),
	..Default::default()
	};
	CakesBakers::insert(bobby_cakes_bakers)
	.exec(&ctx.db)
	.await?;

Perhaps impl it here

sea-orm/sea-orm-macros/src/derives/active_model.rs

Lines 39 to 47 in 8cfa142

	impl ActiveModel {
	pub async fn save(self, db: &sea_orm::DatabaseConnection) -> Result<Self, sea_orm::DbErr> {
	sea_orm::save_active_model::<Self, Entity>(self, db).await
	}
	pub async fn delete(self, db: &sea_orm::DatabaseConnection) -> Result<sea_orm::DeleteResult, sea_orm::DbErr> {
	sea_orm::delete_active_model::<Self, Entity>(self, db).await
	}
	}

[tyt2y3]

What is the final intended API?
I kind of get the problem, but I don't get the proposed solution.

[billy1624]

Add insert & update here

sea-orm/sea-orm-macros/src/derives/active_model.rs

Lines 39 to 47 in dd7585c

	impl ActiveModel {
	pub async fn save(self, db: &sea_orm::DatabaseConnection) -> Result<Self, sea_orm::DbErr> {
	sea_orm::save_active_model::<Self, Entity>(self, db).await
	}
	pub async fn delete(self, db: &sea_orm::DatabaseConnection) -> Result<sea_orm::DeleteResult, sea_orm::DbErr> {
	sea_orm::delete_active_model::<Self, Entity>(self, db).await
	}
	}

[marlon-sousa]

Hello,

I have a question related to this:

Suppose that I have an Active Model I want to use for updating a record. Not every value in the active model needs to be present, because I just need to update two columns. My understanding is that my active model needs to have the primary key filled (otherwise the update wouldn't be aware of what should be updated), plus the two columns I am interested in updating.

So that:

for this model

struct Model {
  id: int,
  name: String,
  phone: String,
  address: String,
}

If I need to update phone and address for a given record I would have

let val = ActiveModel {
  id: Set(1),
// name won't be filled here because I don't want to update the name
  phone: Set("1112223334"),
address: Set("ccccc"),
}.update(db)
.await

Assuming this is true, ghere are some questions:

1. What happens if record with id 1 doesn't exist? How do I know if a row is or is not updated? I know that the update() returns errors, but errors are not yet enumerated and I am not sure if this would or would not trigger an error.
2. Can I attach filters before triggering the update? This would be useful to only update for example a status field if the current value of the status field is equals to something for example (e.e update users set status = 'ACTIVE' where users.id = 2 and users.status = 'READY_TO_ACTIVATION';)
3. Now kind of a suggestion: Would it be possible to update_many based on the ActiveModel instead of using column expressions?

Suppose that I have a batch of users to activate. It would be much more fluent to do

let updated_values = ActiveModel {
  // not filling primary key here because this will be used mto update 0, one or more than one record (s)
  status: Set('ACTIVE'),
  status_reazon: Set("BATCH_ACTIVATED"),
}.prepare() // prepare doesn't exist in the api
.filter(collumns::id.in(Vec![1, 2, 3, 4, 6]))
.filter(columns::Status.eq("Ready_FOR_ACTIVATION")
.update_many()

Ratter than use columns expressions for everything, needing to specify their types, because activeValue already has everything we need to build safely a record in terms of types.

Thanks,

Marlon

[billy1624]

Hey @marlon-sousa,

1. What happens if record with id 1 doesn't exist? How do I know if a row is or is not updated? I know that the update() returns errors, but errors are not yet enumerated and I am not sure if this would or would not trigger an error.

I guess the rows_affected will be zero? I need to double check on this.

2. Can I attach filters before triggering the update? This would be useful to only update for example a status field if the current value of the status field is equals to something for example (e.e update users set status = 'ACTIVE' where users.id = 2 and users.status = 'READY_TO_ACTIVATION';)

Yes, you can! :)
https://www.sea-ql.org/SeaORM/docs/basic-crud/update#update-many

3. Now kind of a suggestion: Would it be possible to update_many based on the ActiveModel instead of using column expressions?

We just added this feature in a recent PR.
#129 (comment)

[tyt2y3]

For 1, yes at least the current intention is not to return error, as long as the statement executes successfully.
But it sounds like returning error if rows_affected is 0 a reasonable behaviour.

[marlon-sousa]

Hello,

There are several update's functions around, so this is probably confusing me.

The default implementation of update from ActiveModelTrait for example returns Self, so that I can not access the rows_affected because I am not calling exec on the entity, either this or generics all around the place are naking me confused.

As far as I understood (with 0.2.2) the update from ActiveModelTrait calls Entity::update(self).exec(db); and this exec returns the original record.

[tyt2y3]

Okay so update is limited to updating only 1 record.
If you are interested in rows_affected, you can use update_many.
rows_affected is always 1 if the row exists.

[marlon-sousa]

Thanks. The question though is ... what if the rrow doesn't exist when you call update()?

Will an error be triggered in the update function? This is what I miss in the documentation.

[tyt2y3]

Replied on above too.
To clarify, if a record does not exist: current behaviour is no error.
I think we can consider changing the behaviour to return an error if the record does not exist.

[alper]

Yeah, how do we do an upset?

[billy1624]

Upsert is under development #412. But seeming the PR is inactive. Perhaps you might want to contribute by starting a new PR :P

[marlon-sousa]

Yes, this would be great, and this is why I asked in the first place.

Why?

Because update() can be used with filters. One might, for example, update a record but only if some column of itself still has some value set.

example:

MyEntity::update(ActiveRecord {
    id: Set(5),
    status: Set("ACTIVE"),
})
.filter(column::Status.eq("READY_FOR_ACTIVATION")
.exec(&db)
.await

This is useful to handle idempotency problems: I want to update status but only if, in the meantime, it hasn't yet been updated by another oprocess or something similar.

In this situation (or if I provide an id which does not exist) I have to know if my update has occurred successfully.

One can arg that I could use update_many, but using update gives me the advantage to have the active model itself be returned, which as far as I understand is not the case with update_many.

[tyt2y3]

You are absolute right. I am convinced. Yes your code had got the idea. See the updated example bfa6691.