如何在ActiveRecord查询中使用或条件

I want to grab all the users that either have an email as the one supplied or the first and last name. So for example:

我想要获取所有的用户，这些用户要么提供了电子邮件，要么提供了名和姓。举个例子:

users = User.where(:first_name => "James", :last_name => "Scott")

which will return all the users that have the first and last name of "James" & "Scott".

它将返回所有拥有“James”和“Scott”名和姓的用户。

users = User.where(:email => "james@gmail.com")

which will return the user with the email as "james@gmail.com".

它将以“james@gmail.com”的形式返回用户。

Is there a way to do a where clause to return either the users with the same first and last name and the user with the email that matches in one where query or do I need to do a merge on the 2 separate where clauses.

有没有一种方法可以做where子句返回相同的姓和名的用户和匹配的电子邮件的用户或者我需要在2个分离的where子句上进行合并。

5 个解决方案

#1

Rails 5 comes with an or method.

Rails 5带有or方法。

This method accepts an ActiveRecord::Relation object. eg:

此方法接受一个ActiveRecord::关系对象。例如:

User.where(first_name: 'James', last_name: 'Scott')
    .or(User.where(email: 'james@gmail.com'))

#2

Try this:

试试这个:

users = User.where("(first_name = 'James' and last_name = 'Scott') or email = 'james@gmail.com'")

To interpolate variables:

插入变量:

users = User.where("(first_name = ? and last_name = ?) or email = ?", first_name, last_name, email)

#3

If you prefer a DSL approach (with no SQL), can use the underlying Arel layer:

如果您喜欢DSL方法(没有SQL)，可以使用底层的Arel层:

t = User.arel_table
users = User.where(
  (t[:first_name].eq('Ernie').and(t[:last_name].eq('Scott')).
    or(t[:email].eq('james#gmail.com'))
)

That's a bit ugly, but if you use some wrapper over Arel (for example, this one), the code is much nicer:

这有点难看，但是如果您在Arel上使用一些包装器(例如，这个)，代码就更好了:

users = User.where(
  ((User[:first_name] == 'Ernie') & (User[:last_name] == 'Scott')) |
   (User[:email] == 'james#gmail.com')
)

#4

If you don't want to write SQL as the others have mentioned, you could access the arel structures directly (looks like another answer has that) or use the squeel gem to do it much more elegantly:

如果您不想像其他人提到的那样编写SQL，您可以直接访问arel结构(看起来像另一个答案那样)，或者使用squeel gem更优雅地完成它:

User.where{(first_name == 'Ernie') & (last_name == 'Scott') | (email == 'james#gmail.com')}

#5

You could use string conditions, as in Client.where("orders_count = ? AND locked = ?", params[:orders], false), and use OR in conjunction with AND. Be careful with the priority of those operators though.

您可以使用字符串条件，如在客户端中。(“orders_count = ?并且锁定= ?"，params[:订单]，错误)，和使用或结合与和。不过要注意这些操作符的优先级。

Cf Active Record Query Interface guide

Cf活动记录查询界面指南

#1