Re: [sqlalchemy] Soft Delete Pattern

[Andrew Martin]

This is awesome!

Thank you, Mike!

-andrew

On Monday, July 3, 2023 at 11:05:28 PM UTC-5 Mike Bayer wrote:

> this is a major area of functionality that begins with the 
> "with_loader_criteria" feature: 
> https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.with_loader_criteria
>
> integration to make the criteria automatic follows at 
> https://docs.sqlalchemy.org/en/20/orm/session_events.html#adding-global-where-on-criteria
>  
>
> there's an example of this here: 
> https://docs.sqlalchemy.org/en/20/_modules/examples/extending_query/filter_public.html
>
>
> On Mon, Jul 3, 2023, at 11:13 PM, Andrew Martin wrote:
>
> Hello,
>
> I have a base class I tend to use that includes a Boolean is_deleted field 
> so that pretty much every object has that available.
>
> Is there a good pattern for setting a filter on these objects that 
> automatically adds a WHERE is_deleted = 'false'?
>
> Or does that just have to be added as a filter on every .query(...) 
> statement?
>
>
> Thanks,
>
> andrew
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/08e50347-ebd9-47a4-b9f8-43b95ebe4e6bn%40googlegroups.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/70e5a166-efaa-430f-a2fc-85852ccd59ccn%40googlegroups.com.

Re: [sqlalchemy] Soft Delete Pattern

[Mike Bayer]

this is a major area of functionality that begins with the 
"with_loader_criteria" feature: 
https://docs.sqlalchemy.org/en/20/orm/queryguide/api.html#sqlalchemy.orm.with_loader_criteria

integration to make the criteria automatic follows at 
https://docs.sqlalchemy.org/en/20/orm/session_events.html#adding-global-where-on-criteria
 

there's an example of this here: 
https://docs.sqlalchemy.org/en/20/_modules/examples/extending_query/filter_public.html


On Mon, Jul 3, 2023, at 11:13 PM, Andrew Martin wrote:
> Hello,
> 
> I have a base class I tend to use that includes a Boolean is_deleted field so 
> that pretty much every object has that available.
> 
> Is there a good pattern for setting a filter on these objects that 
> automatically adds a WHERE is_deleted = 'false'?
> 
> Or does that just have to be added as a filter on every .query(...) statement?
> 
> 
> Thanks,
> 
> andrew
> 
> 
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/08e50347-ebd9-47a4-b9f8-43b95ebe4e6bn%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/7f628110-e543-492d-8acc-82bd577f1477%40app.fastmail.com.

[sqlalchemy] Soft Delete Pattern

[Andrew Martin]

Hello,

I have a base class I tend to use that includes a Boolean is_deleted field 
so that pretty much every object has that available.

Is there a good pattern for setting a filter on these objects that 
automatically adds a WHERE is_deleted = 'false'?

Or does that just have to be added as a filter on every .query(...) 
statement?


Thanks,

andrew

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/08e50347-ebd9-47a4-b9f8-43b95ebe4e6bn%40googlegroups.com.

Re: [sqlalchemy] Should I use a surrogate primary key on an Association Object pattern?

['Michael Mulqueen' via sqlalchemy]

Hi Pierre,

This isn't an official answer, I'm just a long time user of SQLAlchemy.

Either way should work fine. The association object is driven by the
columns on the association table being FKs, whether or not they're part of
a PK isn't relevant.

I've used both ways. In my experience, an artificial PK is easier to
maintain in the long run. Each way has its minor advantages and
disadvantages, but generally a single artificial PK would be my preference.

Mike

On Mon, 3 Jul 2023, 16:43 Pierre Massé,  wrote:

> Dear all,
>
> I am currently reworking a bit of my model and stumbled into this
> question, which I think mainly has opinionated answers - but I would like
> to have some insight regarding SQLAlchemy usage or preferences.
>
> I have a situation where I am in the exact same case like the one
> described in the Association Object
> 
>  in
> the SQLAlchemy ORM docs.
>
> I want to modelize :
> - Caregiver - a person taking care of one or more Helpee
> - Helpee - a person who is being taken care of, by one or more Caregiver
> - their Relationship, which links a Caregiver to a Helpee, but with
> additional data like their family ties (spouse, parent, friend, ...)
>
> This is typically the Association object use case, a many to many
> relationship, holding additional data.
>
> So far, I have been using a "natural" primary key on the Relationship
> table, by using the Caregiver Id, and the Helpee Id to form a composite
> primary key.
>
> From a handful of blog posts (this StackOverflow answer
>  being quite in depth), it looks
> like adding an "artificial" or surrogate primary key on the Relationship
> table should be the way to go. Of course, I would keep a unique constraint
> on (Caregiver Id x Helpee Id) on this table along the new primary key.
>
> My questions are :
> - is the addition of a surrogate primary key a good idea - without taking
> into account the fact that I am using SQLAlchemy?
> - would the "magic" of the association object still operate even though
> the mapped ORM relationships would not be part of the primary key anymore?
>
> The docs example would become:
>
>
> from typing import Optional
>
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.orm import Mapped
> from sqlalchemy.orm import mapped_column
> from sqlalchemy.orm import DeclarativeBase
> from sqlalchemy.orm import relationship
>
>
> class Base(DeclarativeBase):
> pass
>
>
> class Association(Base):
> __tablename__ = "association_table"
> *id: Mapped[int] = mapped_column(primary_key=True)*
> left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*,
> primary_key=True*)
> right_id: Mapped[int] = mapped_column(
> ForeignKey("right_table.id")*, primary_key=True*
> )
> extra_data: Mapped[Optional[str]]
> child: Mapped["Child"] = relationship(back_populates="parents")
> parent: Mapped["Parent"] = relationship(back_populates="children")
> *__table_args__ = (UniqueConstraint('left_id', 'right_id',
> name='_relationship_uc'),)*
>
>
> class Parent(Base):
> __tablename__ = "left_table"
> id: Mapped[int] = mapped_column(primary_key=True)
> children: Mapped[List["Association"]] =
> relationship(back_populates="parent")
>
>
> class Child(Base):
> __tablename__ = "right_table"
> id: Mapped[int] = mapped_column(primary_key=True)
> parents: Mapped[List["Association"]] =
> relationship(back_populates="child")
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com
> 
> .
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 

[sqlalchemy] Should I use a surrogate primary key on an Association Object pattern?

[Pierre Massé]

Dear all,

I am currently reworking a bit of my model and stumbled into this question,
which I think mainly has opinionated answers - but I would like to have
some insight regarding SQLAlchemy usage or preferences.

I have a situation where I am in the exact same case like the one described
in the Association Object

in
the SQLAlchemy ORM docs.

I want to modelize :
- Caregiver - a person taking care of one or more Helpee
- Helpee - a person who is being taken care of, by one or more Caregiver
- their Relationship, which links a Caregiver to a Helpee, but with
additional data like their family ties (spouse, parent, friend, ...)

This is typically the Association object use case, a many to many
relationship, holding additional data.

So far, I have been using a "natural" primary key on the Relationship
table, by using the Caregiver Id, and the Helpee Id to form a composite
primary key.

>From a handful of blog posts (this StackOverflow answer
 being quite in depth), it looks like
adding an "artificial" or surrogate primary key on the Relationship table
should be the way to go. Of course, I would keep a unique constraint on
(Caregiver Id x Helpee Id) on this table along the new primary key.

My questions are :
- is the addition of a surrogate primary key a good idea - without taking
into account the fact that I am using SQLAlchemy?
- would the "magic" of the association object still operate even though the
mapped ORM relationships would not be part of the primary key anymore?

The docs example would become:


from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
pass


class Association(Base):
__tablename__ = "association_table"
*id: Mapped[int] = mapped_column(primary_key=True)*
left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*,
primary_key=True*)
right_id: Mapped[int] = mapped_column(
ForeignKey("right_table.id")*, primary_key=True*
)
extra_data: Mapped[Optional[str]]
child: Mapped["Child"] = relationship(back_populates="parents")
parent: Mapped["Parent"] = relationship(back_populates="children")
*__table_args__ = (UniqueConstraint('left_id', 'right_id',
name='_relationship_uc'),)*


class Parent(Base):
__tablename__ = "left_table"
id: Mapped[int] = mapped_column(primary_key=True)
children: Mapped[List["Association"]] =
relationship(back_populates="parent")


class Child(Base):
__tablename__ = "right_table"
id: Mapped[int] = mapped_column(primary_key=True)
parents: Mapped[List["Association"]] =
relationship(back_populates="child")

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com.

Re: [sqlalchemy] Left join and nested inner join

[Mike Bayer]

use the join construct directly

from sqlalchemy.orm import join


stmt = select(A).outerjoin(join(B, C), A.id == B.a_id)



On Mon, Jul 3, 2023, at 8:29 AM, Michael Ekoka wrote:
> 
> Hi, I'm looking for the SQLAlchemy equivalent to the query
> 
> SELECT *
> FROM a 
> LEFT OUTER JOIN (b INNER JOIN c ON b.id = c.b_id) 
> ON a.id = b.a_id
> 
> Related:
> https://stackoverflow.com/a/56815807/56974
> https://stackoverflow.com/questions/25514160/nested-joins-in-sqlalchemy
> 
> Table "b" and "c" are joined and filtered first, then the outer join is 
> applied. I was able to achieve the same results using a subquery, whose 
> fields I was subsequently able to load using `contains_eager`. FYI
> 
> subq = session.query(B).join(C).subquery(with_labels=True)
> q = (session.query(A)
>  .outerjoin(subq, A.id==subq.c.b_a_id)
>  .options(contains_eager(A.b, alias=subq)
>   .options(contains_eager(B.c, alias=subq
> r = q.all()
> 
> I'm curious whether there's an equivalent using the above nested join syntax.
> 
> Thanks.
> 
> 
> 
> 
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/281524be-e25c-4e37-ad18-397a0908d53b%40app.fastmail.com.

[sqlalchemy] Left join and nested inner join

[Michael Ekoka]

Hi, I'm looking for the SQLAlchemy equivalent to the query

SELECT *
FROM a 
LEFT OUTER JOIN (b INNER JOIN c ON b.id = c.b_id) 
ON a.id = b.a_id

Related: 
https://stackoverflow.com/a/56815807/56974
https://stackoverflow.com/questions/25514160/nested-joins-in-sqlalchemy 

Table "b" and "c" are joined and filtered first, then the outer join is 
applied. I was able to achieve the same results using a subquery, whose 
fields I was subsequently able to load using `contains_eager`. FYI

subq = session.query(B).join(C).subquery(with_labels=True)
q = (session.query(A)
 .outerjoin(subq, A.id==subq.c.b_a_id)
 .options(contains_eager(A.b, alias=subq)
  .options(contains_eager(B.c, alias=subq
r = q.all()

I'm curious whether there's an equivalent using the above nested join 
syntax.

Thanks.


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com.