On Tue, Jan 20, 2009 at 7:18 AM, Gordon Allott <[email protected]> wrote: > On Thu, 2009-01-15 at 13:07 +1100, Justin Warren wrote: > >> result1 = store.find(Employee, Employee.name.like(u'Ben%')) >> result2 = store.find(Employee, Employee.name.like(u'Steve%')) >> result3 = store.find(Employee, Employee.name.like(u'John%')) >> >> company_ids = result1.intersect(result2).intersect(result3) >> >> companies = store.find(Company, Company.id.is_in(company_ids)) >> >> There is probably a more efficient way of doing this. >> > > Hi again, > > It seems that whilst result1.intersection(result2) would work as a way of > generating company ID's, adding more intersections seems to break the SQL, > Namely this is the sql output for two intersections using the > result1.intersection(result2) method (forgive the strange column names, > obviously my application isn't using the tutorial company example): > > """ > SELECT * FROM ( > SELECT dream_log.datetime, dream_log.effect, dream_log.id, > dream_log.intepretation, dream_log.lastincident, dream_log.location, > dream_log.log, dream_log.title, dream_log.type, dream_log.user_id > FROM dream_log JOIN dream_theme ON dream_theme.log_id = dream_log.id > WHERE dream_log.user_id = ? AND dream_theme.name = ?) > > INTERSECT > SELECT * FROM (SELECT dream_log.datetime, dream_log.effect, > dream_log.id, dream_log.intepretation, dream_log.lastincident, > dream_log.location, dream_log.log, dream_log.title, dream_log.type, > dream_log.user_id FROM dream_log JOIN dream_theme ON dream_theme.log_id > = dream_log.id WHERE dream_log.user_id = ? AND dream_theme.name = ?) > """ > I changed the formatting to make it readable, This works perfectly but > if you do something like the following: > result1.intersection(result2).intersection(result3) > > then the following broken SQL is generated by storm: > """ > (SELECT * FROM ( > > SELECT dream_log.datetime, dream_log.effect, dream_log.id, > dream_log.intepretation, dream_log.lastincident, dream_log.location, > dream_log.log, dream_log.title, dream_log.type, dream_log.user_id FROM > dream_log JOIN dream_theme ON dream_theme.log_id = dream_log.id WHERE > dream_log.user_id = ? AND dream_theme.name = ?) > > INTERSECT SELECT * FROM (SELECT dream_log.datetime, dream_log.effect, > dream_log.id, dream_log.intepretation, dream_log.lastincident, > dream_log.location, dream_log.log, dream_log.title, dream_log.type, > dream_log.user_id FROM dream_log JOIN dream_theme ON dream_theme.log_id > = dream_log.id WHERE dream_log.user_id = ? AND dream_theme.name = ?)) > > INTERSECT SELECT * FROM (SELECT dream_log.datetime, dream_log.effect, > dream_log.id, dream_log.intepretation, dream_log.lastincident, > dream_log.location, dream_log.log, dream_log.title, dream_log.type, > dream_log.user_id FROM dream_log JOIN dream_theme ON dream_theme.log_id > = dream_log.id WHERE dream_log.user_id = ? AND dream_theme.name = ?) > """ > Note the '(' at the start;
Note that the parentheses match up in this case: there is a double close-paren after the second query. There are a few things we could do to help fix this: 1. as set operations like INTERSECT are left associative, modify the compiler to not use the extra parentheses. 2. change the ResultSet code to convert chained operations like this into a single Intersect(select1, select2, select3) which would also handle things. James. -- storm mailing list [email protected] Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
