CODEWITHSUNDEEP
ruby-on-rails-4activerecord
Marko Ćilimković
Marko Ćilimković

Reputation: 753

Rails joining multiple methods and scopes

My rails app provides a list of services which are defined like this:

service.rb

has_and_belongs_to_many :clients
has_many :translations, class_name: ServiceTranslation, dependent: :destroy

def with_translation(lang)
  includes(:translations)
      .where("service_translations.language_id=?", lang.id)
      .references(:service_translations)
end

scope :with_clients, -> { select("services.*, count(clients_services.service_id) as clients_count")
  .joins(:clients).group('services.id').order('clients_count desc') }

So the with_translation method is eager loading the translations for the service (name, content etc) and the scope with_clients is making sure that the services are ordered by the number of linked clients (also, if no client is linked to a service, the service is not showing)

Both methods are working when called separately in my services_controller:

services_controller.rb

class ServicesController < ApplicationController
  def index
    #@services = Service.with_clients.page(params[:page])
    #or
    @services = Service.with_translation(@lang).page(params[:page])
  end
end

All is good and dandy, but I need both of those methods working together, so when I do this:

@services = Service.with_clients.with_translation(@lang).page(params[:page])

I get this error message:

PG::Error: ERROR: column "clients_count" does not exist LINE 1: SELECT DISTINCT "services"."id", clients_count AS alias_0 F...

I tried merging the two methods, creating the whole query in the controller, both in vain...any help would be appreciated!

Upvotes: 2

Views: 1313

Answers (1)

Michał Knapik
Michał Knapik

Reputation: 595

The error suggest the output query have clients_count as a column but it is just an alias for COUNT.

Try running scopes in rails console and analysing the queries in the log one by one:

you say this works

Service.with_clients

first make sure two scopes combined without pagination work correctly

Service.with_clients.with_translation(@lang)

The resulting query should be like this:

SELECT  DISTINCT "services"."id", count(clients_services.service_id) AS clients_count FROM "services" 
INNER JOIN "clients_services" ON "clients_services"."service_id" = "services"."id" 
INNER JOIN "clients" ON "clients"."id" = "clients_services"."client_id" 
LEFT OUTER JOIN "service_translations" ON "service_translations"."service_id" = "services"."id" 
WHERE (service_translations.language_id=2) 
GROUP BY services.id
ORDER BY clients_count desc

Upvotes: 1

Related Questions