Thanks Mike, the assurance it's the right idea was what I wanted to check. All sorted much appreciated.
On Sun, 7 Feb 2021, 21:49 Mike Bayer, <mike...@zzzcomputing.com> wrote: > > > 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 > <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 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+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 > <https://groups.google.com/d/msgid/sqlalchemy/4618b656-8abb-4ff7-83dd-cc7b550437a1%40www.fastmail.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/CAC1wBiwj8MxDa1sgdC6_uZUUfaNsKrkG7swMeMSOUe228Stt2A%40mail.gmail.com.
