Rails Model find where not equal

Question

How can I find records in my database 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)

Tichon

What you have is pretty much a good Rails 3 way of doing it i think. – Kamakura

Answer 1

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}

Houseroom

What about in associations? - has_many :group_users, -> { where.not(status: "Declined") }, through: :groups, foreign_key: "user_id" – Nationalism

As a side note, it also works well chained: GroupUser.where(other_condition: true).where.not(user_id: user_id) – Characterize

Answer 2

Rails 4

GroupUser.where.not(user_id: me)

Organography

Answer 3

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)

Eyeball

What about GroupUser.where(group_id: group_id).where.not(user_id: user_id)? – Characterize

@EnricoCarlesso Thanks for posting. I updated my answer to include your suggestion. Thanks. – Eyeball

Answer 4

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.

Litharge

MetaWhere is great, but this task can be done without adding a gem. – Tientiena

@Tientiena of course the task can be done (and imho the way the OP is doing it is perfectly fine), the question is about doing it fancier. So if you think it can be done fancier without a gem, I would be very much interested in how to do that. – Litharge

Vikrant's answer does provide a solution doesn't involve sql or including another gem. But, I stand corrected, your answer is definitely the most coder-friendly/fancy. – Tientiena

adding a gem to accomplish such a simple task. Hitting a mozzy with a bazooka – Rudderpost

This is overkill. – Krahmer

Answer 5

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)

Parol

Answer 6

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)

Simoniac