I believe (correct me if you know better) that an line subquery is quite a bit less efficient than a join. That was my motivation for avoiding that because there are so many rows and we've already seen performance problems with our queries...
In the end I'd like the object to have a "saleprice" attribute (either column_property or, if possible, association_proxy) that is the single saleprice for the given date and store. When using the association proxy, can I somehow specify the first item in the list? What about a mechanism for using a function as a param, like my user_store() example? Is that accomplished with either the association_proxy or column_property? On Apr 1, 4:19 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Kent wrote: > > Mike, > > Suppose you have a Product object mapped to a product table. Say > > there is a table for promotional_prices as well, which looks something > > like this: > > > PRODUCTID STORE PRICE STARTDATE ENDDATE > > 'SKUA' 'WEST' 30.99 01-Aug-2009 10-Aug-2010 > > 'SKUA' 'EAST' 35.99 01-Aug-2009 10-Aug-2010 > > 'SKUA' 'EAST' 32.99 01-Jun-2010 11-Jun-2011 > > > What I'd like to accomplish is when a Product is queried from the > > database, the lowest price from the current store is included as a > > "column" attribute. > > > I've considered using association proxy and also column_property > > (select([...])). > > > Since a bunch of Products may be fetched at once, I'd prefer a join > > over an inline select, because I believe the database query will > > perform better. > > this is most directly accomplished using column_property against a > correlated subquery. You could also have each product fetch the full > list of related products and pick that with the lowest price to have a > straight inner join, but then you're fetching more rows. Are you > trying to avoid using a correlated subquery to get the lowest related > price directly ? > > > > > I need some advice because the "relation" join includes passing a > > STORE and CURRENT_DATE. > > > Is there a way to set this up such that I can define a function which > > is called when the STORE param is needed. > > > def user_store(): > > return 'WEST' # obviously in real life not hard-coded > > > And then tell the mapper to use the user_store() function when it > > needs to supply the STORE parameter. > > > A further complication is that if there are multiple rows returned, > > I'd like the lowest price. > > > Is something like this possible to achieve with association proxies or > > column_properties or both? > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to sqlalch...@googlegroups.com. > > To unsubscribe from this group, send email to > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group at > >http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
