Rails query question

gistfile1.txt

Reasonably complex query using ActiveRecord

I have the following tables

Agents
------
id name

Clients
-------
id name

ClientAgents
------------
client_id agent_id

Activities
----------------
id agent_id client_id target_type target_id created_at type

Activities uses single table inheritance. The target_type determines the class of object that target_id refers to. So we could have the following:

Activities
----------------
id agent_id client_id target_type target_id created_at type
1  1        1         client      2         3/3/2013
2  1        2         agent       2         2/2/2013

Agents
1   Fred

Client
1   Jane
2   Lucy

Now Lucy's activities are [1,2] because Lucy is the client in activity 2 and the target in activity 1.

Now Lucy is a client of Freds (in ClientAgents we have [2, 1])

1. How do we get Lucy's most recent activity
2. How do we get a list of Fred's clients with their most recent activity included, avoiding n+1 queries - assume Fred has many clients like Lucy