Laravel 小结

Laravel 模型(Eloquent)

Eloquent(模型)查询一条数据是否存在返回值是一个collection的集合.

使用empty() isset()等函数对collection集合判断是否为空没有作用.

collection集合中定义了一些方法.
使用$collection->isEmpty()来判断值是否为空

举个茄子

1
2
3
4
5
6
7
$user = User::where('username', '=', '茄子')->get();

if( !$user->isEmpty()){
retrun '有茄子'
}

return '没茄子';

Laravel join 子查询

框架中自带了selectRaw, whereRaw, orderByRaw等编写原生SQL的方法,但是没有 join 相关的原生查询方法。

DB::raw(); #运行原生SQL

举个茄子

现有一个需求,查询所有用户数据,并带出用户具有的权限.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# users 表
+-----------+-----+---------+----------------+
| Field | Key | Default | Extra |
+-----------+-----+---------+----------------+
| id | PRI | NULL | auto_increment |
| name | UNI | NULL | |
| password | | NULL | |
| is_active | | 1 | |

# user_info 表
+-----------+-----+---------+----------------+
| Field | Key | Default | Extra |
+-----------+-----+---------+----------------+
| id | PRI | NULL | auto_increment |
| user_id | | NULL | |
| nick_name | | NULL | |
| real_name | | NULL | |
| email | | NULL | |

# permissions 表
+-----------+-----+---------+----------------+
| Field | Key | Default | Extra |
+-----------+-----+---------+----------------+
| id | PRI | NULL | auto_increment |
| name | | NULL | |
| guard_name| | NULL | |
| created_at| | NULL | |
| updated_at| | NULL | |
+-----------+-----+---------+----------------+

# model_has_permissions 表
+----------------+-----+---------+-------+
| Field | Key | Default | Extra |
+----------------+-----+---------+-------+
| permission_id | PRI | NULL | |
| model_id | PRI | NULL | |
| model_type | PRI | NULL | |
+----------------+-----+---------+-------+

原生SQL写起来很简单:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$sql = <<<SQLCODE
SELECT
users.id,
users.name,
users_infos.nick_name,
new.p_name
FROM
users
LEFT JOIN
users_infos
ON
users.id = users_infos.user_id
LEFT JOIN
(
SELECT
permission.name AS p_name,
permission.id AS p_id,
permission.guard_name,
m.model_id
FROM
permissions
RIGHT JOIN
model_has_permissions AS m
ON
permission.id = m.permission_id
) AS new
ON
new.model_id = users.id
SQLCODE;

$user_infos = DB::select($sql);

将LEFT JOIN 中的子查询的结果起一个new 的新名字(类似于一个临时表)

Laravel自带的分页很棒,但是使用原生的SQL,就无法使用paginate()这个方法了,看来只能DB::raw()LEFT JOIN 添加原生SQL了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$pagesize = 10;
$users = DB::table('users')
->leftJoin('users_infos',
'users.id', '=', 'users_infos.user_id')
->leftJoin(
DB::raw("
(SELECT
p.id,
p.name AS p_name,
m.model_id
FROM
tb_permissions AS p
RIGHT JOIN
tb_model_has_permissions AS m
ON
p.id = m.permission_id
) AS tb_mo"),
'mo.model_id', '=', 'users.id')
->select('users.id',
'users.name',
'users.failed_count',
'mo.p_name',
'login_time',
'users.is_active',
'users.created_at',
'users_infos.nick_name', 'users_infos.real_name',
'users_infos.email',
'users_infos.cell')
->paginate($pagesize);

return $users;

由于配置文件中配置了表前缀,在命名临时子查询的时候要将表前缀加上去

Carbon 时间类常用方法

需要本地化,修改app/Providers/AppServiceProvider.php文件

1
2
3
4
5
6
7
public function boot()
{
//Carbon 本地化
\Carbon\Carbon::setLocale('zh');
//添加mysql最大字符串限制
Schema::defaultStringLength(191);
}

常用方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
$dt = Carbon::create(1975, 12, 25, 14, 15, 16);

var_dump($dt->toDateTimeString() == $dt); // bool(true) => uses __toString()
echo $dt->toDateString(); // 1975-12-25
echo $dt->toFormattedDateString(); // Dec 25, 1975
echo $dt->toTimeString(); // 14:15:16
echo $dt->toDateTimeString(); // 1975-12-25 14:15:16
echo $dt->toDayDateTimeString(); // Thu, Dec 25, 1975 2:15 PM

$dt = Carbon::now();

// $dt->toAtomString() is the same as $dt->format(DateTime::ATOM);
echo $dt->toAtomString(); // 1975-12-25T14:15:16-05:00
echo $dt->toCookieString(); // Thursday, 25-Dec-1975 14:15:16 EST
echo $dt->toIso8601String(); // 1975-12-25T14:15:16-0500
echo $dt->toRfc822String(); // Thu, 25 Dec 75 14:15:16 -0500
echo $dt->toRfc850String(); // Thursday, 25-Dec-75 14:15:16 EST
echo $dt->toRfc1036String(); // Thu, 25 Dec 75 14:15:16 -0500
echo $dt->toRfc1123String(); // Thu, 25 Dec 1975 14:15:16 -0500
echo $dt->toRfc2822String(); // Thu, 25 Dec 1975 14:15:16 -0500
echo $dt->toRfc3339String(); // 1975-12-25T14:15:16-05:00
echo $dt->toRssString(); // Thu, 25 Dec 1975 14:15:16 -0500
echo $dt->toW3cString(); // 1975-12-25T14:15:16-05:00

echo Carbon::now()->tzName; // America/Toronto
$first = Carbon::create(2012, 9, 5, 23, 26, 11);
$second = Carbon::create(2012, 9, 5, 20, 26, 11, 'America/Vancouver');

echo $first->toDateTimeString(); // 2012-09-05 23:26:11
echo $first->tzName; // America/Toronto
echo $second->toDateTimeString(); // 2012-09-05 20:26:11
echo $second->tzName; // America/Vancouver

# 可以使用 parse 方法解析任何顺序和类型的日期(结果为 Carbon 类型的日期时间对象):

echo Carbon::parse('2016-10-15')->toDateTimeString(); //2016-10-15 00:00:00
echo Carbon::parse('2016-10-15')->toDateTimeString(); //2016-10-15 00:00:00
echo Carbon::parse('2016-10-15 00:10:25')->toDateTimeString(); //2016-10-15 00:10:25

echo Carbon::parse('today')->toDateTimeString(); //2016-10-15 00:00:00
echo Carbon::parse('yesterday')->toDateTimeString(); //2016-10-14 00:00:00
echo Carbon::parse('tomorrow')->toDateTimeString(); //2016-10-16 00:00:00
echo Carbon::parse('2 days ago')->toDateTimeString(); //2016-10-13 20:49:53
echo Carbon::parse('+3 days')->toDateTimeString(); //2016-10-18 20:49:53
echo Carbon::parse('+2 weeks')->toDateTimeString(); //2016-10-29 20:49:53
echo Carbon::parse('+4 months')->toDateTimeString(); //2017-02-15 20:49:53
echo Carbon::parse('-1 year')->toDateTimeString(); //2015-10-15 20:49:53
echo Carbon::parse('next wednesday')->toDateTimeString(); //2016-10-19 00:00:00
echo Carbon::parse('last friday')->toDateTimeString(); //2016-10-14 00:00:00

// The most typical usage is for comments
// The instance is the date the comment was created and its being compared to default now()
echo Carbon::now()->subDays(5)->diffForHumans(); // 5 days ago

echo Carbon::now()->diffForHumans(Carbon::now()->subYear()); // 1 year after

$dt = Carbon::createFromDate(2011, 8, 1);

echo $dt->diffForHumans($dt->copy()->addMonth()); // 1 month before
echo $dt->diffForHumans($dt->copy()->subMonth()); // 1 month after

echo Carbon::now()->addSeconds(5)->diffForHumans(); // 5 seconds from now

echo Carbon::now()->subDays(24)->diffForHumans(); // 3 weeks ago
echo Carbon::now()->subDays(24)->diffForHumans(null, true); // 3 weeks

echo Carbon::create(2018, 2, 26, 4, 29, 43)->diffForHumans(Carbon::create(2016, 6, 21, 0, 0, 0), false, false, 6); // 1 year 8 months 5 days 4 hours 29 minutes 43 seconds after

You can also change the locale of the string using Carbon::setLocale(‘fr’) before the diffForHumans() call. See the localization section for more detail.