SQLAlchemy join/has query with example code

[Chris Simpson]

Hello,

I'm trying to convert this working SQL query: (SQLAlchemy models are below)

SELECT COUNT(*)

FROM person

JOIN subscription ON

person.id = subscription.person_id

JOIN plan ON 

subscription.sku_uuid = plan.uuid

JOIN plan_requirements ON

plan.id = plan_requirements.plan_id

WHERE plan_requirements.subscription = 1

Into a SQLAlchemy query. so far from reading the docs,  I have the following:

database.session.query(Person).join(Subscription).filter(Subscription.plan.has() ).all()

With the objective: Show me all people who have at least one plan with the plan_requirements.subscription set to 1 (meaning true).

Do I need to somehow keep chaining my joins?

My SQLAlchemy Models are: (full code is also linked at end)

class Person(database.Model):

    __tablename__ = "person"

    id = database.Column(database.Integer(), primary_key=True)

    uuid = database.Column(database.String(), default=uuid_string)

    given_name = database.Column(database.String())

    family_name = database.Column(database.String())

    subscriptions = relationship("Subscription", back_populates="person")

class Plan(database.Model):

    __tablename__ = "plan"

    id = database.Column(database.Integer(), primary_key=True)

    uuid = database.Column(database.String(), default=uuid_string)

    requirements = relationship(

        "PlanRequirements", uselist=False, back_populates="plan"

    )

class PlanRequirements(database.Model):

    __tablename__ = "plan_requirements"

    id = database.Column(database.Integer(), primary_key=True)

    plan_id = database.Column(database.Integer(), ForeignKey("plan.id"))

    plan = relationship("Plan", back_populates="requirements")

    instant_payment = database.Column(database.Boolean(), default=False)

    subscription = database.Column(database.Boolean(), default=False)

Full source code of models: https://github.com/Subscribie/subscribie/blob/master/subscribie/models.py#L40 

Much appreciated if someone can point me in the right directly. I'm confident with the SQL quiery, just not how to convert that to the ORM.

[Chris Simpson]

After posting, I have arrived at a solution (which might be awful) Please let me know if this is a bad approach or I'm following the api correctly:

I have converted this SQL query:

SELECT COUNT(*)

FROM person

JOIN subscription ON

person.id = subscription.person_id

JOIN plan ON 

subscription.sku_uuid = plan.uuid

JOIN plan_requirements ON

plan.id = plan_requirements.plan_id

WHERE plan_requirements.subscription = 1

Into the following SQLAlchemy query: 

database.session.query(Person)\

.join(Subscription)\

.join(Plan, Subscription.sku_uuid==Plan.uuid)\

.join(PlanRequirements, Plan.id==PlanRequirements.plan_id)\

.filter(PlanRequirements.subscription==1).all()

Kind regards,

Chris

[Mike Bayer]

On Sat, Feb 6, 2021, at 8:56 AM, Chris Simpson wrote:

After posting, I have arrived at a solution (which might be awful) Please let me know if this is a bad approach or I'm following the api correctly:

I have converted this SQL query:

SELECT COUNT(*)

FROM person

JOIN subscription ON

person.id = subscription.person_id

JOIN plan ON 

subscription.sku_uuid = plan.uuid

JOIN plan_requirements ON

plan.id = plan_requirements.plan_id

WHERE plan_requirements.subscription = 1

Into the following SQLAlchemy query: 

database.session.query(Person)\

.join(Subscription)\

.join(Plan, Subscription.sku_uuid==Plan.uuid)\

.join(PlanRequirements, Plan.id==PlanRequirements.plan_id)\

.filter(PlanRequirements.subscription==1).all()

seems to be the right idea except you aren't emitting the "COUNT(*)" part of it, not sure if that's what you wanted.

--

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/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com.

[Chris Simpson]

Thanks Mike, the assurance it's the right idea was what I wanted to check.

All sorted much appreciated.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/ltlIuUdw4_U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4618b656-8abb-4ff7-83dd-cc7b550437a1%40www.fastmail.com.