Ransack error when sorting by joined table attribute: table name "users" specified more than once

Question

When clicking on the table header to sort by user_email(see view), the query it tries to run (see console output) has a duplicate left join users on users.id = invites.user_id.

That appears to be causing the problem, but how to avoid it I'm not sure.

Error:

ActiveRecord::StatementInvalid in Admin::Invites#index
PG::DuplicateAlias: ERROR:  table name "users" specified more than once

Console Output:

: SELECT  
    invites.id,
    invites.email,
    users.id as user_id,
    users.email as user_email FROM "invites" 
  LEFT OUTER JOIN "users" ON "users"."id" = "invites"."user_id" 
  left join users on users.id = invites.user_id 
  ORDER BY "users"."email" ASC LIMIT $1 OFFSET $2):

Controller

@q = query.ransack(params[:q])
@invites = @q.result.page params[:page]

query method called by controller

def query
  Invite.select('invites.id,
                 invites.email,
                 users.id as user_id,
                 users.email as user_email')
  .joins('left join users on users.id = invites.user_id')
end

View

<th><%=sort_link(@q, :email, 'Sendee Email') %></th>
<th><%=sort_link(@q, :user_email, 'Sender Email') %></th>

I'm trying Ransack for the first time (and it's awesome), but I would love to avoid this error somehow.

Any advice would be very appreciated.

UPDATE

{"s"=>"user_email asc"}, "controller"=>"admin/invites", "action"=>"index"} permitted: false>

<table>
  <thead>
    <tr>
      <th><%= sort_link(@q, :email, 'Sendee Email') %></th>
      <th><%= sort_link(@q, :user_email, 'Accepted') %></th>
    </tr>
  </thead>
  <tbody>
    <% @invites.each do |i| %>
      <tr>
        <td><%= i.email %></td>
        <td><%= i.user_email %></td>
      </tr>
    <% end %>
  </tbody>
</table>

...

Answer 1

Your problem is .joins('left join users on users.id = invites.user_id'), you need to change/remove it because it always adds a join to your SQL even when it'a not needed - Ransack is smart enough to figure out when do the join based on params you pass to ransack. This question may help you, you have to rewrite query method

First Scenario

{"s"=>"email asc"}, "controller"=>"admin/invites", "action"=>"index"} permitted: false>

generated SQL

SELECT  
  invites.id,
  invites.email,
  users.id as user_id,
  users.email as user_email FROM "invites" 
left join users on users.id = invites.user_id 
ORDER BY "users"."email" ASC LIMIT $1 OFFSET $2)

one JOIN - SQL is ok

Second Scenario

{"s"=>"user_email asc"}, "controller"=>"admin/invites", "action"=>"index"} permitted: false>

generated SQL

SELECT  
  invites.id,
  invites.email,
  users.id as user_id,
  users.email as user_email FROM "invites" 
LEFT OUTER JOIN "users" ON "users"."id" = "invites"."user_id" 
left join users on users.id = invites.user_id 
ORDER BY "users"."email" ASC LIMIT $1 OFFSET $2)

two JOINs - SQL is bad