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

[James Henstridge]

On Tue, Jan 20, 2009 at 7:18 AM, Gordon Allott <gordallott at gmail.com> 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.