I need to make a consolidated list of payable services, with filtering, ordering and pagination (with willpaginate gem) the payable services are stored in two separate models, Application and Support, both have similar fields (that I need to show in the columns) but I would need to have both in the same query.
here's my situation:
class Application < ActiveRecord::Base
// name, created_at, price ...
has_many :supports
end
class Support < ActiveRecord::Base
// name, application_id, created_at, price ...
belongs_to :application
end
and the controller:
class PaymentsController < ApplicationController
FILTER_STRINGS = {
requested: { reference_status: 'requested' },
sent: { reference_status: 'sent' },
completed: { reference_status: 'completed' }
}
ORDER_STRINGS = {
name: 'name %1$s',
price: 'price %1$s',
date: 'created_at %1$s'
}
def payable_list
@applications = Application.all
@applications = @applications.where(FILTER_STRINGS[filter_params[:filter].to_sym]) if filter_params[:filter]
order_string = format(ORDER_STRINGS[sort_params[:sort].downcase.to_sym], sort_params[:dir])
@applications = @applications.order(order_string)
@applications = @applications.paginate(page: params[:page] || 1, per_page: 20)
end
...
end
The controller currently only loads the Application, but I need to include the Support items as well, in the view I will append the string '(Support)' for support items, to differentiate the items.
How could I include both models in the same ActiveRecord_Relation, so that I can sort them together? I would have no issues with using Ruby to sort the lists, but then I'd have problems with will paginate who seems to only work with ActiveRecord_Relation.
I tried using a join clause, but that still returns an Application object
Application.joins('JOIN supports ON "supports"."application_id" = "applications"."id"')
I tried union as well, with this command:
Application.find_by_sql("SELECT
'application' AS role,
applications.id AS id,
applications.price AS price,
applications.application_id AS application_or_support,
to_char(applications.created_at, 'DD Month YYYY, HH24:MI') AS created_at,
FROM applications
UNION
select
'support' AS role,
supports.id AS id,
supports.price AS price,
supports.support_id AS application_or_support,
to_char(supports.created_at, 'DD Month YYYY, HH24:MI') AS created_at,
from supports;")
but it's only giving me the columns that have a match in the applications model.
I'm thinking that maybe I could make a new model not backed by a DB table, where the rows would originate from that union query, but I wouldn't know how to go about the syntax to get it done.