Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Glad I could help, but I don't understand what is going on. Neither ticket description nor the patch itself helped me. Sorry. What is select_from() good for when it generates a cartesian query? What MIGHT help ME (a lot infact) is a couple of DOs and DONTs examples in one place for all these three constructs OR A 'highlevel user-friendly step-by-step description' about what each construct does, so that I can see where they differ. Anyway, keep the excellent work! Ladislav Lenart On 31.5.2013 01:12, Michael Bayer wrote: this is very helpful because you are here running into an older feature that I think is not very applicable to modern usage, not to mention not terrifically documented, so I've added http://www.sqlalchemy.org/trac/attachment/ticket/2736/ to address changing the role of select_from() to be more what it says, and to offload a particular magic aspect of it into a new method called select_entity_from(). if you look at the docstrings added to the patch there, you may get a sense for what's been going on. I'll probably commit this soon and those docs will be up on the site. There will be an upgrade path for users in this case. On May 30, 2013, at 1:10 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. Sorry for the long delay. I finally had enough time to produce a minimal self-contained regression. The attached file produces the following SQL: WITH RECURSIVE q_cte(partner_id, max_depth) AS ( SELECT partner.id AS partner_id, 1 AS max_depth FROM partner WHERE partner.sponsor_id IS NULL UNION ALL SELECT partner_alias.id AS partner_id, max_depth + 1 AS max_depth FROM partner AS partner_alias, q_cte AS q_cte_alias WHERE partner_alias.sponsor_id = q_cte_alias.partner_id ) SELECT q_cte.partner_id AS q_cte_partner_id, -- suspicious partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id -- BUG I would expect this (q_cte part is correct): WITH RECURSIVE q_cte(partner_id, max_depth) AS ( ... ) SELECT partner.id AS partner_id, partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = partner.id The bug is somehow related to the use of select_from(). Hope this helps, Ladislav Lenart On 10.5.2013 22:04, Michael Bayer wrote: On May 10, 2013, at 3:03 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. My main concern was that the query creates a cartesian product and I thought the warning might have something to do with it. It haven't. The problem is related to the use of select_from(): q = session.query(cls, PersonalContact).select_from(q_cte_union) q = q.join(cls, cls.id == q_cte_union.c.partner_id) This part renders the following SQL: SELECT * FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id As you can see, the JOIN condition is wrong though I have no idea why. The following works, however: q = session.query(cls) q = q.add_entity(PersonalContact) q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) Is my usage of select_from() bad for some reason? Do you know what's going on? I can provide you a fully runnable test case if you still need it (on Monday). Note also that I use SA 0.7.9. Is this fixed in later versions? I've no idea why you're getting it without more context - if everything is generated from a query(), typically the labels are already applied, with the exception of some operations such as subquery() (maybe cte? not sure, I'd need to try it, hence an already existing example saves me lots of time tinkering around trying to guess what you're doing). Things have been improved in 0.8 regarding same-named columns but that only applies when the labels are already present. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. select_from_bug.py -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello. I've read the patch and the new documentation and i've learned about the existence of select_entity_from(). I was trying to say that the new documentation does not help me to understand the meaning / preferred usage of these constructs (i.e. select_from, select_entity_from and aliased). I simply don't understand when should I use which and why. I have already solved my original problem by replacing select_from() with add_entity() and join(). Take a look at older posts in this thread for more details if you're interested, though the original query is a bit more involved than the supplied regression. Ladislav Lenart On 31.5.2013 12:06, Andrija Zarić wrote: On Friday, May 31, 2013 11:46:46 AM UTC+2, Ladislav Lenart wrote: Glad I could help, but I don't understand what is going on. Neither ticket description nor the patch itself helped me. Sorry. What is select_from() good for when it generates a cartesian query? What MIGHT help ME (a lot infact) is a couple of DOs and DONTs examples in one place for all these three constructs OR A 'highlevel user-friendly step-by-step description' about what each construct does, so that I can see where they differ. Hi, Lenart! In the patch you can find doc for the new method Query.select_entity_from. As I read it, it replaces Query.select_from. Have you tried simply replacing old method with the new one (after applying the patch)? a. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
On Friday, May 31, 2013 11:46:46 AM UTC+2, Ladislav Lenart wrote: Glad I could help, but I don't understand what is going on. Neither ticket description nor the patch itself helped me. Sorry. What is select_from() good for when it generates a cartesian query? What MIGHT help ME (a lot infact) is a couple of DOs and DONTs examples in one place for all these three constructs OR A 'highlevel user-friendly step-by-step description' about what each construct does, so that I can see where they differ. Hi, Lenart! In the patch you can find doc for the new method Query.select_entity_from. As I read it, it replaces Query.select_from. Have you tried simply replacing old method with the new one (after applying the patch)? a. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
On Fri, May 31, 2013 at 12:31 PM, Ladislav Lenart lenart...@volny.czwrote: Hello. I've read the patch and the new documentation and i've learned about the existence of select_entity_from(). I was trying to say that the new documentation does not help me to understand the meaning / preferred usage of these constructs (i.e. select_from, select_entity_from and aliased). I simply don't understand when should I use which and why. I have already solved my original problem by replacing select_from() with add_entity() and join(). Take a look at older posts in this thread for more details if you're interested, though the original query is a bit more involved than the supplied regression. Ladislav Lenart Sorry I rushed with the reply! I haven't carefully read your original post, but concentrated on the patch as I was hoping it was relevant to my issue. More importantly, I was misleading and wrong in trying to 'summarize' the docs, stating there are new and old methods. The examples in the patch say those two cases: select_stmt = select([User]).where(User.id == 7) q = session.query(User).select_entity_from(select_stmt) user_from_select = aliased(User, select_stmt.alias()) should produce similar result, i.e. give you User entity from different mapping then usual; whether select_from() should work as intended - resulting cartesian product in docs example should be 'eliminated' by further join. Cheers, a. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
On Friday, May 31, 2013 6:31:25 AM UTC-4, Ladislav Lenart wrote: Hello. I've read the patch and the new documentation and i've learned about the existence of select_entity_from(). I was trying to say that the new documentation does not help me to understand the meaning / preferred usage of these constructs (i.e. select_from, select_entity_from and aliased). I simply don't understand when should I use which and why. use select_from() as you have been, and you're done. It now does what you expect. A more detailed explanation is here: http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#query-select-from-no-longer-applies-the-clause-to-corresponding-entities I have already solved my original problem by replacing select_from() with add_entity() and join(). Take a look at older posts in this thread for more details if you're interested, though the original query is a bit more involved than the supplied regression. Ladislav Lenart On 31.5.2013 12:06, Andrija Zarić wrote: On Friday, May 31, 2013 11:46:46 AM UTC+2, Ladislav Lenart wrote: Glad I could help, but I don't understand what is going on. Neither ticket description nor the patch itself helped me. Sorry. What is select_from() good for when it generates a cartesian query? What MIGHT help ME (a lot infact) is a couple of DOs and DONTs examples in one place for all these three constructs OR A 'highlevel user-friendly step-by-step description' about what each construct does, so that I can see where they differ. Hi, Lenart! In the patch you can find doc for the new method Query.select_entity_from. As I read it, it replaces Query.select_from. Have you tried simply replacing old method with the new one (after applying the patch)? a. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Thank you for the excellent description. The replace in bold did the trick for me :-) Ladislav Lenart On 31.5.2013 16:31, Michael Bayer wrote: On Friday, May 31, 2013 6:31:25 AM UTC-4, Ladislav Lenart wrote: Hello. I've read the patch and the new documentation and i've learned about the existence of select_entity_from(). I was trying to say that the new documentation does not help me to understand the meaning / preferred usage of these constructs (i.e. select_from, select_entity_from and aliased). I simply don't understand when should I use which and why. use select_from() as you have been, and you're done. It now does what you expect. A more detailed explanation is here: http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#query-select-from-no-longer-applies-the-clause-to-corresponding-entities I have already solved my original problem by replacing select_from() with add_entity() and join(). Take a look at older posts in this thread for more details if you're interested, though the original query is a bit more involved than the supplied regression. Ladislav Lenart On 31.5.2013 12:06, Andrija Zarić wrote: On Friday, May 31, 2013 11:46:46 AM UTC+2, Ladislav Lenart wrote: Glad I could help, but I don't understand what is going on. Neither ticket description nor the patch itself helped me. Sorry. What is select_from() good for when it generates a cartesian query? What MIGHT help ME (a lot infact) is a couple of DOs and DONTs examples in one place for all these three constructs OR A 'highlevel user-friendly step-by-step description' about what each construct does, so that I can see where they differ. Hi, Lenart! In the patch you can find doc for the new method Query.select_entity_from. As I read it, it replaces Query.select_from. Have you tried simply replacing old method with the new one (after applying the patch)? a. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello.
Sorry for the long delay. I finally had enough time to produce a minimal
self-contained regression. The attached file produces the following SQL:
WITH RECURSIVE
q_cte(partner_id, max_depth) AS (
SELECT
partner.id AS partner_id,
1 AS max_depth
FROM partner
WHERE partner.sponsor_id IS NULL
UNION ALL
SELECT
partner_alias.id AS partner_id,
max_depth + 1 AS max_depth
FROM
partner AS partner_alias,
q_cte AS q_cte_alias
WHERE partner_alias.sponsor_id = q_cte_alias.partner_id
)
SELECT
q_cte.partner_id AS q_cte_partner_id, -- suspicious
partner.sponsor_id AS partner_sponsor_id
FROM
q_cte
JOIN partner ON q_cte.partner_id = q_cte.partner_id -- BUG
I would expect this (q_cte part is correct):
WITH RECURSIVE
q_cte(partner_id, max_depth) AS (
...
)
SELECT
partner.id AS partner_id,
partner.sponsor_id AS partner_sponsor_id
FROM
q_cte
JOIN partner ON q_cte.partner_id = partner.id
The bug is somehow related to the use of select_from().
Hope this helps,
Ladislav Lenart
On 10.5.2013 22:04, Michael Bayer wrote:
On May 10, 2013, at 3:03 PM, Ladislav Lenart lenart...@volny.cz wrote:
Hello.
My main concern was that the query creates a cartesian product and I thought
the
warning might have something to do with it. It haven't. The problem is
related
to the use of select_from():
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
This part renders the following SQL:
SELECT *
FROM
q_cte
JOIN partner ON q_cte.partner_id = q_cte.partner_id
As you can see, the JOIN condition is wrong though I have no idea why. The
following works, however:
q = session.query(cls)
q = q.add_entity(PersonalContact)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
Is my usage of select_from() bad for some reason? Do you know what's going
on? I
can provide you a fully runnable test case if you still need it (on Monday).
Note also that I use SA 0.7.9. Is this fixed in later versions?
I've no idea why you're getting it without more context - if everything is
generated from a query(), typically the labels are already applied, with
the exception of some operations such as subquery() (maybe cte? not sure, I'd
need to try it, hence an already existing example saves me lots of time
tinkering around trying to guess what you're doing).
Things have been improved in 0.8 regarding same-named columns but that only
applies when the labels are already present.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
# coding=utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import literal_column
from sqlalchemy.orm.util import aliased
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import relationship
Base = declarative_base()
session = None
class Partner(Base):
__tablename__ = 'partner'
id = Column(Integer(), primary_key=True)
sponsor_id = Column(Integer(), ForeignKey('partner.id', ondelete='SET NULL'))
sponsor = relationship('Partner', primaryjoin='Partner.sponsor_id == Partner.id', remote_side='Partner.id', uselist=False)
def find_subtree(self, max_depth=None):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'), i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id == q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
# q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
def main():
global session
conn_string = 'postgresql+psycopg2://lada:heslo@localhost:5433/zfp_xxx'
engine = create_engine(conn_string, echo=True)
session = sessionmaker(bind=engine, autoflush=False)()
Base.metadata.bind = engine
Base.metadata.create_all()
root =
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
this is very helpful because you are here running into an older feature that I think is not very applicable to modern usage, not to mention not terrifically documented, so I've added http://www.sqlalchemy.org/trac/attachment/ticket/2736/ to address changing the role of select_from() to be more what it says, and to offload a particular magic aspect of it into a new method called select_entity_from(). if you look at the docstrings added to the patch there, you may get a sense for what's been going on. I'll probably commit this soon and those docs will be up on the site. There will be an upgrade path for users in this case. On May 30, 2013, at 1:10 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. Sorry for the long delay. I finally had enough time to produce a minimal self-contained regression. The attached file produces the following SQL: WITH RECURSIVE q_cte(partner_id, max_depth) AS ( SELECT partner.id AS partner_id, 1 AS max_depth FROM partner WHERE partner.sponsor_id IS NULL UNION ALL SELECT partner_alias.id AS partner_id, max_depth + 1 AS max_depth FROM partner AS partner_alias, q_cte AS q_cte_alias WHERE partner_alias.sponsor_id = q_cte_alias.partner_id ) SELECT q_cte.partner_id AS q_cte_partner_id, -- suspicious partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id -- BUG I would expect this (q_cte part is correct): WITH RECURSIVE q_cte(partner_id, max_depth) AS ( ... ) SELECT partner.id AS partner_id, partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = partner.id The bug is somehow related to the use of select_from(). Hope this helps, Ladislav Lenart On 10.5.2013 22:04, Michael Bayer wrote: On May 10, 2013, at 3:03 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. My main concern was that the query creates a cartesian product and I thought the warning might have something to do with it. It haven't. The problem is related to the use of select_from(): q = session.query(cls, PersonalContact).select_from(q_cte_union) q = q.join(cls, cls.id == q_cte_union.c.partner_id) This part renders the following SQL: SELECT * FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id As you can see, the JOIN condition is wrong though I have no idea why. The following works, however: q = session.query(cls) q = q.add_entity(PersonalContact) q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) Is my usage of select_from() bad for some reason? Do you know what's going on? I can provide you a fully runnable test case if you still need it (on Monday). Note also that I use SA 0.7.9. Is this fixed in later versions? I've no idea why you're getting it without more context - if everything is generated from a query(), typically the labels are already applied, with the exception of some operations such as subquery() (maybe cte? not sure, I'd need to try it, hence an already existing example saves me lots of time tinkering around trying to guess what you're doing). Things have been improved in 0.8 regarding same-named columns but that only applies when the labels are already present. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. select_from_bug.py -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags, ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
The Query usually does apply_labels automatically. if you are getting that
warning with your query below, there's too much going on there for me to
identify by sight where that might be happening, I would need actual code which
I can run in order to diagnose.
On May 10, 2013, at 11:10 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider
use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags, ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
Hello.
My main concern was that the query creates a cartesian product and I thought the
warning might have something to do with it. It haven't. The problem is related
to the use of select_from():
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
This part renders the following SQL:
SELECT *
FROM
q_cte
JOIN partner ON q_cte.partner_id = q_cte.partner_id
As you can see, the JOIN condition is wrong though I have no idea why. The
following works, however:
q = session.query(cls)
q = q.add_entity(PersonalContact)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
Is my usage of select_from() bad for some reason? Do you know what's going on? I
can provide you a fully runnable test case if you still need it (on Monday).
Note also that I use SA 0.7.9. Is this fixed in later versions?
Thank you,
Ladislav Lenart
On 10.5.2013 17:17, Michael Bayer wrote:
The Query usually does apply_labels automatically. if you are getting that
warning with your query below, there's too much going on there for me to
identify by sight where that might be happening, I would need actual code
which I can run in order to diagnose.
On May 10, 2013, at 11:10 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello.
I get a warning like this:
usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py:2276: SAWarning:
Column 'id' on table sqlalchemy.sql.expression.Select at 0xa3e19cc; Select
object being replaced by another column with the same key. Consider
use_labels
for select() statements.
use_labels() or apply_labels() should help but both work only on the core
constructs. How can I do this on a Query level?
Also, can this alone cause a cartesian product in a query?
My entire (recursive) query:
def find_subtree(self, max_depth=None, eager=False):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'),
i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id ==
q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth max_depth)
q_cte_union = q_cte.union_all(q_rec)
if eager:
q = session.query(cls, PersonalContact).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
q = q.join(PersonalContact, cls.id == PersonalContact.partner_id)
q = q.options(
subqueryload_all(cls.partner_regions),
joinedload_all(cls.personal_data, PersonalData.address,
innerjoin=True),
subqueryload_all(PersonalContact.contact_tags,
ContactTag.tag),
)
else:
q = session.query(cls)
q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
Thank you,
Ladislav Lenart
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] [Q][0.7.9] How to issue apply_labels() on an ORM query?
On May 10, 2013, at 3:03 PM, Ladislav Lenart lenart...@volny.cz wrote: Hello. My main concern was that the query creates a cartesian product and I thought the warning might have something to do with it. It haven't. The problem is related to the use of select_from(): q = session.query(cls, PersonalContact).select_from(q_cte_union) q = q.join(cls, cls.id == q_cte_union.c.partner_id) This part renders the following SQL: SELECT * FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id As you can see, the JOIN condition is wrong though I have no idea why. The following works, however: q = session.query(cls) q = q.add_entity(PersonalContact) q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) Is my usage of select_from() bad for some reason? Do you know what's going on? I can provide you a fully runnable test case if you still need it (on Monday). Note also that I use SA 0.7.9. Is this fixed in later versions? I've no idea why you're getting it without more context - if everything is generated from a query(), typically the labels are already applied, with the exception of some operations such as subquery() (maybe cte? not sure, I'd need to try it, hence an already existing example saves me lots of time tinkering around trying to guess what you're doing). Things have been improved in 0.8 regarding same-named columns but that only applies when the labels are already present. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
