well there are two general ways to get various join conditions to produce objects, and I probably need to clarify the difference more, both in my own mind and on the docs.
one is, you just make a mapper against a Table like always, and then you use a particular custom query separately, and feed the result into mapper.instances(). that example is up in the docs now, though not very detailed, at http://www.sqlalchemy.org/docs/adv_datamapping.html#adv_datamapping_resultset . it would look like: foo = Table(...) m = mapper(MyClass, foo) q = text("select foo.bar as foo_bar, foo.bat as foo_bat from foo where something_crazy") results = m.instances(q.execute()) I think this is the way that would satisfy the need for very custom queries. you also can save yourself the column listing by doing something more like: q = select([foo, bar], "where foo.id=12 and bar.lala_id=foo.bar_id and other_stuff") results = m.instances(q.execute()) or skip the whole instances() thing and just do: results = m.select("foo.id=12 and bar.lala_id=foo.bar_id and other_stuff") or use bind params: results = m.select("foo.id=:fooid and bar.lala_id=foo.bar_id and other_stuff", fooid=12) a zillion ways to do it, in my typical anti-pythonic style :) the above styles should all be functional more or less, some tested more than others. the other general way is that you make a mapper that is specifically against a certain query, instead of just a single table. this feature, since its a little more exotic, I havent tested too much so it probably has issues. it means that your object is mapped to a query, and all the columns represented in that query. im not sure if you really need this for what youre doing. but the idea is you can make wahtever query you want: s = select([company,stockprice], and_( company.c.company_id==stockprice.c.company_id, stockprice.c.day=='Tuesday', func.my_custom_function(stockprice.c.foo)==12, "my_textual_criterion(stockprice.foo)=19" ) ) (notice the new 'func' operator i just added last night) so then feed that to a mapper, just giving it a clue what the "primary table" is: m = mapper(MyClass, s, primarytable=company) That idea probably works right now, it will take all the columns from 'company' and 'stockprice' and apply them all to the class as though they were one table. Even more out there, is to take a text query: s = text("select company.foo as company_foo, company.bar as company_bar where exotic_condition=exotic_results") and feed that to the mapper constructor also: m = mapper(MyClass, s, primarytable=company) where above, you can see I had to make sure the columns come out as labels "tablename_columnname", etc. This example probably doesnt work right now since its going to try to query the textual query for columns and primary keys which it doesnt have. let me know how it goes, this will be yet another experiment in seeing how solid the query construction is. - mike Robert Leftwich wrote: > I wrote: >> A question on the empty section 'Custom Join Condition' in the Advanced >> Data Mapping - if I have two tables, say Company and DailyStockPrice >> (which has volume, open, close columns), it is easy to define a relation >> to get *all* the daily prices for a particular company, but what if I >> want to retrieve only the prices at the end of a week or a month. The >> sql is fairly straightforward (involving subqueries), but how can I >> setup a mapping to include this (likely) textual sql as the join >> condition? >> > > On reflection this particular example should probably be a custom query, > but I'm > interested in the capabilities of custom join conditions and would still > appreciate any insight/comments. > > Robert > > > > ------------------------------------------------------- > This SF.net email is sponsored by: Splunk Inc. Do you grep through log > files > for problems? Stop! Download the new AJAX search engine that makes > searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! > http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click > _______________________________________________ > Sqlalchemy-users mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users > ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click _______________________________________________ Sqlalchemy-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
