On Fri, Nov 23, 2018 at 10:48 AM Valery Tikhonov <[email protected]> wrote: > > Hi, I have two connected problems. > > First one is in subquery: > > query = db.session.query(OrderColorDistribution, Order, Article) \ > .filter(OrderColorDistribution.color_id == color_id) \ > .join(Order, Order.order_id == OrderColorDistribution.order_id) \ > .join(Article, and_(Article.ax_item_id == Order.ax_item_id, > Article.group_id == group_id)) > return query.subquery('ocr_subquery') > > > both OrderColorDistribution and Order contains order_id. So it returns query: > SELECT order_color_distribution.order_id AS order_id, ... orders.order_id AS > order_id, > > My question is - how to relabel AS to have table prefix for all fields from > this table?
When you work with subquery() you need to add with_labels: https://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=subquery%20with_labels#sqlalchemy.orm.query.Query.subquery.params.with_labels > > And now main question: > In the main query I join this subquery: > > query = query.outerjoin(ocr_subquery, and_(ocr_subquery.c.date == > PlanMonthly.date, > ocr_subquery.c.color_id == > PlanMonthly.color_id, > ocr_subquery.c.group_id == > PlanMonthly.group_id)) > query = query.options(contains_eager(PlanMonthly.orders_by_color) > .contains_eager(OrderColorDistribution.order)) > > > Each PlanMonthly contains multiple OrderColorDistributions, and each > OrderColorDistribution contain one order. > I would like to have them all loaded at once, as I have this data in a query. > > But - without alias contains_eager doesn't know where to get data from. (With > alias it adds additional FROM, which is completely incorrect). if it's adding a FROM then the alias you are passing is not being interpreted correctly. Here's an example of what it seems you want to do, works on this end, if you can modify it with your actual mappings to show how it is failing we can see what the problem is. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) bs = relationship("B") class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id")) c_id = Column(ForeignKey("c.id")) c = relationship("C") class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) c1, c2 = C(), C() s.add_all([A(bs=[B(c=c1), B(c=c2)]), A(bs=[B(c=c2), B(c=c2)])]) subq = s.query(B, C).join(B.c).subquery(with_labels=True) q = s.query(A).join(subq, A.id == subq.c.b_a_id).options( contains_eager(A.bs, alias=subq).contains_eager(B.c, alias=subq)) for row in q: for b in row.bs: print("B: %s C: %s" % (b, b.c)) > I need to make: > 1. subquery use table prefix for all fields (like label for single field) to > have orders.order_id AS orders_order_id > 2. make first contains_eager use label (like alias, but without FROM > modifications) ocr_subquery.order_color_distributions_<column> > 3. make second contains_eager use label ocr_subquery.orders_<column> > > Is it possible? > > Many thanks. > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
