2

In my application, I have to models: Workflow and Step; steps belongs_to workflow and a workflow has_many steps. Steps have an index and a boolean status ('completed'). I want to retrieve workflows whose step 1 is completed and step 2 is not, i.e. something like this in SQL:

SELECT * FROM workflows w
INNER JOIN steps s1 ON s1.workflow_id = w.id
INNER JOIN steps s2 ON s2.workflow_id = w.id
WHERE s1.index = 1 AND s1.completed = 1
AND s2.index = 2 AND s2.completed = 0

I've tried to express this query with Squeel, but it seems it does not allow multiple joins on the same association: I cannot find a way to name the joins, and when I type something like this: Workflow.joins{steps}.joins{steps}, the generated SQL is simply:

SELECT `workflows`.* FROM `workflows` 
INNER JOIN `workflow_steps` ON `workflow_steps`.`workflow_id` = `workflows`.`id`

Any idea how I can achieve that?

Dom
  • 31
  • 2

1 Answers1

1

I don't know if you gonna like it, but it's possible via self-reference:

Workflow.joins{steps.workfolw.steps}.
  where{(steps.index == 1) & (steps.completed == true)} &
        (steps.workfolw.steps.index == 2) & (steps.workfolw.steps.completed == false)}.
  uniq
jdoe
  • 15,665
  • 2
  • 46
  • 48
  • Interesting. I'll give it a try (my system being actually a bit more complex than what I described). – Dom Jun 13 '12 at 08:11