Rails Model find where not equal
How can I find records in my databa开发者_如何学Cse on a not equal condition? I have this now, but is there a fancy rails-speak way of doing it?
GroupUser.where('user_id != ?',me)
In Rails 4.x (See http://edgeguides.rubyonrails.org/active_record_querying.html#not-conditions)
GroupUser.where.not(user_id: me)
In Rails 3.x
GroupUser.where(GroupUser.arel_table[:user_id].not_eq(me))
To shorten the length, you could store GroupUser.arel_table
in a variable or if using inside the model GroupUser
itself e.g., in a scope
, you can use arel_table[:user_id]
instead of GroupUser.arel_table[:user_id]
Rails 4.0 syntax credit to @jbearden's answer
Rails 4
GroupUser.where.not(user_id: me)
Rails 4:
If you want to use both not equal and equal, you can use:
user_id = 4
group_id = 27
GroupUser.where(group_id: group_id).where.not(user_id: user_id)
If you want to use a variety of operators (ie. >
, <
), at some point you may want to switch notations to the following:
GroupUser.where("group_id > ? AND user_id != ?", group_id, user_id)
The only way you can get it fancier is with MetaWhere.
MetaWhere has a newer cousin which is called Squeel which allows code like this:
GroupUser.where{user_id != me}
It goes without saying, that if this is the only refactor you are going to make, it is not worth using a gem and I would just stick with what you got. Squeel is useful in situations where you have many complex queries interacting with Ruby code.
You should always include the table name in the SQL query when dealing with associations.
Indeed if another table has the user_id
column and you join both tables, you will have an ambiguous column name in the SQL query (i.e. troubles).
So, in your example:
GroupUser.where("groups_users.user_id != ?", me)
Or a bit more verbose:
GroupUser.where("#{table_name}.user_id IS NOT ?", me)
Note that if you are using a hash, you don't need to worry about that because Rails takes care of it for you:
GroupUser.where(user: me)
In Rails 4, as said by @dr4k3, the query method not
has been added:
GroupUser.where.not(user: me)
In Rails 3, I don't know anything fancier. However, I'm not sure if you're aware, your not equal condition does not match for (user_id) NULL values. If you want that, you'll have to do something like this:
GroupUser.where("user_id != ? OR user_id IS NULL", me)
精彩评论