#125 new

"order by" based on nested model attribute creates an incorrect SQL statement

Reported by Masoud | April 5th, 2010 @ 12:36 PM

First: Great plug-in! Many thanks.

Then: Consider the following MVC:

class SuppClaim < ActiveRecord::Base

  has_many :reviews, :class_name => "Review", :foreign_key => :Supp_claim_id, :dependent => :destroy 
      named_scope :from_date, lambda { |c| {:joins => ["LEFT OUTER JOIN reviews ON (supp_claims.id =  reviews.Supp_claim_id)"], :conditions => ['reviews.FirstReviewDate >= ?', c ] }} 
      named_scope :to_date, lambda { |c| {:joins => ["LEFT OUTER JOIN reviews ON (supp_claims.id = reviews.Supp_claim_id)"], :conditions => ['reviews.FirstReviewDate <= ?', c ] }}



class Review < ActiveRecord::Base

  belongs_to :supp_claim, :class_name => 'SuppClaim', :foreign_key => :Supp_claim_id


    class SuppClaimsController < ApplicationController

      def index
        @search = SuppClaim.search(params[:search])
        @supp_claims = @search.all.paginate(:page => (params[:page]), :per_page => APP_CONFIG['items_per_page'])



<% form_for @search do |f| %>
    <%= f.label "from", "From: "%>
    <%= f.text_field :from_date %>
    <%= f.label "to", "To: "%>
    <%= f.text_field :to_date %>
    <%= f.submit "Submit" %>
<% end %>

<%= will_paginate @supp_claims%>
      <th> some other columns </th?
      <th><%= order @search, :by => :reviews_FirstReviewDate, :as => "Review Date" %></th>
    <%= render(:partial => "supp_claims/supp_claims_row" , :collection => @supp_claims) %>
  <%= will_paginate @supp_claims%>

The default "index" action produces the correct results, but when you click on the header for "Review Date" the following SQL error is generated:

ActiveRecord::StatementInvalid (Mysql::Error: Not unique table/alias: 'reviews': SELECT `supp_claims`.* FROM `supp_claims` INNER JOIN `reviews` ON reviews.Supp_claim_id = supp_claims.id LEFT OUTER JOIN reviews ON reviews.Supp_claim_id = supp_claims.id WHERE (reviews.FirstReviewDate >= '2010-03-21')  ORDER BY reviews.FirstReviewDate ASC):

Any suggestions on how I could sort a result-set based on an attribute in an associated model?


No comments found

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket