Hello.
Thank you for your prompt answer. I will try to create a working example that
demonstrates the issue. Though it will take me a couple of days, maybe weeks (my
regular work followed by a vacation).
I have another problem. I rephrased the SQL, because postgres's planner had
issues with EXISTS and thought it is a great idea to perform full scans of
several huge tables in order to return several hundreds result rows. Enter
CTEs...
Short-short intro:
* Client is the joined table inheritance root.
* PersonalClient and CorporateClient are its subclasses.
* Partner is a salesman.
* Deal is a contract signed between salesman(s) and client(s).
* ImportedClientShare and ClientShare are M:N relationships between clients and
deals.
* ImportedPartnerShare and PartnerShare are M:N relationships between partners
and deals.
* We import deals from an external DB. Those are called imported. Imported deal
has external_id and Imported*Share apply.
* However, a user of our system (a partner) can create a new deal locally. Such
a deal does not have an external id (yet) and local *Share apply to it.
The following code should return all clients of a given partner via
ImportedClientShare or via ClientShare:
q1 = session.query(ImportedClientShare.client_id.label('client_id'))
q1 = q1.join(ImportedPartnerShare, ImportedClientShare.deal_id ==
ImportedPartnerShare.deal_id)
q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id)
q1 = q1.filter(
ImportedPartnerShare.partner_id == partner_id, # input argument
Deal.external_id != None,
)
q2 = session.query(ClientShare.client_id.label('client_id'))
q2 = q2.join(PartnerShare, ClientShare.deal_id == PartnerShare.deal_id)
q2 = q2.join(Deal, ClientShare.deal_id == Deal.id)
q2 = q2.filter(
PartnerShare.partner_id == partner_id, # input argument
Deal.external_id == None,
)
client_ids = q1.union(q2).cte('client_ids')
q = session.query(Client).with_polymorphic([PersonalClient,
CorporateClient])
q = q.join(client_ids, Client.id == client_ids.c.client_id)
It produces the following SQL:
WITH client_ids AS (
SELECT anon_1.client_id AS client_id
FROM (
SELECT imported_client_share.client_id AS client_id
FROM
imported_client_share
JOIN imported_partner_share ON
imported_client_share.deal_id =
imported_partner_share.deal_id
JOIN deal ON imported_client_share.deal_id = deal.id
WHERE
imported_partner_share.partner_id = 610556
AND deal.external_id IS NOT NULL
UNION
SELECT client_share.client_id AS client_id
FROM
client_share
JOIN partner_share ON client_share.deal_id =
partner_share.deal_id
JOIN deal ON client_share.deal_id = deal.id
WHERE
partner_share.partner_id = 610556
AND deal.external_id IS NULL
) AS anon_1
)
SELECT
client.type AS client_type,
client.id AS client_id,
client.external_id AS client_external_id,
personal_client.id AS personal_client_id,
personal_client.data_id AS personal_client_data_id,
corporate_client.id AS corporate_client_id,
corporate_client.data_id AS corporate_client_data_id
FROM
client
LEFT OUTER JOIN personal_client ON client.id = personal_client.id
LEFT OUTER JOIN corporate_client ON client.id = corporate_client.id
JOIN client_ids ON client.id = client_ids.client_id
The SQL IS correct, returns expected results when executed in pgadmin3 for
example, but SA's ORM layer crashes with the error:
File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2348, in
instances
rows = [process[0](row, None) for row in fetch]
File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 2011,
in _instance
return _instance(row, result)
File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 2024,
in _instance
tuple([row[column] for column in pk_cols])
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2835,
in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: "Could not locate column in row for column 'client.id'"
I also tried it without with_polymorphic() and the result is the same.
Do you have any idea what is going on / what am I doing wrong and how I can fix
this?
Thanks,
Ladislav Lenart
On 27.2.2013 19:05, Michael Bayer wrote:
>
> On Feb 27, 2013, at 12:21 PM, Ladislav Lenart <lenart...@volny.cz> wrote:
>
>> Hello.
>>
>> Suppose I have the following mapped classes, A and B, that have two distinct
>> M:N
>> relationships, AB1 and AB2. If A.x is null, only relations in AB1 apply. If
>> it
>> is not null, only relations in AB2 apply. A also has 1:N relationship to C
>> (one
>> A can have more Cs). Finally, A is infact a joined table inheritance
>> superclass
>> with two subclasses, A1 and A2. I want to select all As for a given B via
>> AB1 or
>> AB2. I also want to prefetch A.cs of the results using joinedload. I use the
>> code like this:
>>
>> q1 = session.query(A).with_polymorphic([A1, A2])
>> q1 = q1.filter(exists().where(and_(
>> A.x == None,
>> AB1.a_id == A.id,
>> AB1.b_id == b_id, # input argument
>> ))
>>
>> q2 = session.query(A).with_polymorphic([A1, A2])
>> q2 = q2.filter(exists().where(and_(
>> A.x != None,
>> AB2.a_id == A.id,
>> AB2.b_id == b_id, # input argument,
>> ))
>>
>> q = q1.union_all(q2)
>> q = q.options(
>> joinedload(A.cs),
>> )
>>
>> return q
>>
>> This creates the following SQL:
>>
>> SELECT ....
>> FROM (
>> SELECT... -- via AB1
>> UNION ALL
>> SELECT ... -- via AB2
>> ) anon_1
>> LEFT OUTER JOIN c ON c.a_id = a.id -- error line
>>
>> This fails with a missing from clause error for table a. The attribute a.id
>> is
>> actually anon_1.a_id.
>
> I'm kind of amazed it even managed to render that without bombing out a lot
> sooner. I'm not sure joinedload() is sophisticated enough right now to
> figure out the two queries inside of a UNION and all of that, one thing to
> try would be if your code has different/better/worse behavior on 0.8 (I'd be
> curious), but short of that the workaround is to join explicitly and then use
> contains_eager(), like:
>
> q = q.outerjoin(A.cs).options(contains_eager(A.cs))
>
> that's assuming plain old outerjoin(A.cs) works here (which it also might
> have similar issues). If that's not working then you really might have to
> get more explicit, in a case this complicated that almost might not be worth
> it.
>
> Another option is to use subqueryload(A.cs) instead. subqueryload() tends to
> produce a wider range of queries and also tends to put less strain on the
> database.
>
> Ultimately its a SQLAlchemy bug but it's not clear how hard it would be to
> fix. If all the above options fail I might try to see if there's a repair
> path at least within 0.8. A link to working example mappings would be of
> great help here.
>
>
>
>
>
>
--
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.
