laravel 开发中知识积累|Annabelle's Blog

ORM whereExists 子查询


$query = AssetCves::query()
        ->where('entity', 'asset_websites')
        ->whereExists(function($query){
        $query->from(DB::raw('(select * from asset_websites) as ids'))
            ->select(['id'])
            ->whereRaw('asset_cves.entity_id=ids.id');
    });

上述查询将产生如下的 SQL 语句：

select * from `asset_cves` where `entity` = 'asset_websites' and exists 
(select `id` from (select * from asset_websites) as ids where asset_cves.entity_id=ids.id)

模型关联多态一对一


<?php

namespace App\Models\Mysql\V2;

use Illuminate\Database\Eloquent\Model;

class Asset extends Model
{

    protected $table = self::TABLE;
    protected $primaryKey = self::PRIMARY_KEY;

    public function asset_owner_info()
    {
        return $this->morphOne(AssetOwnerInfo::class, 'entity');
    }

}

<?php

namespace App\Models\Mysql\V2;

use Illuminate\Database\Eloquent\Model;

class AssetOwnerInfo extends Model
{

    const TABLE = 'asset_owner_info';
    const ALIAS = 'asset_owner_info';

    protected $table = self::TABLE;

    public function entity()
    {
        return $this->morphTo();
    }
}

faker 数据伪造

    $faker = Faker\Factory::create();

    for ($c=1; $c<=200; $c++) {

        $ports = [];
        $p_arr = [];
        $webs  = [];

        $max = rand(1, 8);
        for ($i = 1; $i <= $max; $i++) {
            $p       = rand(1, 65535);
            $p_arr[] = $p;
            $ports[] = [
                'port'          => $p,
                'protocol'      => $faker->randomElement(['http', 'https', 'ssh', 'ftp']),
                'apply'         => $faker->randomElement([
                    'HP Comware switch sshd',
                    'Mysql',
                    'Apache httpd',
                    'Squid http proxy',
                    'kube-proxy',
                ]),
                'apply_version' => $faker->randomElement(['1.1', '2.36', '1.56', '1.7']),
                'banner'        => $faker->randomElement([
                    '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00',
                    'Synology&nbsp;RackStation',
                ]),
                'service'       => $faker->randomElement(['blk-availability', 'oddjobd', 'systemd-journald', 'autofs']),
                'label'         => $faker->randomElement(['远程管理', 'WEB服务器', '数据库服务器']),
                'time_used'     => $faker->randomElement([1, 2, 3, 4, 5]),
            ];
        }

        $max2 = rand(1, 5);

        for ($i = 1; $i <= $max2; $i++) {
            $webs[] = [
                'port'   => $faker->randomElement($p_arr),
                'title'  => $faker->title(),
                'header' => '200 OK',
                'body'   => $faker->sentences(),
                'host'   => $faker->domainWord,
                'domain' => $faker->domainName,
            ];
        }

        if (!$ports) {
            continue;
        }

        $a = [
            'task_id'      => rand(1, 8),
            'task_info_id' => rand(1, 3),
            'task_no'      => $faker->uuid,
            'org_id'       => rand(1, 3),
            'user_id'      => rand(1, 3),
            'probe_id'     => rand(1, 3),
            'ip'           => $faker->ipv4,
            'status'       => '1',
            'time_used'    => rand(1, 3),
            'location'     => $faker->city,
            'ports'        => $ports,
            'port_status'  => '1',
            'ip_status'    => '1',
            'has_banner'   => '1',
            'os'           => 'Linux',
            'web'          => $webs,
            'factory'      => $faker->randomElement(['cisco', 'redhat', 'huawei']),
            'route'        => '1',
            'created_at'   => $faker->date('Y-m-d H:i:s'),
            'updated_at'   => $faker->date('Y-m-d H:i:s'),
        ];

        $insert = [
            'index' => \App\Models\ES\Asset::INDEX,
            'type'  => \App\Models\ES\Asset::TYPE,
            'body'  => $a,
        ];

        \App\Service\ES::Client()->index($insert);

    }

全部字段可fill


$guarded = [];

多字段模糊搜

这里的%添加到绑定参数中，而不是写在语句内，要保证concat的参数干净，不要有`符号。

$model->whereRaw("concat({$table_fields}) like ?", ["%{$q['value']}%"]);

left join 多字段

$model->leftJoin($table_name, function ($join) use ($host_table, $table_name) {
    $join->on("{$host_table}.ip", '=', "{$table_name}.ip")
        ->on("{$host_table}.region", '=', "{$table_name}.region");
});

scope


public function scopeExpired($query)
{
    return $query->where("user_type", self::USER_TYPE_USER)->where("end_time", "<=", Carbon::now()->toDateTimeString());
}

Users::expired()->get()->pluck("user_id");

DB insert 批量插入


if (count($insert_data) >= 1000) {
    DB::connection()->table(self::TARGET_TABLE)->insert($insert_data);
}

》 laravel 源码注释中讲明是批量插入

/**
 * Insert a new record into the database.
 *
 * @param  array  $values
 * @return bool
 */
public function insert(array $values)
{
    // Since every insert gets treated like a batch insert, we will make sure the
    // bindings are structured in a way that is convenient when building these
    // inserts statements by verifying these elements are actually an array.
}

Cache

获取和存储有时你可能想从缓存中获取一个数据，而当请求的缓存项不存在时，程序能为你存储一个默认值。例如，你可能想从缓存中获取所有用户，当缓存中不存在这些用户时，程序将从数据库将这些用户取出并放入缓存。你可以使用 Cache::remember 方法来实现：


Cache::remember('web_category', '秒' ,function () {
    $query = ES::connection(OperatedWeb::CONN)->raw()->search($query_params);
    $list  = array_get($query, 'aggregations.web_category.buckets');
    $list  = array_column($list, 'key');
    sort($list);
    return json_encode($list);
});

Storage

先选定配置的磁盘再进行操作，否则操作无效


》 config/filesystem.php
'tmp' => [
        'driver'     => 'local',
        'root'       => storage_path('tmp'),
],

》 运用
$files = Storage::disk('tmp')->files('es_index_search_slowlog');

DB事务


public function update($update_condition, $data)
{
    try {
        DB::beginTransaction();
        //do something
        DB::commit();
        return $this->success();
    } catch (Exception $e) {
        DB::rollBack();
        return $this->error($e->getMessage());
    }
}

时间

取代获取当前时间date(Y-m-d H:i:s) 采用 now()->toDateTimeString();

ORM

给定的键作为集合的键

$cates = Cates::query()->get()->keyBy('cate_id')->toArray();

$ret = $user
        ->select(['user_id', 'status']) // 参数可以是字符串 或 数组
        ->selectRaw('user_name as uname') // select 的字段表达式
        ->selectSub('user_id', 'id') // as
        ->addSelect(['status','password']) //参数可以是字符串 或 数组
        ->from('channels')
        ->join('users', 'user_id', '<', 'user_id')
        ->joinWhere('orders', 'user_id', '<', '4')
        ->leftJoin('refunds', 'user_id', '<', 'user_id')
        ->leftJoinWhere('goods', 'goods_id', '>', 'goods_id')
        ->where(['user_id' => 4])
        ->orWhere('status', '=', 1)
        ->whereColumn('pass', '=', 'xxx')  //比较两列的大小
        ->whereRaw('user_id in (select user_id from channels)')
        ->whereIn('user_id',[1,2,3,4])
        ->orWhereIn('user_id',[1,2,3,4])
        ->whereNotIn('user_id', [5,6,7])
        ->orWhereNotIn('user_id', [5,6,7])
        ->whereNull('user_id', 'or', true)
        ->orWhereNull('status')
        ->whereBetween('created_at', ['2017-01-01', '2017-08-08'])
        ->orWhereBetween('created_at', ['2017-01-01', '2017-08-08'])
        ->whereNotBetween('created_at', ['2017-01-01', '2017-08-08'])
        ->whereExists(function ($query) {
            $query->select('user_id')
                ->from('orders')
                ->whereRaw('orders.user_id = users.id');
        })
        ->groupBy('user_id','status')
        ->orderBy('user_id')
        ->orderByDesc('status')
        ->orderByRaw("DATE_FORMAT(o.create_time,'%Y-%m-%d')")
        ->offset(4)
        ->limit(5)
        ->forPage(3, 10)

        //->mergeWheres(['user_name', 'status'], [])

        ->distinct() //添加 distinct
        ->toSql();
    dd($ret);

    select distinct `user_id`, `status`, user_name as uname, (user_id) as `id`, `status`, `password` from `channels` inner join `users` on `user_id` < `user_id` inner join `orders` on `user_id` < ? left join `refunds` on `user_id` < `user_id` left join `goods` on `goods_id` > ? where (`user_id` = ?) or `status` = ? and `pass` = `xxx` and user_id in (select user_id from channels) and `user_id` in (?, ?, ?, ?) or `user_id` in (?, ?, ?, ?) and `user_id` not in (?, ?, ?) or `user_id` not in (?, ?, ?) or `user_id` is not null or `status` is null and `created_at` between ? and ? or `created_at` between ? and ? and `created_at` not between ? and ? and exists (select `user_id` from `orders` where orders.user_id = users.id) group by `user_id`, `status` order by `user_id` asc, `status` desc, DATE_FORMAT(o.create_time,'%Y-%m-%d') limit 10 offset 20

Event

修改 EventServiceProvider
php artisan event:generate 产生 Event 和 Listener
event(xxx) 触发事件
Events 传递数据， Listeners 处理数据
如是异步开启监听 php artisan queue:work redis --queue=ip_domain_groups

代码片段如下：

》Provider：EventServiceProvider
protected $listen = [
    'App\Events\IpDomainGroup' => [
        'App\Listeners\IpDomainGroupProcessor',
    ],
];

》Events：IpDomainGroup
public function __construct($data, $ipDomainData, $type)
{
    $this->ipDomainData = $ipDomainData;
    $this->data         = $data;
    $this->type         = $type;
}

》Listeners：IpDomainGroupProcessor
public function handle(IpDomainGroup $event)
{
    try {
        $ipDomain = $event->ipDomainData;
    }catch (\Exception $exception) {
        logger()->error('IpDomainGroupProcessor', [$exception->getMessage(), $exception->getLine(), $exception->getFile()]);
    }
}

》触发事件
event(new \App\Events\IpDomainGroup($data, $ipDomain, 'insert'))

》监听异步事件
php artisan queue:work redis --queue=ip_domain_groups

目录