Empty Array is NULL in Rails where find

Question

I have the following simple where find condition:

Player.where('id NOT IN (?)', @groups.collect {|g| g.player_ids}.flatten)

So this finds all players that are currently not in any groups, right? Well, the problem is that if there are no players in any groups, nothing is shown. The reason for this is the following SQL is generated:

SELECT "players".* FROM "players" WHERE (id NOT IN (NULL))

This seems like a strange default to me, and I've tested it in Rails 3.0.7 and 3.1.0.rc4 with the same result. Now I could create some conditions if @groups.collect {|g| g.player_ids} is empty, but is there a better way to do this?

Sam Ruby · Accepted Answer · 2011-07-20 02:41:49Z

2

You may not like this any better, but...

Player.where('id not in (select id from players where id in (?))', 
    @groups.collect {|g| g.player_ids}.flatten)

answered Jul 20, 2011 at 2:41

Sam Ruby

4,35024 silver badges23 bronze badges

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

2 Comments

amunds Over a year ago

That works the way I wanted, and it will work perfectly since I'm only working on collections smaller than 100. Thanks!

Anatoly Over a year ago

subquery is not so bad if use small collections

Luke · Accepted Answer · 2012-08-06 22:16:31Z

0

@groups.collect {|g| g.player_ids}.flatten.join(',')

edited Aug 6, 2012 at 22:16

Luke

11.5k43 gold badges63 silver badges70 bronze badges

answered Jul 20, 2011 at 2:28

Anatoly

15.6k5 gold badges57 silver badges77 bronze badges

6 Comments

amunds Over a year ago

Unfortunately that don't work with PostgreSQL, it gives the following error: PGError: ERROR: invalid input syntax for integer: ""

Anatoly Over a year ago

what's the SQL generated by this query. BTW, do you handle the case when this id-s collection is empty?

amunds Over a year ago

SQL: SELECT "players".* FROM "players" WHERE (id NOT IN ('')). I'm not doing anything to handle the empty collection as I assumed it would default to get all records if the collection was empty.

Anatoly Over a year ago

it's not default. The ruby code just returns the empty collection, then AREL combine the SQL query.

amunds Over a year ago

I originally wrote the query in MongoDB and it worked like that there, I guess I just expected it to be a standard. Thanks for your time, Mikhailov.

|

Collectives™ on Stack Overflow

Empty Array is NULL in Rails where find

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related