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

[Justin Warren]

On Wed, 2009-01-14 at 23:58 +0000, Gordon Allott wrote:
> On Wed, 2009-01-14 at 23:50 +0000, alexrichardson at gmail.com wrote:
> 
> > Your second example uses an 'And' which means you are trying to match
> > an employee row where the name is 'Ben' AND ' Steeve' AND 'John'. An
> > employee's ame can not match all three values, so it always fails and
> > you get back 0 rows.
> > 
> > However, if you change to an 'Or' it should work. Then you are trying
> > to match an employee row where the name is 'Ben' OR ' Steeve' OR
> > 'John'.
> > 
> > So I would expect something along the following to work:
> > 
> > > store.using(*origin).find(Company, Or(*employees))
> 
> An or works, I would expect it to, but what I need is an and (or
> something like it), an or would return companies where any of the
> employee's listed existed and I need just the companies where all of
> the employee's listed exist, I think that's what is confusing the issue
> slightly.

You want to find all the companies that have *all 3* employees, right?

This is an intersection, like this:

SELECT company_id FROM Employee WHERE employee_name LIKE 'Ben%'
  INTERSECT
SELECT company_id FROM Employee WHERE employee_name LIKE 'Steve%'
  INTERSECT
SELECT company_id FROM Employee WHERE employee_name LIKE 'John%'
;

So, something like this in Storm should work:

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.

-- 
Justin Warren <daedalus at eigenmagic.com>
eigenmagic.com