[storm] Fwd: Re: Re: multiple join's

[Gordon Allott]

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 (gordallott at gmail.com)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
Url : https://lists.ubuntu.com/archives/storm/attachments/20090119/349835c0/attachment.pgp