[sqlalchemy] 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 
<https://docs.sqlalchemy.org/en/13/orm/tutorial.html>,  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  
<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.

-- 
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/8ae57449-73b5-4c2a-81e8-aec971a42ff2n%40googlegroups.com.