流畅的查询生成器(Fluent)

目录

基础介绍

流畅的查询生成器(Fluent Query Builder)是Laravel建立SQL查询和操作数据库的强大接口. 所有查询使用prepared statements语句, 防止SQL注入保护.

可以开始使用DB类的table方法执行流畅查询. 只需标明您要查询的表:

$query = DB::table('users');

你现在构建了一个基于"users"表的流畅的查询生成器. 使用这个查询生成器, 你可以检索, 插入, 更新或删除表中的记录:

查询数据

从数据库中检索出记录的数组:

$users = DB::table('users')->get();

提示: get方法返回一个对象数组, 数组项的属性对应表的相应的列.

从数据库中检索出一条记录:

$user = DB::table('users')->first();

通过主键从数据库中检索出一条记录:

$user = DB::table('users')->find($id);

提示:如果没查询到数据, first方法会返回null. get会返回空数组.

从数据库中检索出一个单个列的值:

$email = DB::table('users')->where('id', '=', 1)->only('email');

只从数据库中选择某些列:

$users = DB::table('users')->get(array('id', 'email as user_email'));

从数据库中选择不相同的(distinct)结果集:

$user = DB::table('users')->distinct()->get();

建立Where子句

where 和 or_where

有多种方法协助你构建where子句. 这些方法中最基础的是whereor_where方法. 下面是如何使用它们:

return DB::table('users')
    ->where('id', '=', 1)
    ->or_where('email', '=', 'example@gmail.com')
    ->first();

当然, 你会只用简单地=查询. 您还可以使用>, <, != (<>)like:

return DB::table('users')
    ->where('id', '>', 1)
    ->or_where('name', 'LIKE', '%Taylor%')
    ->first();

正如你已经了解的, where方法将使用AND条件追加到查询语句后面, 而or_where方法将使用OR条件追加.

where_in, where_not_in, or_where_in, and or_where_not_in

where_in方法 允许你简单的构建基于数组项的检索查询:

DB::table('users')->where_in('id', array(1, 2, 3))->get();

DB::table('users')->where_not_in('id', array(1, 2, 3))->get();

DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_in('id', array(1, 2, 3))
    ->get();

DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_not_in('id', array(1, 2, 3))
    ->get();

where_null, where_not_null, or_where_null, and or_where_not_null

Twhere_null方法提供了对数据的null值的检索:

return DB::table('users')->where_null('updated_at')->get();

return DB::table('users')->where_not_null('updated_at')->get();

return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_null('updated_at')
    ->get();

return DB::table('users')
    ->where('email', '=', 'example@gmail.com')
    ->or_where_not_null('updated_at')
    ->get();

嵌套的Where子句

您可能会发现需要一个括号内的WHERE子句的集合. 只需通过一闭包函数作为or_where方法的参数就好:

$users = DB::table('users')
    ->where('id', '=', 1)
    ->or_where(function($query)
    {
        $query->where('age', '>', 25);
        $query->where('votes' '>', 100);
    })
    ->get();

上面的例子生成一个像这样的SQL语句:

SELECT * FROM "users" WHERE "id" = ? OR ("age" > ? AND "votes" > ?)

动态Where子句

动态where方法, 是提高代码的可读性很棒的方法, 下面是例子:

$user = DB::table('users')->where_email('example@gmail.com')->first();

$user = DB::table('users')->where_email_and_password('example@gmail.com', 'secret');

$user = DB::table('users')->where_id_or_name(1, 'Fred');

表连接

需要连接到另一个表? 试下joinleft_join方法:

DB::table('users')
    ->join('phone', 'users.id', '=', 'phone.user_id')
    ->get(array('users.email', 'phone.number'));

你要连接的table作为第一个参数传入.剩下的三个参数是用来构建ON后面的join子句的.

一旦你会使用join方法,你也就会使用left_join方法了.方法的使用方法是一样的:

DB::table('users')
    ->left_join('phone', 'users.id', '=', 'phone.user_id')
    ->get(array('users.email', 'phone.number'));

你也可以将闭包函数传入到第二个参数, 实现增加多个ON子句条件:

DB::table('users')
    ->join('phone', function($join)
    {
        $join->on('users.id', '=', 'phone.user_id');
        $join->or_on('users.id', '=', 'phone.contact_id');
    })
    ->get(array('users.email', 'phone.numer'));

结果排序

你可以很容易地使用order_by方法排序查询结果, 通过第二个参数实现正序和逆序排列标识:

return DB::table('users')->order_by('email', 'desc')->get();

当然, 你也可以指定多列作为排序依据:

return DB::table('users')
    ->order_by('email', 'desc')
    ->order_by('name', 'asc')
    ->get();

跳过和取得

如果想限定查询的结果数量, 你可以使用take方法:

return DB::table('users')->take(10)->get();

设置查询的偏移量(OFFSET),使用skip方法:

return DB::table('users')->skip(10)->get();

聚集

想得到MIN, MAX, AVG, SUM, 或 COUNT的值?只需要把列名传入进去就好:

$min = DB::table('users')->min('age');

$max = DB::table('users')->max('weight');

$avg = DB::table('users')->avg('salary');

$sum = DB::table('users')->sum('votes');

$count = DB::table('users')->count();

当然你也可以通过where子句限定聚集结果:

$count = DB::table('users')->where('id', '>', 10)->count();

表达式

有时你可能需要通过内置的SQL函数给某列设值, 例如"NOW()".但是直接写入的话会自动引用和转义. 所以需要使用DB类的raw方法:

DB::table('users')->update(array('updated_at' => DB::raw('NOW()')));

raw方法告诉查询直接作为字符串, 而不是作为一个绑定参数注入的表达的内容. 例如, 你也可以使用递增列值的表达式: DB::table('users')->update(array('votes' => DB::raw('votes + 1')));

当然, 递增递减的方便方法也会提供:

DB::table('users')->increment('votes');

DB::table('users')->decrement('votes');

插入数据

插入方法的参数是数组. 插入方法将返回true或false以表示插入是否成功:

DB::table('users')->insert(array('email' => 'example@gmail.com'));

插入一条一个有自动递增ID的记录. 你可以使用 insert_get_id方法在插入数据后返回ID:

$id = DB::table('users')->insert_get_id(array('email' => 'example@gmail.com'));

提示: The insert_get_id method expects the name of the auto-incrementing column to be "id".

更新数据

更新方法的参数是数组, 方式如下:

$affected = DB::table('users')->update(array('email' => 'new_email@gmail.com'));

当让,当你想更新指定的数据时, 在更新之前请先指定where子句:

$affected = DB::table('users')
    ->where('id', '=', 1)
    ->update(array('email' => 'new_email@gmail.com'));

删除数据

方法示例:

$affected = DB::table('users')->where('id', '=', 1)->delete();

通过ID主键快速删除:

$affected = DB::table('users')->delete(1);