Skip to content

Instantly share code, notes, and snippets.

@darrenterhune darrenterhune/finder.rb
Last active Aug 29, 2015

Embed
What would you like to do?
Find all users unless has_many matches a specific date
User
has_many :bookings
| id | name |
---------------
| 1 | amy |
| 2 | jack |
Booking
belongs_to :user
| id | date | user_id |
-------------------------------
| 1 | 2014-01-01 | 1 |
| 2 | 2014-01-02 | 2 |
| 3 | 2014-01-03 | 1 |
| 4 | 2014-01-04 | 2 |
| 5 | 2014-01-05 | 1 |
specific_date = '2014-01-03'
# I've tried a whole bunch of combinations of the following:
User.joins(:bookings).where('bookings.date = ?', specific_date).having('count(*) != 1')
# Query should only return User 'jack' because 'amy' has an associated booking with date = specific_date
# The following in plain mysql works for what I want, but I cannot find a solution to write this activerecord query:
HAVING (SELECT COUNT(*) FROM bookings WHERE bookings.date = '2014-01-03' AND bookings.user_id = users.id) != 1
# I cannot use straight sql because "#{specific_date}" opens up sql injection... and it's just ugly
@fertech

This comment has been minimized.

Copy link

fertech commented Mar 13, 2014

hmmm, been looking into it. Haven't been able to find a way, other than having two queries, finding the booked vendors first, then getting the users not in the result... Too expensive?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.