Ent: Feature Request: Soft Deletes

[errorhandler]

Are there any plans to support "soft" deletes? That is, assuming the File schema has a deleted_at column, the code:

client.File.
    Delete().
    Where(file.UpdatedAtLT(date))
    Exec(ctx)

Would execute (approximately):

UPDATE files SET deleted_at = NOW() WHERE updated_at < ?

and

client.File.
    Query().
    All(ctx)

Would execute (approximately):

SELECT * FROM files WHERE deleted_at IS NULL

[danf0rth]

This is good feature, but i see two ways how to implement it, with possibility to gain more flexibility:

1. Create a some kind of flag in entity declaration, that will works as a stated above (not universal)
2. Create a some kind of flexible Mixin, that can intercept queries and patch them to add custom logic.

The second is more flexible, but also more complicated.
For example, we create a SoftDelete mixin.

type SoftDelete struct{}

func (SoftDelete) Fields() []ent.Field {
    return field.Time("deleted_at").Optional()
}

Now we add interceptors to Delete and Query query builders, that will entercepts and patch queries. It is important that DeleteQuery should be able to be patched into UpdateQuery, so that delete is basically not even happen.

func (SoftDelete) Delete() ent.Builder { 
    // here we return Update statement, that is extends delete with all the WHERE condtions, but providing a SET instruction to update single field
    return builder.Update().SetDeletedAt(time.Now())
}

func (SoftDelete) Query() ent.Builder {
    // here we return patched Query, that just add a ... deleted_at IS NULL
    return builder.Query().Where(
        softdelete.DeletedAt
    )
}

So basically query that was:

DELETE FROM pets WHERE id = 1

become

UPDATE pets SET deleted_at = ? WHERE id = ?

And query that was, for example:

SELECT * FROM pets WHERE name = ? AND age = ?

become

SELECT * FROM pets WHERE (name = ? AND age = ?) AND deleted_at IS NULL

Pros:

• can create any kind of Mixin that can patch our requests
  
  
  
  • increment field on update to see how many times entity was updated
  
  
  • lazy migration, user updates fullname, we split it into first_name and last_name
  
  

Cons:

• too complicated
• queries can be patched wrong way so we get undefined behavior that difficult to debug
• probably many others that did not come to my mind

[covrom]

What about Default Fields? I.e. in most common cases, this is "Id", "CreatedAt", "UpdatedAt", "DeletedAt".

[lixin9311]

At least we need multi-key unique constraints (some_key, deleted_by).

[cgfork]

Any plan for this feature?