How to show foreign key relationship data in jqgrid?

I know how to access foreign key attributes in a scaffold index view. I can simply refer to the attributes using dot notation 开发者_StackOverflowsuch as property.que.name. Given the following models:

class Priority < ActiveRecord::Base
  belongs_to :que
  ...
end

class Que < ActiveRecord::Base
  has_many :priorities
  ...
end

In the index view, I can do something like this to get the name value:

<td><%=h priority.que ? priority.que.name : "" %></td>

How do I do this in the jqgrid?

I tried this but the jqgrid comes back empty:

Priorities Controller:

@priorities = Priority.find(:all, :order => "position", :conditions => "multitenant_team_id = " + current_user.team.id.to_s ) do
  if params[:_search] == "true"
    id =~ "%#{params[:id]}%" if params[:id].present?
    issue_id =~ "%#{params[:issue_id]}%" if params[:issue_id].present?
    que =~ "%#{params[:que]}%" if params[:que].present?
    customer =~ "%#{params[:customer]}%" if params[:customer].present?
    title =~ "%#{params[:title]}%" if params[:title].present?
    reporting_source =~ "%#{params[:reporting_source]}%" if params[:reporting_source].present?
    priority =~ "%#{params[:priority]}%" if params[:priority].present?
    product =~ "%#{params[:product]}%" if params[:product].present?
    current_owner =~ "%#{params[:current_owner]}%" if params[:current_owner].present?
  end
  paginate :page => params[:page], :per_page => params[:rows]
  order_by "#{params[:sidx]} #{params[:sord]}"

end

if request.xhr?
end

respond_to do |format|
  format.html # index.html.erb
  format.json { render :json => @priorities.to_jqgrid_json(
    [:id, :issue_id, :que.name, :customer, :title, :reporting_source, 
     :priority, :product, :current_owner],
     params[:page], params[:rows], @priorities.total_entries)}
  format.xml  { render :xml => @priorities }
 end

end

Index View:

<%= jqgrid("Priorities", "priorities", "/priorities",
[
  {:field => "id", :label => "ID", :width => 35, :resizable => false},
  {:field => "issue_id", :label => "Issue Id"},
  {:field => "que", :label => "Queue"},
  {:field => "customer", :label => "Customer"},
  {:field => "title", :label => "Title"},
  {:field => "reporting_source", :label => "Reporting Source"},
  {:field => "priority", :label => "Priority"},
  {:field => "product", :label => "Product"},
  {:field => "current_owner", :label => "Current Owner"}
],
  { :rows_per_page => 12, :height => 450 }
)%>

If I specify que instead of que.name, I get the data back in the grid but the Queue field shows a "#" symbol so I suspect the .to_jqgrid_json call doesn't like my syntax.

Has anyone tried this before? I hope so.

I fixed my problem. I ended up changing my find to a find_by_sql so I could do a left outer join on the ques table. I think there were a couple of issues. I think the *to_jqgrid_json* had problems with null foreign key values and I couldn't figure out how to get at the Que.name any other way. I'm using SQLServer so I had to use isnull(ques.name, '') to convert the null to empty space.

So I replaced my find as follows:

@priorities = Priority.find_by_sql ["select priorities.id id, issue_id, isnull(ques.name,' ') queue_name, customer, title, reporting_source, priority, product, current_owner from priorities left outer join ques on priorities.que_id = ques.id where priorities.multitenant_team_id = ? order by issue_id", current_user.team.id.to_s]

This introduced another problem in that find_by_sql returns an array which breaks the @priorities.total_entries call. So I had to replace it with array.count.

format.json { render :json => @priorities.to_jqgrid_json(
    [:id, :issue_id, :queue_name, :customer, :title, :reporting_source, :priority, :product, :current_owner], 
     params[:page], params[:rows], @priorities.count)}

My grid looks great!

Edit

My grid LOOKS great but it doesn't paginate or sort. Back to the drawing board. :(

Okay, I think I fixed it for real this time.

@priorities = Priority.find(:all,
                        :select => "priorities.id, priorities.issue_id, 
                                        priorities.customer, priorities.title, 
                                        priorities.reporting_source, priorities.priority,
                                        priorities.product, priorities.current_owner,
                                        priorities.position,
                                        isnull(ques.name,' ') queue_name",
                            :joins => "LEFT OUTER JOIN ques ON ques.id = priorities.que_id",
                            :order => "priorities.position", 
                            :conditions => "priorities.multitenant_team_id = " + current_user.team.id.to_s ) do

I had know idea I could specify joins like this. This keeps the resultset in a format the 2dc_jqgrid plugin likes. Sorting, pagination and searching all work now. Now my grid looks good and actually works.