laravel笔记+数据库操作

399 阅读2分钟

数据库操作

更新:

DB::table('contacts')
    ->where('id', 1)
    ->update(['meta->wants_newsletter' => false]);

查询:

$query = Goods::where([
    'business_no' => $this->business_no,
    'is_delete'   => 0,
]);// 关键词查询
if ($request->filled('keyword')) {
    $query->where(function ($query) use ($request) {
        $query->orWhere('goods_name', 'like', '%' . $request->keyword . '%')
            ->orWhere('goods_model_no', 'like', '%' . $request->keyword . '%')
            ->orWhere('goods_sn','like','%' . $request->keyword . '%');
    });
}

SQL语句

// 分组查询
$store_arr = $query
    ->select(DB::raw('count(*) as user_count, sum(order_fact_money) as amount, store_id, order_fact_money'))
    ->groupBy('store_id')
    ->get()->toArray();
SELECT 
SUM(CASE WHEN MONTH(s.CreateTime) = 1 THEN s.Amount ELSE 0 END) AS '一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 2 THEN s.Amount ELSE 0 END) AS '二月',
SUM(CASE WHEN MONTH(s.CreateTime) = 3 THEN s.Amount ELSE 0 END) AS '三月',
SUM(CASE WHEN MONTH(s.CreateTime) = 4 THEN s.Amount ELSE 0 END) AS '四月',
SUM(CASE WHEN MONTH(s.CreateTime) = 5 THEN s.Amount ELSE 0 END) AS '五月',
SUM(CASE WHEN MONTH(s.CreateTime) = 6 THEN s.Amount ELSE 0 END) AS '六月',
SUM(CASE WHEN MONTH(s.CreateTime) = 7 THEN s.Amount ELSE 0 END) AS '七月',
SUM(CASE WHEN MONTH(s.CreateTime) = 8 THEN s.Amount ELSE 0 END) AS '八月',
SUM(CASE WHEN MONTH(s.CreateTime) = 9 THEN s.Amount ELSE 0 END) AS '九月',
SUM(CASE WHEN MONTH(s.CreateTime) = 10 THEN s.Amount ELSE 0 END) AS '十月',
SUM(CASE WHEN MONTH(s.CreateTime) = 11 THEN s.Amount ELSE 0 END) AS '十一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 12 THEN s.Amount ELSE 0 END) AS '十二月'
FROM Orders AS s
WHERE YEAR(s.CreateTime) = 2014

select sum(case when create_time between 132324324 and 123221324 then order_money else 0) as '1月'
    /**
     * 计算时间区间
     * @param string $unit
     * @return array
     */
    private function computeTimeRange($unit = 'day')
    {
        if ($unit == 'month') Carbon::useMonthsOverflow(false);
        $now = Carbon::now();

        $func       = ucfirst($unit);
        $sub_func   = 'sub' . $func;
        $start_func = 'startOf' . $func;
        $end_func   = 'endOf' . $func;

//        $range = [$now->$start_func()->toDateString() => [$now->$start_func()->timestamp, $now->$end_func()->timestamp]];
        $range = [$now->$start_func()->toDateString() => [$now->$start_func()->toDateTimeString(), $now->$end_func()->toDateTimeString()]];
        for ($i = 0; $i < 17; $i++) {
            $day = $now->$sub_func();
//            $range[$day->$start_func()->toDateString()] = [$day->$start_func()->timestamp, $day->$end_func()->timestamp];
            $range[$day->$start_func()->toDateString()] = [$day->$start_func()->toDateTimeString(), $day->$end_func()->toDateTimeString()];
        }

        return $range;
    }
//调用
   public function getPerformChart(IdCountRequest $request){
        $time_unit  = $request->filled('time_unit') ? $request->time_unit : 'day';
        $time_range = $this->computeTimeRange($time_unit);

        $sql = [];
        $str = 'sum(case when {column_name} between "%s" and "%s" then order_fact_money else 0 end) as "%s"';

        foreach ($time_range as $key => $item) {
            $sql[] = sprintf($str, $item[0], $item[1], $key);
        }
        $sql = implode(',', $sql);

        // 总业绩
        $query = OrderOffline::query()->where(['business_no' => $this->business_no, 'is_delete' => 0]);
//        $query->where('is_followed', '>', 0);
        if ($request->filled('store_id')) {
            $query->where('store_id', $request->store_id);
        }
        $income = $query->select(DB::raw(str_replace('{column_name}', 'order_time', $sql)))->first();

        return $income->toArray();
    }

关联操作

Eloquent 假设外键应该在父级上有一个与之匹配的 id(或者自定义 $primaryKey),换句话说,Eloquent 将会通过 user 表的 id 值去 phone 表中查询 user_id 与之匹配的 Phone 记录。如果你想要关联关系使用其他值而不是 id,可以传递第三个参数到hasOne 来指定自定义的主键:

return $this->hasOne('App\Phone', 'foreign_key', 'local_key');
return $this->hasOne('App\Phone', 'user_id', 'id');    

return $this->belongsTo(Store::class, 'foreign_key', 'owner_key');
return $this->belongsTo(Store::class, 'store_id', 'id');

关联关系:hasOne,hasMany

反向关联:belongsTo,belongsToMany

处理数据方法

// 数组转XML
public function arrayToXml($arr){
   $xml = '<xml>';      //  $xml = '&lt'.'xml'.'&gt';
   foreach ($arr as $key=>$val){
      $xml=$xml."&lt".$key."&gt".$val."&lt/".$key."&gt"; 
      //$xml=$xml."<".$key.">".$val."</".$key.">";
   }
   $xml=$xml.'&lt'.'/xml'.'&gt';   // $xml= $xml.'</xml>';
   return $xml;
}
// 使用curl方法调用微信接口
$url = "https://api.mch.weixin.qq.com/pay/unifiedorder";
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
//设置头文件的信息作为数据流输出
curl_setopt($curl, CURLOPT_HEADER, 1);
curl_setopt($curl, CURLOPT_HTTPHEADER, Array("Content-Type:text/xml; charset=utf-8"));    // 一定要定义content-type为xml,要不然默认是text/html!
//设置获取的信息以文件流的形式返回,而不是直接输出。
curl_setopt($curl, CURLOPT_RETURNTRANSFER,1);
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_POSTFIELDS, $xmlData);
$res = curl_exec($curl);
curl_close($curl);



数据库---索引

  • 索引是表示数据的另一种方式,它提供的数据顺序不同于数据在磁盘上的物理存储顺序
  • 索引的特殊是在表内重新排列记录的物理位置。
  • 索引可建立在数据表的一个或者多个列上,或建立在表的几列组合上。每个索引都会被起个名字
  • 索引被创建于已有表中,可以使对行的定位更快速更有效。用户无法看到索引,它们只能被用来加速查询
  • 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是因为索引本身也需要更新。因此,理想的做法是仅仅在常常用于搜索的列上面建立索引


PHP基础

array_get() :将数组以点形式连接

/**
 * Get an item from an array using "dot" notation.
 *
 * @param  \ArrayAccess|array  $array
 * @param  string  $key
 * @param  mixed   $default
 * @return mixed
 */
function array_get($array, $key, $default = null)
{
    return Arr::get($array, $key, $default);
}

array_uniqe() :数组去重

/**
 * Removes duplicate values from an array
 * @link http://php.net/manual/en/function.array-unique.php
 * @param array $array <p>
 * The input array.
 * </p>
 * @param int $sort_flags [optional] <p>
 * The optional second parameter sort_flags
 * may be used to modify the sorting behavior using these values:
 * </p>
 * <p>
 * Sorting type flags:
 * SORT_REGULAR - compare items normally
 * (don't change types)
 * @return array the filtered array.
 * @since 4.0.1
 * @since 5.0
 */
function array_unique(array $array, $sort_flags = null) { }

date('Y-m-d', $timestamp):将时间戳转换成日期格式

date('Y', $timestamp):获取时间戳的年份,对应m【月份】,day【日期几号】

$date = Carbon::parse('2018-10-05'):获取一个对应日期的Carbon对象

$date->daysInMonth  这个月多少天

$date->startOfMonth(): 这个月第一天

$date->endOfMonth(): 这个月最后一天