CODEWITHSUNDEEP
ruby-on-railspostgresql
Marc
Marc

Reputation: 1043

Postgresql query to return where all values of a column are false

I have a tables for jobs and bids. Each job has many bids. There is a bid column "is_selected" that is a boolean. Only one bid can be is_selected=true for a job.

My question is that I want to return the number of jobs where all bids are is_selected=false.

So for example, 

Job 1 has bids a=true, b=false, c=false, d=false 
Job 2 has bids a=false, b=false, c=false
Job 3 has bids a=false, b=false
Job 4 has no bids

The result of this query should return a query with 3 jobs (only Job 1 has selected a bidder; so it should return job 2, 3, 4).

How can this be done in postgresql or ActiveRecord/Rails?

Update with data structure:

3 tables

Event (has_many jobs)

Job (has_many bids, belongs_to event)

Bid (belongs_to job)

Upvotes: 0

Views: 240

Answers (2)

harmic
harmic

Reputation: 30577

This should work as a pure SQL solution:

select count(1) 
from jobs 
where id not in (select distinct job_id from bids where is_selected=true)

assuming you only want to know the total number of jobs that have no bids. If you want the details of the jobs you can replace the contents of the select clause.

Upvotes: 1

house9
house9

Reputation: 20594

here is one way to do it in ruby - not as efficient as doing it at the database

all_jobs = Job.includes(:bids)
logger.debug "ALL JOBS: #{all_jobs.length}"

jobs = all_jobs.reject { |job| job.bids.detect(&:is_selected) }
logger.debug "UNSELECTED JOBS #{jobs.length}"

Upvotes: 1

Related Questions