in my example, I'd like to be able to say
session.query(Product).get('SKUA').saleprice
and get 32.99 as a result if user_store() function returns a 'EAST'
and today is 01-Apr-2009
I probably should mention that it is even slightly more complicated...
if there is no row selected from the promotional_prices table, then
the .saleprice attribute should revert to a product table
"regular_price" column.
All this is accomplished with a column_property, I think, as long as I
can work out how to tell the mapper it should call user_store() to
fill in part of the join criteria.... possible?
Even better, can I work that out as an association property?
On Apr 1, 4:35 pm, Kent <k...@retailarchitects.com> wrote:
> 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.
