New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
allow for func.xyz() to be treated as a "binary" in terms of ORM relationships #3831
Comments
Changes by Lukas Siemon (@lukas-gitl):
|
1 similar comment
Changes by Lukas Siemon (@lukas-gitl):
|
Michael Bayer (@zzzeek) wrote: you can use a function in a primaryjoin, but the primaryjoin needs to have a comparison operator between the two columns to be compared to each other (or the same col in the self-referential case). The example on the SO issue at least uses a comparison function, but within SQLAlchemy this is still not of the form "operand operator operand". Currently the primaryjoin has to have an expression including that basic geometry. The enhancement here would require that either the func.foo() can be converted into a "binary" expression or it otherwise gives some clue that it has a "left" and "right" side which would then be consumable by the relationship mechanics. This can be made to work right now using a custom construct that provides for "binary" as the visitable name but still renders the SQL function at the compile level using @compiles. This would be hacky though. I can think of a few ways this would be made to work internally, one is to replace all the visit_binary() throughout the ORM with some more generalized "visit_left_and_right_comparison" type of thing that would be also added to the sql package, that would be a very big deal. Another might be to create some weird operator that embeds into BinaryExpression but generates as a function instead - there are some similar behaviors like that right now. In either case, there would need to be some method on a Function that produces this construct, e.g. func.my_whatever(x, y, z).compares(left, right). |
Michael Bayer (@zzzeek) wrote: 1.2 is only tentative milestone, lots of issues will need to be moved out into later releases at some point. |
Changes by Michael Bayer (@zzzeek):
|
Changes by Michael Bayer (@zzzeek):
|
Changes by Michael Bayer (@zzzeek):
|
Changes by Michael Bayer (@zzzeek):
|
Michael Bayer (@zzzeek) wrote: here's a proof of concept. If you can run this through the paces that might make this easier to turn into a patch.
|
Lukas Siemon (@lukas-gitl) wrote: Thank you very much for your quick reply @zzzeek! Real world use case: We have I'm not sure why I've currently implemented this for our use case using Which direction do you think would be most promising to investigate for getting this to work (hacky is fine for now)? |
Lukas Siemon (@lukas-gitl) wrote: And you were quicker than I was here. I'll take a look at the code you posted now! |
Michael Bayer (@zzzeek) wrote: POC #2 start with allowing custom_op to work with
then do it all inside of an operator:
|
Lukas Siemon (@lukas-gitl) wrote: This works. However there is an issue when the column is not actually returned from the object. Let me get a short example together here. |
Lukas Siemon (@lukas-gitl) wrote: In your proof of concept replace the line
this then results in the sql
which is incorrect since a different |
Lukas Siemon (@lukas-gitl) wrote: Alternative solution for reference only: For reference I'm posting a solution to this using column_property. For me this wasn't a great solution, but it might help others. The downside is that returned columns can not be defined dynamically:
|
Michael Bayer (@zzzeek) wrote: @lukas-gitl try the second recipe I have with the custom operator. that is much more localized to just the operator and your second query seems to work. For the first example, there's some more methods that would need to be on FunctionAsBinary that would probably allow it to be interpreted correctly in that case. As I'm looking at the internals, for this feature to be worth it, it would have to not assume the SQL function is a simple fn(a, b). A SQL function might take any number of arguments but still be a comparison between any two arbitrary elements within its argument list. This sort of makes a real "feature" point more towards the custom BinaryExpression / FunctionElement subclass all over again. it would ideally look like:
the returned object would need to trip "visit_binary" within the context of relationship setup and "visit_function" within the context of SQL compilation. A new architectural feature to Visitable might be most appropriate here (new optional attribute compiler_visit_name most likely). |
Lukas Siemon (@lukas-gitl) wrote: We are still running SQLAlchemy 1.0.X, so the patch will probably look slightly different. Looking into how much work it would be to upgrade to 1.1.X (probably a lot). Would a similar patch be possible for 1.0.X? |
Michael Bayer (@zzzeek) wrote: @lukas-gitl this is a new feature so targeted @ 1.2 at the earliest. |
Lukas Siemon (@lukas-gitl) wrote: I was referring to the fact that |
Michael Bayer (@zzzeek) wrote: I'm trying the completely standalone recipe and not seeing the "anon" query you're referring towards. I can get that query if I add say a limit(1), but it doesn't have the extra "venue" token in it. |
Lukas Siemon (@lukas-gitl) wrote: Weird that you don't get anon. I get the same query for both SQLAlchemy versions 1.0.12 and 1.1.2. Here is the full example: Query 1:
Query 2:
Python Code:
|
Michael Bayer (@zzzeek) wrote: oh, "from_self()", that's important :) |
Michael Bayer (@zzzeek) wrote: That condition is not related here, I get that same mis-SQL not using any special features at all:
and there's necessarily a "bug" there, if you want to do joinedload you can't limit the inner query to not include the columns you need:
In theory the load_only() would be "smart" like the joinedload() in some way but this is all totally outside of the func feature here. |
Lukas Siemon (@lukas-gitl) wrote: Ah, that makes so much sense now. I already ran into this before, but didn't connect the dots. The way we handle it currently is by force returning primary and foreign keys on the inner select. I obviously have to extend it to include any columns used in queried relationships. Another positive side effect to this ticket is that I just finished the migration to 1.1.2. And nothing big broke. Very impressed! Thank you again so much @zzzeek! Always extremely happy with your responses here! |
Lukas Siemon (@lukas-gitl) wrote: SQLAlchemy has some smarts here already: The first query adds "other_id" in the inner select event though only the id is required. I'm assuming this is b/c it's marked as foreign key? Venue is added as a select table but then never used. Not critical, but I'd love some insight what is happening here and if that could be generified. Or is it as simple as "foreign keys are always returned"?
Python Code
|
Lukas Siemon (@lukas-gitl) wrote: Could this be extended to force loading of |
Lukas Siemon (@lukas-gitl) wrote: I'm doing this now to force fetching of columns used in relationships and it works perfectly (so far):
|
Lukas Siemon (@lukas-gitl) wrote: I'm trying to use your second recipe now, and it works ok. However I can no longer filter by false to create an empty query. Any idea what is going on? Error:
Code:
|
Michael Bayer (@zzzeek) wrote: local_remote_pairs - Just add a column_property() with what you need? 2nd recipe - 1st recipe not working ? as Ive thought about this more the 1st one is closer to what I think I might do here. as far as how your monkeypatch might be failing the patch I made probably is missing something, running the test suite against my patch directly might show something. |
Lukas Siemon (@lukas-gitl) wrote: What I mean is this: Currently SQLAlchemy is "smart" enough to always load foreign keys and primary keys. I was wondering if we could make it even smarter to always load fields used in loaded relationships (the required information is available in local_remote_pairs). Ah, from your response it sounded like the 2nd recipe was the desired way of doing this. The first recipe seems to be working fine now! Thank you again! |
Michael Bayer (@zzzeek) wrote: only the values in "local" for "local / remote pairs" would make any sense to be loaded locally and for the vast majority of relationships, those are just columns that are already loaded. As I said, for extremely rare / special circumstances where the "local" side is within some kind of function, use a column_property(). |
Anonymous wrote: sqlalchemy.orm import * from sqlalchemy.sql.compiler import SQLCompiler Patch compiler to allow for binary operators: http://tiny.cc/5ff6fydef patched_get_operator_dispatch(self, operator_, qualifier1, qualifier2): noinspection PyProtectedMemberoriginal_get_operator_dispatch = SQLCompiler._get_operator_dispatch class FunctionOp(operators.custom_op): |
Lukas Siemon (@lukas-gitl) wrote: I see, that makes sense. IMPORTANT: For anyone reading this at a later point, consider using patch 1, not patch 2 as there are known issues with patch 2. |
Michael Bayer (@zzzeek) wrote: I'm assuming you have thing working w/ the recipes for now, I'm trying to expedite 1.2 along and this looks like a complicated feature add (but manageable as a recipe). |
Changes by Michael Bayer (@zzzeek):
|
Lukas Siemon (@lukas-gitl) wrote: Sounds good! We are using this successfully in production since my last comment. |
Michael Bayer (@zzzeek) wrote: ok: |
Michael Bayer (@zzzeek) wrote: support functions "as binary comparison" Added new feature :meth: Change-Id: I07018e2065d09775c0406cabdd35fc38cc0da699 → f7076ec |
Changes by Michael Bayer (@zzzeek):
|
In #3831 we added the ability for SQL functions to be used in primaryjoin conditions as the source of comparison, however we didn't add documentation from the main relationship docs so nobody could find it unless they read the migration notes. Since the two use cases that have come up for this are materialized path with string functions, and geometry functions, add the example based on the use case requested in geoalchemy/geoalchemy2#220 (comment) This example hasn't been tested yet and is subject to revision. Change-Id: I410d8ffade3f17cf616fc5056f27d7d32092207b
Migrated issue, originally created by Lukas Siemon (@lukas-gitl)
I'm trying to follow the documentation as described here:
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#non-relational-comparisons-materialized-path
This works as expected, however when I try to use (any)
func
function this fails. I noticed this feature is experimental, but is it possible to fix it for this use case?Error:
ArgumentError: Relationship Venue.parents could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments. Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship.
The issue is also documented here:
http://stackoverflow.com/questions/38006116/how-can-i-create-a-many-to-many-relationship-with-sqlalchemy-using-a-sql-functio
Minimal Test case:
The text was updated successfully, but these errors were encountered: