#15 Fun with Find Conditions

Apr 06, 2007 | 4 minutes | Active Record

You can pass more than simple strings to find conditions. Arrays, ranges, and nil values can be passed as well. In this episode you will see the tricks involved with passing these odd objects to find conditions. (Update: audio fixed).

Click to Play Video ▶

Download:
mp4Full Size H.264 Video (11.1 MB)
m4vSmaller H.264 Video (6.77 MB)
webmFull Size VP8 Video (15.8 MB)
ogvFull Size Theora Video (17.9 MB)

这次我们来讨论一下关于find方法查询条件的话题。以下是Rails Console输出的SQL查询语句。对应的动作是查找所有优先级为3的未完成任务。

          terminal
        
>>Task.count(:all,:conditions=>["complete=? and priority=?,false,3])
=>2

对应的SQL查询是

          SQL
        
SELECT count(*) AS count_all FROM tasks WHERE (complete=0 and priority=3);

查找所有优先级为3的未完成任务。

以上代码正常工作是没有问题的，可是如果想查找出所有优先级为nil的任务呢？

          terminal
        
Task.find(:all, :conditions > ["completed = ? AND priority = ?", false, nil])

难道要生成如下的SQL查询码？

          SQL
        
SELECT * FROM "tasks" WHERE (completed = 'f' AND priority = NULL);

查询空值是生成了错误的SQL语句。

上面的查询不能工作，正确的写法应当是priority IS NULL，priority = NULL。同理，当查找多个优先级的条件时应当使用IN而不是=，并且得用小括号扩这备选值以便满足SQL语法

          terminal
        
Task.find(:all, :conditions > ["completed = ? AND priority IN (?)", false, [1,3]])

我们期待的，正确语法的SQL语句是

          SQL
        
SELECT * FROM "tasks" WHERE (completed = 'f' AND priority IN (1,3));

从Rails1.2开始，可以通过传入hash作为查询条件来解决这个问题了。正确性由Rails来保证。

          terminal
        
Task.find(:all, :conditions > { :completed > false, priority > [1,3] }

对应的SQL查询是

          SQL
        
SELECT * FROM "tasks" WHERE (completed = 'f' AND priority IN (1,3)

使用hash作为查询条件及生成的SQL查询

          terminal
        
Task.find(:all, :conditions > {:completed > false, priority > 2..4})

对应的SQL查询是

          SQL
        
SELECT * FROM "tasks" WHERE ("tasks"."priority" BETWEEN 2 AND 4 AND "tasks"."completed" = 'f');

传入Range会被转换为BETWEEN字句。

使用动态字段查询方法

在episode 2 曾经介绍过find_by 方法。同样也支持hash和Range作为查询条件。

          terminal
        
Task.find_by_priority(1..5)

对应的SQL查询是

          SQL
        
SELECT * FROM "tasks" WHERE ("tasks"."priority" BETWEEN 1 AND 5) LIMIT 1;

作为结论，我们知道，为了保证生成正确格式的SQL查询语句，推荐使用hash格式的查询条件。