CODEWITHSUNDEEP
sqlruby-on-rails
Ido Traini
Ido Traini

Reputation: 83

SQL and Rails complex query

I'm building a Rails application for to manage a services company scheduling.

For the scope of this question, the model structure is:

Client Has Many Contracts

Contracts Has Many Services

Workers Has Many Services

In my query i have a worker and a client, and i need to produce a

@contracts relationship

that match all contracts that have in common the client and the worker.

I've tried in lot of ways, but if i produce recursively the @contracts list via select{} and a loop, i obtain an array that is not an ActiveRecord and it cannot be used in the rails app (for example in erb).

I'm stuck :/

Upvotes: 1

Views: 64

Answers (2)

Paul Richter
Paul Richter

Reputation: 11082

Assuming you have the proper has_many and belongs_to relationships setup, your query would probably look something like this:

Contract.joins(services: :worker)
        .where(services: {worker_id: target_worker_id})
        .where(contracts: {client_id: target_client_id}).uniq

where target_worker_id and target_client_id are the worker and clients you wish to filter by.

This query will join contracts to the services table (because you said "Contracts has many Services"), which also joins the services table to the workers table (because you said "Workers has many services"). In short, it appears that services is a join table, the result of a many-to-many relationship, hence the join clause.

Edit: Note the .uniq, which is necessary to ensure only distinct Contract objects are loaded. Otherwise, as you experienced, the inner join caused by the .join method will essentially create a collection of duplicates.

Let me know if I've misunderstood anything, or if you have additional questions on this answer.

Quick note: You wrote i obtain an array that is not an ActiveRecord and it cannot be used in the rails app (for example in erb). I'm not entirely clear what you mean, but any instance variable created in the controller can be used in ERB; it doesn't need to be an active record object. As stated though, I'm not sure if this is what you meant.

Upvotes: 1

ilan berci
ilan berci

Reputation: 3881

class Client < ActiveRecord::Base
  has_many :conctracts, inverse_of: client, dependent: :destroy
end

class Contract < ActiveRecord::Base
  has_many :services
end

class Worker < ActiveRecord::Base
  has_many :services

end

class Service < ActiveRecord::Base
  belongs_to :worker
  belongs_to :contract

  has_many :clients, through: contract

  scope :workers, ->(*w) {
    where(worker_id: w.flatten.compact.uniq)
  }

  scope :contracts, ->(*c) {
    where(contract_id: c.flatten.compact.uniq)
  }
end

workers = Worker.all

clients = Client.join(contracts: {services: worker}).merge(Service.workers(workers))

Upvotes: 0

Related Questions