I'm working with a model structure that is not exactly ideal (i.e. I didn't create it) and I'm having a hard time writing an AR query to get me the data I want...
I've got the following models:
class Project < ActiveRecord::Base
has_many :project_states
has_many :states, :through => :project_states
end
class State < ActiveRecord::Base
has_many :forms
has_many :project_states
has_many :projects, :through => :project_states
end
class ProjectState < ActiveRecord::Base
belongs_to :state
belongs_to :project
end
class Form < ActiveRecord::Base
belongs_to :state
end
Simple enough. Ish. What I'm trying to do is get a a list of all forms for all projects. Essentially let's say I have two forms in the database, and 2 projects each of which has both forms. I'd like get the results:
-----------------------------------------------
project.name | state.name | form.name
Project 1 Ohio Form 1
Project 1 California Form 2
Project 2 Ohio Form 1
Project 2 California Form 2
I can do this quite easily with the following sql query:
SELECT
states.name, forms.name, projects.name
FROM
forms
LEFT JOIN
states ON forms.state_id = states.id
INNER JOIN
project_states ON project_states.state_id = forms.state_id
LEFT JOIN
projects O开发者_StackOverflowN projects.id = project_states.project_id;
But I'm not sure how to do this through AR. I could do it as a straight SQL query, but there are methods on the models I need for display. I could do it by simply fetching projects, looping over them and then looping over the forms for each one, but the display needs to be a sortable table and that would make it impossible to sort on any form attributes.
Is there an easyish way to implement this I'm missing? For example if I do something like:
Form.joins(:states=> [:projects])
This returns one result for each form, not one result for each form for each project. If I had a direct relationship between Projects and Forms, it would be easy enough as I could just fetch off the join table. But I don't have that...
Follow up, I couldn't really find a way to do this in rails without resorting to find_by_sql, so I ended up creating a view and a new model for the view to get the data I wanted.
精彩评论