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.
