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;
Does anyone have any idea about fixing this?
--
Gordon Allott ([email protected])
signature.asc
Description: This is a digitally signed message part
-- storm mailing list [email protected] Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
