Re: [sqlalchemy] [Q] SA 0.7.9 - missing from when using union_all and joinedload_all
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows into
all the tables so that a polymorphic result comes back, so that we can in
fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes
for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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)
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?
Again, this is a very complex query, even more complex than the previous
one, and it boils down to limits in what the ORM can currently handle. It's
probably a bug, though there may be workarounds that allow it to work,
however it's the kind of issue that typically takes me many hours to
diagnose and fix or at least work around, given code that I can run and pdb
in order to debug. This is not something you'd have much luck resolving on
your own unless you wanted to become deeply familiar with SQLAlchemy
internals.
I would recommend again making sure all these issues remain in the latest
0.8 version and attempting to produce a rudimentary series of test classes
which I can run in order to reproduce your results. I can reconstitute
these models looking just at your queries, though sometimes after all that
effort the issue isn't reproduced, due to some quirk in the mappings that's
also required.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
[sqlalchemy] query.whereclause
Hello, I want to feed a Query.whereclause to mysqldump. For this i will need the full where clause with all labels replaced with values, right now I get labels like :project_id_1. How can I do that? Many thanks, Sebastian -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified
that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize
for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows
into all the tables so that a polymorphic result comes back, so that we can
in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes
for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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)
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?
Again, this is a very complex query, even more complex than the previous
one, and it boils down to limits in what the ORM can currently handle.
It's probably a bug, though there may be workarounds that allow it to work,
however it's the kind of issue that typically takes me many hours to
diagnose and fix or at least work around, given code that I can run and pdb
in order to debug. This is not something you'd have much luck resolving on
your own unless you wanted to become
Re: [sqlalchemy] [Q] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
Hello.
I have new insigths / new bug to report.
Even when I reformat the query like this:
q = session.query(Client, PersonalClient, CorporateClient)
q = q.outerjoin(PersonalClient, Client.id == PersonalClient.id)
q = q.outerjoin(CorporateClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
joinedload_all(CorporateClient.data, CorporateData.address),
)
joinedload_all / subqueryload_all still do not work.
The only thing that works is to separate the query into two completely
independet ones:
q = session.query(Client, PersonalClient)
q = q.join(PersonalClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
)
This will also be my solution for the foreseeable future.
Thank you,
Ladislav Lenart
On 28.2.2013 11:29, Ladislav Lenart wrote:
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the
main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified
that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize
for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows
into all the tables so that a polymorphic result comes back, so that we can
in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes
for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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 =
Re: [sqlalchemy] query.whereclause
Thank you, this did the trick. And since only I use it offline the security issues are OK for me. On 02/28/2013 12:27 PM, Simon King wrote: On Thu, Feb 28, 2013 at 10:28 AM, Sebastian Elsner sebast...@risefx.com wrote: Hello, I want to feed a Query.whereclause to mysqldump. For this i will need the full where clause with all labels replaced with values, right now I get labels like :project_id_1. How can I do that? Many thanks, Sebastian One approach is suggested at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings. You will need to pay attention to the warning at the top of that page - by not using bound parameters, you open yourself up to SQL Injection holes, so if you don't trust the user of the system you need to be very careful about escaping. To get the compiled version of a query (the equivalent of d on that wiki page) I think you can use the query.selectable property. Hope that helps, Simon -- check out www.pointcloud9.com Sebastian Elsner - Pipeline Technical Director - RISE t: +49 30 20180300 flor...@risefx.com f: +49 30 61651074 www.risefx.com RISE FX GmbH Schlesische Strasse 28, Aufgang B, 10997 Berlin c/o action concept, An der Hasenkaule 1-7, 50354 Hürth Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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] U1DB support
For this kind of thing we'd not have a SQLAlchemy dialect, there are potentially ways that some parts of the SQLAlchemy ORM can be made to integrate with non-relational backends, specifically unit of work hooks that can take advantage of the ability to track changes to a Python object and flush them out to a backend. But even there, the main advantage would be that an application could integrate across a relational and non-relational backend simultaneously - if you were only talking to a NoSQL db, you'd probably use some other package. If I had unlimited development resources, I might break out that part of the SQLAlchemy ORM that does object instrumentation and change tracking into it's own product, which could then be used in different ways. If for no other reason than to not have any packages with the word SQL in them if we aren't actually using any relational DBs :). For the querying side of things and obviously the schema side, there's no overlap between SQLAlchemy and such a system. On Feb 28, 2013, at 7:03 AM, ock...@raz.or.at wrote: While I realize that U1DB is some sort of a NoSQL DB, I wonder if it'd be possible to create a SQLAlchemy backend for it and if there are any intentions to do so in the near future? Regards Bernhard Reiter -- 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] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
All of that has been fixed in 0.8. With 0.8 both versions work fine.
On Feb 28, 2013, at 5:29 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the
main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified
that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize
for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows
into all the tables so that a polymorphic result comes back, so that we can
in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes
for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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)
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?
Again, this is a very complex query, even more complex than the previous
one, and it boils down to limits in what the ORM can currently handle.
It's probably a bug, though there may be workarounds that allow it to
work, however it's the kind of issue that typically takes me many hours to
diagnose and fix or at least work
Re: [sqlalchemy] [Q] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
Hello again, Michael.
Just a follow-up to my previous post. The following code does not work as I
would like:
q = session.query(Client, PersonalClient)
q = q.join(PersonalClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
)
It generates inner subqueries for joins which I don't want.
The following does not work either:
q = session.query(Client).with_polymorphic([PersonalClient])
It generates outer joins instead of inner joins.
BUT the following DOES the right thing and is by far the simplest and most
elegant:
q = session.query(PersonalClient)
# I can reference Client columns via PersonalClient (e.g.
PersonalClient.inherited_column) in filter and such.
I wasted almost all day to figure this. I have no idea why I haven't tried this
(much) sooner. I guess I was stuck on with_polymorphic() and haven't expected
that PersonalClient would magically join on Client as well.
Thank you,
Ladislav Lenart
On 28.2.2013 11:48, Ladislav Lenart wrote:
Hello.
I have new insigths / new bug to report.
Even when I reformat the query like this:
q = session.query(Client, PersonalClient, CorporateClient)
q = q.outerjoin(PersonalClient, Client.id == PersonalClient.id)
q = q.outerjoin(CorporateClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
joinedload_all(CorporateClient.data, CorporateData.address),
)
joinedload_all / subqueryload_all still do not work.
The only thing that works is to separate the query into two completely
independet ones:
q = session.query(Client, PersonalClient)
q = q.join(PersonalClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
)
This will also be my solution for the foreseeable future.
Thank you,
Ladislav Lenart
On 28.2.2013 11:29, Ladislav Lenart wrote:
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the
main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible
workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified
that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize
for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows
into all the tables so that a polymorphic result comes back, so that we
can in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug
fixes for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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
Re: [sqlalchemy] [Q] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
Cool!
I will switch to it once it is officially released.
Thank you,
Ladislav Lenart
On 28.2.2013 19:04, Michael Bayer wrote:
All of that has been fixed in 0.8. With 0.8 both versions work fine.
On Feb 28, 2013, at 5:29 AM, Ladislav Lenart lenart...@volny.cz wrote:
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the
main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible
workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just verified
that
I am using SA 0.7.8 at the moment (version from debian distro). I apologize
for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query as
given, and it produces the identical SQL. I've inserted a bunch of rows
into all the tables so that a polymorphic result comes back, so that we
can in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of
0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug
fixes for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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)
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?
Again, this is a very complex query, even more complex than the previous
one, and it boils down to limits in what the ORM can currently handle.
It's probably a bug, though there may be workarounds that allow it to
Re: [sqlalchemy] [Q] SA 0.7.10 - issues with union, with_polymorphic, joinedload / subqueryload
a join() or a joinedload to a polymorphic selectable always wraps the right
side in a subquery as the ORM doesn't directly support rendering of an
expression like a JOIN (b JOIN c ON b.id=c.id) ON a.id=b.id. The expression
language does, but within the ORM it uses the subquery approach when its asked
to join automatically 's that the query works on all platforms. Supporting
having it nest the JOIN directly is http://www.sqlalchemy.org/trac/ticket/2587 .
On Feb 28, 2013, at 1:08 PM, Ladislav Lenart lenart...@volny.cz wrote:
Hello again, Michael.
Just a follow-up to my previous post. The following code does not work as I
would like:
q = session.query(Client, PersonalClient)
q = q.join(PersonalClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
)
It generates inner subqueries for joins which I don't want.
The following does not work either:
q = session.query(Client).with_polymorphic([PersonalClient])
It generates outer joins instead of inner joins.
BUT the following DOES the right thing and is by far the simplest and most
elegant:
q = session.query(PersonalClient)
# I can reference Client columns via PersonalClient (e.g.
PersonalClient.inherited_column) in filter and such.
I wasted almost all day to figure this. I have no idea why I haven't tried
this
(much) sooner. I guess I was stuck on with_polymorphic() and haven't expected
that PersonalClient would magically join on Client as well.
Thank you,
Ladislav Lenart
On 28.2.2013 11:48, Ladislav Lenart wrote:
Hello.
I have new insigths / new bug to report.
Even when I reformat the query like this:
q = session.query(Client, PersonalClient, CorporateClient)
q = q.outerjoin(PersonalClient, Client.id == PersonalClient.id)
q = q.outerjoin(CorporateClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
joinedload_all(CorporateClient.data, CorporateData.address),
)
joinedload_all / subqueryload_all still do not work.
The only thing that works is to separate the query into two completely
independet ones:
q = session.query(Client, PersonalClient)
q = q.join(PersonalClient, Client.id == PersonalClient.id)
if eager:
q = q.options(
joinedload_all(PersonalClient.data, PersonalData.address),
)
This will also be my solution for the foreseeable future.
Thank you,
Ladislav Lenart
On 28.2.2013 11:29, Ladislav Lenart wrote:
Hello again.
I have successfully installed SA 0.7.10 and the query as-is works. However
jonedload_all / subqueryload_all options on the query do NOT work. I have
slightly extended your cte_example which now demonstrates the issues.
joinedload_all does not crash but there is undesired sql activity after the
main
query which renders the joinedload useless. subqueryload_all crashes.
I would be glad for any words of advice or idea(s) for possible
workaround(s),
Ladislav Lenart
On 28.2.2013 10:26, Ladislav Lenart wrote:
Thank you very much for your valuable time, Michael!
Your example code seems correct. The only differences I found are:
* Missing FK on ImportedPartnerShare.partner_id:
class ImportedPartnerShare(Base):
deal_id = Column(Integer, ForeignKey('deal.id'))
partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is
missing in yout example script
* All FKs in the example should have nullable=False and ondelete='CASCADE'.
But I suppose none of this makes any difference. As you wrote and
confirmed,
this issue (and many others) was resolved in SA 0.7.9. I have just
verified that
I am using SA 0.7.8 at the moment (version from debian distro). I
apologize for
the err subject. I did not check it when I wrote that line, I just assumed.
Thank you again,
Ladislav Lenart
On 28.2.2013 04:12, Michael Bayer wrote:
OK, I've reconstructed mappings which correspond directly to your Query
as given, and it produces the identical SQL. I've inserted a bunch of
rows into all the tables so that a polymorphic result comes back, so that
we can in fact verify that the ORM reads the client_id column correctly.
Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as
of 0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug
fixes for the CTE feature as it had only been introduced in 0.7.6.
Script is attached.
On Feb 27, 2013, at 4:11 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Feb 27, 2013, at 3:12 PM, Ladislav Lenart lenart...@volny.cz wrote:
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
