Sort by join table field-CodePudding

I have a users table and a reporting_events table where reporting_events belongs to user.

I want to sort the users table based on the reporting events values. Users should be sortable based on values of various reporting event names.

For example, there can be reporting events of two users with reporting event name as "avg_response_time" and reporting event values as 1 and 2 for the two users. We should be able to sort with avg_response_time of user both in desc and asc orders.

What I have tried:

reports = users.joins(:reporting_events).where("reporting_events.name='avg_response_time'").order("reporting_events.value desc")

filter_params[:sort_order] gives the column and direction it should sort by. When I don't use distict I'm getting duplicate users in users list.

I want to list all users even if reporting_events does't exist for the user.

CodePudding user response：

You need a user an outer join instead on an inner join (default) :

users.joins('LEFT OUTER JOIN reporting_events ON reporting_events.user_id = users.id').where(...

CodePudding user response：

I came up with the following solution.

  def sorted_users(users)
      return users unless filter_params[:sort_order].present?

      return users.order(order_by) if sort_by_name?

      return sorted_users_by_count if sort_by_count?

      sorted_users_by_value
    end

    def sorted_users_by_count
      users_with_reporting_events.group("users.id").order("count(reporting_events) #{sort_direction}")
    end

    def sorted_users_by_value
      users_with_reporting_events.order("reporting_events.value #{sort_direction} NULLS LAST")
    end

    def sort_by_count?
      [ReportingEvent::EVENT_TYPES[:ASSIGN_AGENT], ReportingEvent::EVENT_TYPES[:CLOSE_CONVERSATION]].include?(filter_params[:sort_order][:column])
    end

    def sort_by_name?
      filter_params[:sort_order][:column] == "first_name"
    end

    def users_with_reporting_events
      users.left_outer_joins(:reporting_events).where(
        "reporting_events.name = ?", filter_params[:sort_order][:column]).or(users.where(reporting_events: { id: nil }))
    end