On Fri, Jun 14, 2019, at 12:30 PM, Jonathan Vanasco wrote: > > > On Friday, June 14, 2019 at 9:33:49 AM UTC-4, Mike Bayer wrote: >> docs for this general idea are at >> https://docs.sqlalchemy.org/en/13/core/custom_types.html#redefining-and-creating-new-operators >> but this is likely a good example to add > > Mike- This is actually a great example to add, because in Postgres (and > likely other databases) the query planner will only consult a function index > if that function is used in the query. > > Sorry to sidetrack this for a moment, but can the TypeDecorator support > multiple comparisons or would you recommend another SqlAlchemy internal? > > The use-case I am concerned with a table that has an index on a 32 char md5 > value and function index that is a 6char substring of that md5 value. Every > time I want to query: > > query.filter(Foo.md5 == md5) > > I have to write something like the following, so the query planner will use > the function index: > > query.filter((Foo.md5 == md5, func.substring(Foo.md5, 0, 6) == md5[:6]) > > With a few billion records in a table, the difference is a few seconds of > processing vs instant results. Right now I'm automating this with a function, > but a TypeDecorator looks to be a much better option.
you can do that, sure, have the __eq__() method return that whole function. Hybrid property can do this too w/ a custom comparator as can column_property() with the same idea. There are Comparator objects all over the place and they are all pluggable. makes for very difficult stack traces. > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/75fff3a2-3e08-44b9-832a-1335443fea9f%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/75fff3a2-3e08-44b9-832a-1335443fea9f%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/daf56ef1-a575-4c27-9df7-622d53df56be%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
