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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
