flipbit03 / sqlalchemy-easy-softdelete Goto Github PK

• SQLAlchemy Easy Soft-Delete version: 0.8.2
• Python version: 3.11.5
• Operating System: MacOS

Description

Attempting to use SoftDelete on queries with limit and offset.
When I use limit or (and) offset with a simple query, the query returns soft-deleted items too.
Looks like limit and offset override the query.

Example:

db.query(Account).all() - works fine
db.query(Account).limit(100).offset(0) - return all acounts with deleted accounts

Hello,

I've encountered a problem when using sqlalchemy_easy_softdelete with recursive Common Table Expressions (CTEs) in SQLAlchemy.

Environment:

SQLAlchemy version: "^2.0.14"
sqlalchemy_easy_softdelete version:"^0.7.1"
Python version:"^3.10"
Steps to Reproduce:

Define a model with soft deletion enabled.
Implement a recursive CTE query on this model.
Try to execute the query.
Expected Behavior:
The query should execute without errors and should not return the soft-deleted records.

Actual Behavior:
I received a NotImplementedError with the message: "Unsupported object <class 'sqlalchemy.sql.selectable.CTE'> in statement.froms".

Additional Details:
It appears the library currently doesn't support CTEs, specifically when they are used in the FROM clause of a SELECT statement.

It would be great if support for CTEs could be added, or at least a mechanism to bypass soft delete for specific queries when using recursive CTEs or other advanced SQLAlchemy features.

Thank you for your attention to this issue!

Having additional fields for soft deleted objects can be helpful to add visibility, for example (very rough prototype):

class SoftDeleteDetailedMixin(
    generate_soft_delete_mixin_class(...)
):
    deleted_at: datetime
    deleted_reason: Optional[str]
    deleted_by: Optional[str]

Then one would be able to do:

product.delete(deleted_reason="Not going to renew stock", deleted_by=admin_user.email)

I might have some time later this month to create a PR by myself, but I still wanted to write it out.

I like the project, thank you for developing it!

• SQLAlchemy Easy Soft-Delete version: 0.4.0
• Python version: 3.9
• Operating System: Mac OS

Description

Why only supporting python 3.10 and not 3.9 ? We're using AWS Lambda which has not released yet python 3.10 so we're stuck with python 3.9. I'm considering forking this to support python 3.9, is any there strong incompatibility ?

• SQLAlchemy Easy Soft-Delete version: 0.5.1
• Python version: 3.10.8
• Operating System: MacOS

Description

Attempting to use SoftDelete on complex queries with joins and filters.
Simple queries work fine, but more complex queries are returning soft-deleted items

What I Did

class SoftDeleteMixin(generate_soft_delete_mixin_class()):
    # type hint for autocomplete IDE support
    deleted_at: datetime


class Operator(Base, SoftDeleteMixin):
    __tablename__ = 'operators'

    id = Column(Integer, primary_key=True, index=True)
    region_id = Column(Integer, ForeignKey('regions.id'), index=True)
    name = Column(String(255), nullable=False)
    email = Column(String(255), unique=True, index=True)
    is_active = Column(Boolean, default=True, nullable=False)
    weight = Column(Integer, default=100, nullable=False)
    slack_userid = Column(String(32), unique=True)
    heimdall_id = Column(Integer, unique=True)

    operations = relationship('Operation', secondary=operators_operations_table, back_populates='operators')
    loads = relationship('OperatorLoad', back_populates='operator')
    plans = relationship('OperationPlan', back_populates='operator')
    attendances = relationship('OperatorAttendance', back_populates='operator')
    region = relationship('Region')
    assignment = relationship('AssignedDate', back_populates='operator')

Inside my operators router:

    return db.query(models.Operator) \
        .options(joinedload(models.Operator.operations), joinedload(models.Operator.region)) \
        .order_by(models.Operator.is_active.desc()) \
        .order_by(models.Operator.name) \
        .all()

This returns all operators, without respect for their deleted_at status.

However, if I simplify the query, I get only the records that are not soft deleted:

    return db.query(models.Operator).all()

• SQLAlchemy Easy Soft-Delete version: 0.8.2
• Python version: 3.10.7
• Operating System: Debian GNU/Linux 11 (bullseye)

Description

I want to get a deleted instance using Session.get(). I included execution_options but it doesn't work.
I still can use the query().filter(), but I think it's reasonable for an update.

What I Did

db: Session
deleted_id: int

obj = db.get(MyModel, deleted_id, execution_options={'include_deleted': True})
assert obj is not None  # failed

# a hotfix is
obj = db.query(MyModel).execution_options(include_deleted=True).filter(
    MyModel.id == deleted_id
).one()

• SQLAlchemy Easy Soft-Delete version: 0.2.1
• Python version: 3.10
• Operating System: Ubuntu
• SQLAlchemy: 1.4.40

Description

Tenho alguns filtros de texto que após a instalação quebraram, o erro retornado é seguinte:

Unsupported object <class 'sqlalchemy.sql.selectable.Subquery'> in statement.froms

Os filtros utilizam a seguinte abordagem:

query = self.session.query(Artigo)
...
artigos = query.filter(Artigo.titulo.ilike(texto))
artigos_por_autor = query.join(Autor).filter(Autor.nome.ilike(texto))
lista_artigos = [artigos, artigos_por_autor]
query = lista_artigos[0].union(*lista_artigos[1:])
...
query = query.all()

Tentei algumas soluções com: union_all, expect_, expect_all, e outras estruturas de união, mas continua retornando erro de subquery (já testei .subquery e outras funções que indicam subordinação) ou outros aleatórios relacionado a formatação.

• SQLAlchemy Easy Soft-Delete version: 0.8.2
• Python version: 3.9.11
• Operating System: macOS 13.4

Description

On models that don't inherit from the generated SoftDeleteMixin class, but also have a field of the same name (so a deleted_at field in this case), select results are still filtered without providing the execution option.

I can work around this, but it'd be nice if the filtering only applied to models inheriting from the mixin.

What I Did

class Note(BaseModel):
    __tablename__ = "notes"
   id = Column(Integer, primary_key=True, autoincrement=True)
   deleted_at = Column(DateTime, nullable=True)

new_note = Note(deleted_at=datetime.utcnow())
session.add(new_note)
session.commit(new_note)
item = Note.query.filter(Note.id == new_note.id).first()
assert item is not None  # fails

• SQLAlchemy Easy Soft-Delete version: 0.8.2
• Python version: 3.9.17
• Operating System: CentOS

Description

I have an SQLAlchemy query that uses an outer join, e.g.

session.query(models.Dataset)
    .distinct()
    .outerjoin(
        models.ShareObject,
        models.ShareObject.datasetUri == models.Dataset.datasetUri,
    )
    .outerjoin(
        models.DatasetTagConnection,
        models.DatasetTagConnection.datasetUri == models.Dataset.datasetUri,
    )

What I Did

All of the entities are SoftDeleteMixins, so the lib basically adds 3 filter statements at the end of the query:

select
	distinct dataset.name as dataset_name,
        -- more fields selected
from
	dataset
left outer join share_object on
	share_object."datasetUri" = dataset."datasetUri"
left outer join dataset_tag_connection on
	dataset_tag_connection."datasetUri" = dataset."datasetUri"
where
	share_object.deleted IS NULL
	and dataset_tag_connection.deleted IS NULL
	and dataset.deleted IS NULL;

But this is not how you would write the actual SQL query. Instead one would add the where clauses for the outer joins to the ON clause like this:

select
	distinct dataset.name as dataset_name,
        -- more fields selected
from
	dataset
left outer join share_object on
	share_object."datasetUri" = dataset."datasetUri"
        and share_object.deleted IS NULL
left outer join dataset_tag_connection on
	dataset_tag_connection."datasetUri" = dataset."datasetUri"
	and dataset_tag_connection.deleted IS NULL
where
	and dataset.deleted IS NULL;

I already tried to implement a fix myself but updating the ON clause is quite tricky and not natively supported.

For auditing purposes, it would be important to fill in the 'undeleted_at' column as well.

• SQLAlchemy Easy Soft-Delete version: v0.6.2
• Python version: 3.11
• Operating System: Linux

Description

Library does not support UNION queries generated using SQLAlchemy Core Statements

What I Did

I've created a query using SQLAlchemy Core statements which has a .union() on it, and the library failed with the following error:

Query:

    user_and_org_select = (
        select([user_table.c.id, text("user"), account_name_table.c.name, account_name_table.c.normalized_name])
        .select_from(user_table)
        .outerjoin(account_name_table, user_table.c.id == account_name_table.c.user_id)
    ).union(
        select([organization_table.c.id, text("organization"), account_name_table.c.name,
                account_name_table.c.normalized_name])
        .select_from(organization_table)
        .outerjoin(account_name_table, organization_table.c.id == account_name_table.c.organization_id)
    )

error

  File "/home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.11/site-packages/sqlalchemy_easy_softdelete/handler/sqlalchemy_easy_softdelete.py", line 20, in soft_delete_execute
    adapted = SoftDeleteQueryRewriter(deleted_field_name, disable_soft_delete_option_name).rewrite_statement(
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/cadu/.local/share/virtualenvs/kernel-backend-N3YlRYFF/lib/python3.11/site-packages/sqlalchemy_easy_softdelete/handler/rewriter/__init__.py", line 47, in rewrite_statement
    raise NotImplementedError(f"Unsupported statement type \"{(type(stmt))}\"!")
NotImplementedError: Unsupported statement type "<class 'sqlalchemy.sql.selectable.CompoundSelect'>"!

• SQLAlchemy Easy Soft-Delete version:
• Python version:
• Operating System:

Description

Describe what you were trying to get done.
Tell us what happened, what went wrong, and what you expected to happen.

What I Did

Paste the command(s) you ran and the output.
If there was a crash, please include the traceback here.