Last active
August 29, 2015 13:57
-
-
Save darrenterhune/9476563 to your computer and use it in GitHub Desktop.
Find all users unless has_many matches a specific date
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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?