时间戳

首页 > 解决方案 > 具有 4 个表的查询生成器

php - 具有 4 个表的查询生成器

问题描述

这是我的查询生成器功能：

public function MonthSoldGDV($dev,$year){

            $monthlyGDV = DB::table('pams_unit')
            ->join('pams_phase','pams_unit.phase_id','pams_phase.phase_id')
            ->join('pams_project','pams_phase.project_id','pams_project.project_id')
            ->join('pams_developer','pams_project.dev_id ','pams_developer.id')
            ->select('pams_developer.developer_name')
            ->selectRaw('year(pams_unit.sold_date) as year')
            ->selectRaw('month(pams_unit.sold_date) as month')
            ->selectRaw('sum(pams_unit.sold_price) as gdv')
            ->where('pams_developer.developer_name','$dev')
            ->whereRaw('year(sold_date) = $year')
            ->groupBy('month')
            ->get();

            return $monthlyGDV;

        }

但它显示一个错误Column not found: 1054 Unknown column '$year' in 'where clause'
有人可以帮我找出我的问题吗？

标签： phplaraveleloquent

解决方案

有时您可能需要在查询中使用原始表达式。要创建原始表达式，您可以使用 DB::raw 方法：

在您的情况下，'$year'PHP 认为这'$year'是一个string字符，而不是变量，并且whereRaw 含义使用原始表达式。这是你错误的原因

$monthlyGDV = DB::table('pams_unit')
    ->join('pams_phase', 'pams_unit.phase_id', '=', 'pams_phase.phase_id') //changed
    ->join('pams_project', 'pams_phase.project_id', '=', 'pams_project.project_id') //changed
    ->join('pams_developer', 'pams_project.dev_id ', '=', 'pams_developer.id') //changed
    ->select('pams_developer.developer_name')
    ->selectRaw('year(pams_unit.sold_date) as year')
    ->selectRaw('month(pams_unit.sold_date) as month')
    ->selectRaw('sum(pams_unit.sold_price) as gdv')
    ->where('pams_developer.developer_name', $dev) //changed
    ->where(DB::raw('year(sold_date)'), $year) //changed
    ->groupBy('month')
    ->get();

编辑：

    public function join($table, $one, $operator = null, $two = null, $type = 'inner', $where = false)
    {
        // If the first "column" of the join is really a Closure instance the developer
        // is trying to build a join with a complex "on" clause containing more than
        // one condition, so we'll add the join and call a Closure with the query.
        if ($one instanceof Closure) {
            $join = new JoinClause($type, $table);

注意第三个参数是$operator，所以你应该做这样的事情

join('pams_developer', 'pams_project.dev_id ', '=', 'pams_developer.id')

推荐阅读