CODEWITHSUNDEEP
sqlrubyruby-on-rails-4
Beengie
Beengie

Reputation: 1608

SQL Query filtering records

I am working on a query to show Jobquotes that are not awarded (awarded: false). The code below is working well, but I have other Jobquotes that are awarded (awarded: true) with the same job.id. I was wondering if there was a way to filter the results to remove any Jobquotes that have the same job.id where one of them is awarded.

Upvotes: 0

Views: 32

Answers (1)

engineersmnky
engineersmnky

Reputation: 29613

You could try something like this: (Please note I feel there is a better way to do this just can't think of anything right now)

class Jobquote < ActiveRecord::Base
  belongs_to :job
  belongs_to :quote
  scope :awarded_job_quotes, ->{ where(awarded: true)}
  scope :open_job_quotes, -> {
        awarded_job_ids = awarded_job_quotes.pluck(:job_id)
        where.not(job_id: awarded_job_ids)
        }
  scope :display_open_job_quotes, -> {
        open_job_quotes.select('*, (total_cost/quote_qty) AS cost_each')
        .includes(job: [:part], quote: [:vendor])
        .where.not("quotes.quote_number" => nil)
        .order("parts.number, cost_each")
        .group(:quote_id, "parts.number")
        }
end

You can then call as

 Jobquote.display_open_job_quotes

What this will do is look for all awarded jobs and then exclude all of them from the query by job_id. I then created a scope(display_open_job_quotes) to just return the requested data as I feel long query chains like this can be distracting in a controller.

Note:This will run an additional query to get the initial job_ids.

Upvotes: 2

Related Questions