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. > > 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 >> >> >> 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 > > <https://groups.google.com/d/msgid/sqlalchemy/1fa888e3-a888-4245-912f-6500d23f3620n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/4618b656-8abb-4ff7-83dd-cc7b550437a1%40www.fastmail.com.
