在看ActiveRecord,定义Criteria从condition、select、group、having、limit等出发,定义各种sql,的确是一个途径,但是否过于简单?很多情况下,似乎还是需要自己去拼sql,例如在kevinkorb的博客中(http://www.kevinkorb.com/post/26)提到如下例子:
$price_low = 1000;
$price_high = 5000;
$Products = Product::model()
->with(array('brand'))
->findAll("price > :price_low AND price < :price_high", array (
':price_low' => $price_low,
':price_high' => $price_high )
)
);
这种语法实际上并没有对DB操作进行封装,用PHP的数据结构来避免拼接sql,比如我想要一个查询:
select * from table where col1=2 or col1=3 or col1=4;
没错,就是想要有in的功能,我是否需要自己写好col1 in ($str_ids)
而这里的$str_ids需要我来自己拼接呢?
Perl中有SQL::Abstract类,基本上将需要自己拼sql的地方都用perl数据结构表达了,以上情况可以用:
my %where = (
requestor => 'inna',
worker => ['nwiger', 'rcwe', 'sfz'],
status => { '!=', 'completed' }
);
my($stmt, @bind) = $sql->select('tickets', '*', \%where);
生成的SQL为:
$stmt = "SELECT * FROM tickets WHERE
( requestor = ? ) AND ( status != ? )
AND ( worker = ? OR worker = ? OR worker = ? )";
@bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
也可以用in语法:
my %where = (
status => 'completed',
reportid => { -in => [567, 2335, 2] }
);
#Which would generate:
$stmt = "WHERE status = ? AND reportid IN (?,?,?)";
@bind = ('completed', '567', '2335', '2');
AR中是否有类似的功能呢?