SQL and Rails complex query

Question

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 :/

Paul Richter · Accepted Answer · 2015-04-22 13:38:00Z

1

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.

edited Apr 22, 2015 at 13:38

answered Apr 21, 2015 at 18:16

Paul Richter

11.1k10 gold badges56 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ido Traini Over a year ago

Thank you for the help! The road look promising. But i have a duplication of some contracts in this way, when i have some contracts with more than one service associated at a worker.

Paul Richter Over a year ago

@IdoTraini oh yes I forgot about that. Just add .uniq to the end of the query. I'll update the answer in a moment. That will ensure only distinct Contract objects are loaded, no duplicates. The duplication is the result of the inner join caused by the .join method.

Ido Traini Over a year ago

@Richter, it's fine. Indeed, i've tried .uniq!, but it give me error, but without the ! it runs. thank you :)

Ido Traini Over a year ago

I'm not able to vote for the moment, but i will honor my debt asap ;)

Paul Richter Over a year ago

@IdoTraini Ah yes I believe its 15 pts for upvotes. I appreciate the accept, thanks.

|

ilan berci · Accepted Answer · 2015-04-21 19:26:07Z

0

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))

answered Apr 21, 2015 at 19:26

ilan berci

3,8791 gold badge18 silver badges21 bronze badges

1 Comment

Paul Richter Over a year ago

The OP wrote i need to produce a @contracts relationship that match all contracts that have in common the client and the worker. This sounds very much like they want to obtain a collection of Contract objects for a given client and worker, not Client objects.

Collectives™ on Stack Overflow

SQL and Rails complex query

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related