Thus Spake the Demigod

We ran into some trouble today with finder_sql. Our object graph is fairly complicated, and can be summed up like this:

Job has many
Items, which have many
Processes, which have many
Cycles, which are iterations of many
Tasks.

This leads to us doing some pretty crazy SQL to compress the graph and save on database hits, and led today to an interesting discovery. We had done a has_many on User to return a list of tasks the user was able to perform. The finder_sql we generated looked like this:

SELECT *
FROM tasks t
INNER JOIN cycles c on t.cycle_id == c.id
...
INNER JOIN jobs j ON i.job_id = j.id

Unfortunately, the fist ID returned belonged to Job, so while the attributes for each Task was correct, the ID was actually the Job’s ID.

The fix was to select only the attributes of tasks:

SELECT t.*
FROM tasks t
INNER JOIN cycles c on t.cycle_id == c.id
...
INNER JOIN jobs j ON i.job_id = j.id