[Q][0.7.9] How to issue apply_labels() on an ORM query?

[Ladislav Lenart]

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

[Michael Bayer]

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.

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@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.
>
>

[Ladislav Lenart]

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

[Michael Bayer]

On May 10, 2013, at 3:03 PM, Ladislav Lenart <lena...@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.

[Ladislav Lenart]

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

[Michael Bayer]

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.

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@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>

[Ladislav Lenart]

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

[Ladislav Lenart]

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.

[Andrija Zarić]

On Fri, May 31, 2013 at 12:31 PM, Ladislav Lenart <lena...@volny.cz> 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.

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.

[Michael Bayer]

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


 

[Ladislav Lenart]

Thank you for the excellent description. The replace in bold did the trick for
me :-)

Ladislav Lenart