Rails 3中ActiveRecord的where子句其中id设置或空 [英] Rails 3 ActiveRecord where clause where id is set or null

问题描述

我有一个ActiveRecord，注释，有两列：USER_ID和的post_id，它可以为null。与空USER_ID和的post_id的注释是全球性的注解是适用于所有用户的所有帖子。

I have an ActiveRecord, Annotation, with two columns: user_id and post_id, which can be null. An annotation with null user_id and post_id are "global" annotations that are applicable to all posts across all users.

我想检索与特定用户的回复和全球所有的注解相关联的所有注释。在MySQL，SQL命令将

I would like to retrieve all annotations associated with a particular user's post AND all global annotations. In MySQL, the SQL command would be

select * from annotations where (user_id = 123 and post_id = 456) or (user_id is null and post_id is null)

在Rails 3，什么是code最好的办法这是一个Annotation.where条款？

In Rails 3, what is best way to code this as a Annotation.where clause?

推荐答案

不幸的是没有真正伟大的方式来使用或SQL语法。你最好的两个选项都可能是自己使用绑定的参数写在where子句：

Unfortunately there is no really great way to use OR sql syntax. Your best two options are probably to write the where clause yourself using bound parameters:

annotations = Annotation.where("(user_id = ? and post_id = ?) OR (user_id is null and post_id is null)").all

或者你也可以潜入阿雷尔，并使用该语法手艺它（检查出的 asciicast的阿雷尔帖子）。

Or you can dive into Arel and use that syntax to craft it (check out asciicast's Arel post).

警告后，一切都在或呼叫伪code，不知道该怎么办的post_id为空 - 你可能有刚刚通过user_id是零和的post_id为空，以或（），但我最好的猜测是：

Warning, everything in the or call is psuedocode, no idea how to do 'post_id is null' - you may have to just pass "user_id is null and post_id is null" to or(), but my best guess is:

annotations = Annotation.arel_table
annotations.where(annotations[:user_id].eq(123).
            and(annotations[:post_id].eq(456)).
            or(annotations[:user_id].eq(nil).and(annotations[:post_id].eq(nil))

这篇关于Rails 3中ActiveRecord的where子句其中id设置或空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！