Laravel 技巧总结

Laravel 使用技巧笔记

1、在 $request 中添加数据

$request->offsetSet('foo', 'bar');

$request->merge(['foo' => 'bar']);

2、WithAndWhereHas

public function scopeWithAndWhereHas($query, $relation, $constraint)
{
    return $query->whereHas($relation, $constraint)
                 ->with([$relation => $constraint]);
}

 3、按中文排序

$users = User::orderBy(\DB::raw('CONVERT(name using GBK)'))->get();

 4、Laravel5.5.* JSON数组取值，查询问题

例如,我们有一个users表其中有一个firends字段，存储的是json数组：

[{
    "id": 1,
    "img": "img-1.jpg",
    "name": "zhangsan"
},
{
    "id": 2,
    "img": "img-2.jpg",
    "name": "zhangsan"
},
{
    "id": 3,
    "img": "img-3",
    "name": "lisi"
}]

所以我想要做的是在Users表上查询来自Friends字段的所有内容,其中名字是zhangsan的所有记录

所以类似于：User :: where(‘friends->name’, 'zhangsan')–>get();

理想会得到如下结果：

[{
    "id": 1,
    "img": "img-1.jpg",
    "name": "zhangsan"
},
{
    "id": 2,
    "img": "img-2.jpg",
    "name": "zhangsan"
}]

然而并不是，正确写法是这样的：

原生SQL:

select * from users
where friends->'$.name' = 'zhangsan'  or JSON_CONTAINS(friends->'$[*].name', '"zhangsan"', '$');

Laravel5.5:

User::whereRaw('JSON_CONTAINS(friends->\'$[*].name\', \'"zhangsan"\', \'$\')'->get();

select created_at,template_name  from reports
where JSON_CONTAINS(sample_ids_json->'$[*].sample_id', '"R-180725-485599-BLD-989856_CF"', '$');

5、Laravel5.5.* query语句打印

\DB::enableQueryLog();
User::all();
return response()->json(\DB::getQueryLog());

6、Laravel5.5.* Carbon

文档地址 https://carbon.nesbot.com/

7、aravel5.5.* A1 A2 A3 ... A24 B1 B2 B3 ... B24 排序

数据:

B2

A4
B3
B4

A1
A2
A5
A6

A7
A8
A11

A3

A9
A12
A13
A10
B5

B6
A14
A15
A16
B7
B8
B9
B10
B11
B12
B13
B14
B15
B16
B17
A17
A18
A19
A20
A21
A22
A23
A24

B18
B19
B20
B21
B22
B1

SQL语句1：

SELECT
    * 
FROM
    `label_prints` 
ORDER BY SUBSTR(serial_number FROM 1 FOR 1), CAST(SUBSTR(serial_number FROM 2) AS UNSIGNED);

SQL语句2：

SELECT
    * 
FROM
    `label_prints` 
ORDER BY
CASE
    WHEN serial_number REGEXP '^[A-Z]{2}' THEN
    1 ELSE 0 
    END ASC,
CASE   
    WHEN serial_number REGEXP '^[A-Z]{2}' THEN
    LEFT ( serial_number, 2 ) ELSE LEFT ( serial_number, 1 ) 
    END ASC,
CASE   
    WHEN serial_number REGEXP '^[A-Z]{2}' THEN
    CAST( RIGHT ( serial_number, LENGTH( serial_number ) - 2 ) AS SIGNED ) ELSE CAST( RIGHT ( serial_number, LENGTH( serial_number ) - 1 ) AS SIGNED ) 
    END ASC;

SQL语句3：

SELECT
    * 
FROM
    `label_prints` 
ORDER BY
    serial_number REGEXP '^[A-Z]{2}' ASC,
IF
    ( serial_number REGEXP '^[A-Z]{2}', LEFT ( serial_number, 2 ), LEFT ( serial_number, 1 ) ),
    CAST(
    IF
        (
            serial_number REGEXP '^[A-Z]{2}',
            RIGHT ( serial_number, LENGTH( serial_number ) - 2 ),
            RIGHT ( serial_number, LENGTH( serial_number ) - 1 ) 
        ) AS SIGNED 
);

Laravel5.5查询语句：

LabelPrint::orderByRaw("SUBSTR(serial_number FROM 1 FOR 1), CAST(SUBSTR(serial_number FROM 2) AS UNSIGNED)")->get();

8、Mysql 查询结果逗号分割

select group_concat(name) from sample_types；

 9、手动创建分页器

1、如果你想手动创建分页实例并且最终得到一个数组类型的结果，可以根据需求来创建 Illuminate\Pagination\Paginator 或者 Illuminate\Pagination\LengthAwarePaginator 实例来实现。

<?php

use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;

// 分页
$currentPage = $request->page ?: 1;
return new LengthAwarePaginator(
    $patients->forPage($currentPage, 15),//当前的分页
    count($patients),//总页数
    15,//每页个数
    Paginator::resolveCurrentPage(),
    ['path' => Paginator::resolveCurrentPath()]
);

2、如果只需要共计和分页结果则

<?php

    $users = User::all();
    
    return [
       'total' => $users->count(),
       'data' => $users->forPage($request->page ? $request->page : 1, 15)  
    ];

 9、linux以某用户执行某条命令

格式： sudo -u 用户名 命令

sudo -u www-data php artisan migrate

 10、Laravel邮件发件人乱码、标题乱码、附件标题乱码解决办法

标题乱码：

$subject = "=?UTF-8?B?".base64_encode($subject)."?=";

发件人乱码 配置文件config/mail.php：

'from' => [
        'address' => env('MAIL_ADDRESS'),
        'name'    => "=?UTF-8?B?" . base64_encode(env('MAIL_NAME')) . "?="
    ],

参考：https://www.cnblogs.com/matric/p/9241208.html