On Wed, Jun 8, 2011 at 1:37 PM, Jeremy Evans <[email protected]> wrote:
> On Jun 8, 9:20 am, Michael Lang <[email protected]> wrote:
>> > uninsured_codes = nil
>> => nil
>> > DB[:insurances].filter(:code => uninsured_codes).sql
>>
>> => "SELECT * FROM `insurances` WHERE (`code` IS NULL)"
>>
>> Good!
>>
>> > uninsured_codes = ['', 'NO INS', 'NO INS HS', 'HMLS-NOIN', 'NDA']
>>
>> => ["", "NO INS", "NO INS HS", "HMLS-NOIN", "NDA"]>
>> DB[:insurances].filter(:code => uninsured_codes).sql
>>
>> => "SELECT * FROM `insurances` WHERE (`code` IN ('', 'NO INS', 'NO INS
>> HS', 'HMLS-NOIN', 'NDA'))"
>>
>> Good!
>>
>> > uninsured_codes = [nil, '', 'NO INS', 'NO INS HS', 'HMLS-NOIN', 'NDA']
>>
>> => [nil, "", "NO INS", "NO INS HS", "HMLS-NOIN", "NDA"]>
>> DB[:insurances].filter(:code => uninsured_codes).sql
>>
>> => "SELECT * FROM `insurances` WHERE (`code` IN (NULL, '', 'NO INS',
>> 'NO INS HS', 'HMLS-NOIN', 'NDA'))"
>>
>> Above doesn't work in mysql. The record with NULL is not returned.
>> Need to see:
>> => "SELECT * FROM `insurances` WHERE ((`code` IN ('', 'NO INS', 'NO
>> INS HS', 'HMLS-NOIN', 'NDA')) OR (`code` IS NULL))"
>>
>> With the above query, the NULL record is returned along with the others.
>>
>> Can Sequel be extended to do "OR <field> IS NULL" when an array with
>> legit values as well as nil is passed?
>
> Such a change is easy to make, but I think it is a bad idea. NULL =
> NULL is not true in SQL (it is NULL). Here's a simple example:
>
> artists = []
> artists << Artist.load(:id=>1)
> artists << Artist.new
> artist_ids = artists.map{|a| a.id} # [1, nil]
> albums_for_artists = Album.filter(:artist_id=>artist_ids).all
>
> With your idea, albums_for_artists will be all albums for artist 1 and
> all albums that don't have an artist. That's obviously not what is
> desired.
>
My natural inclination in a case like this would've been to do this:
artists = []
artists << Artist.load(:id=>1)
artists << Artist.new
artist_ids = artists.map{|a| a.id}.compact # [1]
albums_for_artists = Album.filter(:artist_id=>artist_ids).all
I say "natural inclination" because I actually do have code now where
I compact the array to eliminate the nil values as I didn't want to
risk looking for NULL's.
Today, I got frustrated with trying to add an "OR" clause to find
specific values or IS NULL on a column within a rather complex
existing query construct as I kept ending up with a full table list
because the "OR" wasn't parenthesized quite right with the column I
wanted to be either NULL or the specific values. I never did quite
get it right and just ended up running the query twice and merging the
results (due to my own short-comings in fully understanding how Sequel
combines the ANDs and OR's) since I had to get the task done extremely
quick for an upcoming meeting.
Michael
--
http://codeconnoisseur.org
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
