在查询数据中,会有多个过滤条件,有的还需要或(or),下面以Laravel数据库操作中的 查询构建器 为例,分情况说明如何组装查询语句。

多条and过滤条件

代码:

$result = DB::table('table_name')
                ->where(array(
                    array('field1', '=', 'value1'),
                    array('field2', '=',  'value2')
                ))
                ->get();

实际执行的是:

select * from `table_name` where (`field1` = 'value1' and `field2` = 'value2')

多条and和or过滤条件

代码:

$result = DB::table('table_name')
                ->where(array(
                    array('field1', '=', 'value1'),
                    array('field2', '=',  'value2')
                ))
                ->orWwhere(array(
                    array('field1', '=', 'value1'),
                    array('field2', '=',  'value2')
                ))
                ->get();

实际执行的是:

select * from `table_name` where (`field1` = 'value1' and `field2` = 'value2') or (`field1` = 'value1' and `field2` = 'value2')

多条and和or过滤条件并有公共的and条件

代码:

$result = DB::table('table_name')
                ->where('field1', 'value1')
                ->where(function($query){
                    $query->where(array(
                        array('field2', '=', 'value2'),
                        array('field3', '=', 'value3')
                    ))->orWhere(function ($query){
                        $query->where(array(
                            array('field2', '=', 'value2'),
                            array('field3', '=', 'value3')
                        ));
                    });
                })
                ->get();

实际执行的是:

select * from `table_name` where `field1` = 'value1' and ((`field2` = 'value2' and `field3` = 'value3') or ((`field2` = 'value2' and `field3` = 'value3')))

多条and和or过滤条件并有公共的and条件并有参数传递

代码:

$result = DB::table('table_name')
                ->where('field1', 'value1')
                ->where(function($query)use($value2,$value3){
                    $query->where(array(
                        array('field2', '=', $value2),
                        array('field3', '=', $value3)
                    ))->orWhere(function ($query)use($value2,$value3){
                        $query->where(array(
                            array('field2', '=', $value2),
                            array('field3', '=', $value3)
                        ));
                    });
                })
                ->get();

实际执行的是:

select * from `table_name` where `field1` = 'value1' and ((`field2` = 'value2' and `field3` = 'value3') or ((`field2` = 'value2' and `field3` = 'value3')))

如未注明,均为原创,转载请注明来自Peak Xin's Blog

No Comments »