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

Question

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)

event_id (integer)

Bid (belongs_to job)

job_id (integer)
is_selected (Boolean)

If you want a good answer you need to provide better examples of your data as it's stored in the db. I can't tell what your schema looks like from this. And it's a good bet it's suboptimal as well. — Scott Marlowe
– Scott Marlowe, Commented Dec 29, 2013 at 0:41

house9 · Accepted Answer · 2013-12-28 18:58:37Z

1

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}"

answered Dec 28, 2013 at 18:58

house9

20.7k9 gold badges57 silver badges62 bronze badges

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

2 Comments

Marc Over a year ago

how would you do the sql query at the database level?

Scott Marlowe Over a year ago

How are you storing these data in the database?

harmic · Accepted Answer · 2013-12-29 06:52:26Z

1

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.

answered Dec 29, 2013 at 6:52

harmic

31k6 gold badges75 silver badges102 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related