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 On Saturday, 6 February 2021 at 13:42:54 UTC Chris Simpson wrote: > 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/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com.
