I think I get the idea. That's why the properties with hybrid_property.expression defined are able to work as SQL SELECT fields in the cases where a tuple is returned rather than an ORM model... because you don't have to have a place in _dict__ to put it, it just goes into the tuple.
My main goal was figuring out how to easily limit which columns are returned, without re-specifying joins that are already defined in the relationships on the Model, so that probably muddied my question. I think the answer to that is that either I query the whole ORM object (and get the joins for free, as defined in the Model) or if I must limit the columns returned, then I just have to re-specify the joins in each query. Thank you for the feedback and all the support in general. On Monday, June 3, 2019 at 6:24:29 PM UTC-4, Mike Bayer wrote: > > I think the thing you need to understand about the "hybrid" is that it > isn't "loaded" with the object, that is, it has no place in __dict__ to be > stored. The function that you create is invoked every time you access it > on your object or class. If you have an object instance of Task, as you've > observed, the in-python version of it will run every time when you say > some_task.my_hybrid. That's why it's called a "hybrid". > > So technically, it doesn't make sense to say > quey(Task).load_only(Task.some_hybrid), becuase there is no place on Task > to put that value. > > However, it *does* make sense that you might want to load_only the columns > on Task that your hybrid uses in order to create its value. If your hybrid > was like, "return self.x + self.y" you would want to load_only(Task.x, > Task.y). > > So from *there*, there could be a way that these column attributes could > be extracted from your hybrid automatically, so that if you said > load_only(Task.some_hybrid), it would actually mean load_only(Task.x > Task.y). I could even come up with a recipe to do that in the meantime. > But I'm not sure this would make a good feature or not because it adds more > layers of magic to a concept that is already proving to be confusing. > > let me know if that makes sense. > > > On Mon, Jun 3, 2019, at 5:06 PM, Gmoney wrote: > > I thought I figured out another clever way, but it's not working working > as I thought it might. Thought maybe using ".from_self" I could do the > base full automatic ORM Model query to get everything, then do the > .from_self to wrap it with an outer query and only return the columns I > need. > > q = (db.session.query(Task).from_self(*columns_i_want)) > > Unfortunately though, even though query(Task) alone can figure out the > relationships/joins, once it's in this statement with the from_self > version, you don't get them anymore. > > I was so excited - I thought that was definitely going to work. Any > chance I'm just missing a step that could make that method work? I see the > '.add_entity' option but that didn't seem to help and kind of defeats the > purpose of my simplicity goal anyway. > > On Monday, June 3, 2019 at 3:57:18 PM UTC-4, Gmoney wrote: > > I think it's starting to make sense. I did misunderstand exactly when the > .expression SQL was used - I had thought even queries for ORM models would > use it, but I see it does work as you describe. > > My goal is to return a subset of fields and make that query building > operation be dynamic and "simple" for downstream services. My trouble > seems to be that as I move away from the baseline full "query(MyORMClass)", > the relationship configured joins no longer automatically come along, so I > have to re-specify them in the query. > > Thanks for the response, I will consider the column_property alternative - > it might be better for our situation. > > As far as limiting fields returned then though - I think these are my > options, if I understand them right: > > query(ORM_Class) > -Returns list of ORM Model instances > -The only option that auto-includes all joins defined in ORM Model > relationships. > -Does not calculate hybrid_property in SQL > -Referencing hybrid_property on the returned ORM Model evaluates > hybrid_property in python. > > load_only(ORM_Class.column_attr,... etc) > -Returns list of ORM Models (sparse, lazily populated) > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Can't ask for hybrid_property in query > -Referencing hybrid_property on the returned ORM Model still evaluates > hybrid_property in python. > > query(ORM_Class.column_attr, ORM_Class.hybrid_property) > -Returns Tuple > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Uses the .expression form of the hybrid_property to SELECT/calculate it > in SQL > > with_entities(ORM_Class.column_attr,... etc) > -Returns Tuple > -Will *NOT* add necessary joins from relationship definitions in Model. > Must join manually in query. > -Uses the .expression form of the hybrid_property to SELECT/calculate it > in SQL > > > > > On Monday, June 3, 2019 at 2:21:41 PM UTC-4, Mike Bayer wrote: > > well hybrids don't apply to a load_only operation because they are not > included in the query that emits when the ORM SELECTs for an object. > that is, @hybrid_property.expression gives you a SQL expression, but that's > never included when you say session.query(Task). It's only if you said, > session.query(Task).filter(Task.hybrid_test == 'x'). > > There's a construct called column_property() that *is* included in the > columns when you say query(Task). This is actually the kind of property > that's used for all the regular columns but it also accomodates arbitrary > SQL expressions. to that extent there's some overlap between > column_property and hybrids which is why they are both listed at > https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html. > <https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html> > > let me know if that makes sense. > > On Mon, Jun 3, 2019, at 2:17 PM, Gmoney wrote: > > I should at least clarify that I have @hybrid_test.expression in place for > that property and it works when I just do the base 'get me everything' > query. > > > On Monday, June 3, 2019 at 2:13:17 PM UTC-4, Gmoney wrote: > > I'm trying to use load_only and was able to get a really basic example to > work. My problem is that once I try to load one of my hybrid properties, > it fails. > > This code works for any number of basic Column properties but fails once I > add the hybrid property: > > columns = (Task.msg_id, Task.hybrid_test) > > q = (db.session.query(Task).options(load_only(*columns).lazyload('*'))) > > AttributeError: Neither 'hybrid_property' object nor 'ExprComparator' > object associated with Task.hybrid_test has an attribute 'property' > > > Just wondering if I should be able to get this to work if I keep digging > or post a more thorough code snippet, or is it not possible using this > approach? > > > -- > 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 sqlal...@googlegroups.com. > To post to this group, send email to sqlal...@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/2661a10c-4741-4e5b-a1e7-2db578c5712b%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/2661a10c-4741-4e5b-a1e7-2db578c5712b%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 sqlal...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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/02f8addf-a7ef-4fad-b972-b582ec902f30%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/02f8addf-a7ef-4fad-b972-b582ec902f30%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/704ccbf4-b6cf-4d13-babb-22ef4dcb4b86%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.