0

This is my query builder function :

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;

        }

But it show an error Column not found: 1054 Unknown column '$year' in 'where clause'
Can someone help me to figure out my problem ?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Ho Wei Kang
  • 101
  • 2
  • 10
  • `'year(sold_date) = $year'` - That will literally translate as that. If you want to use the _value_ of `$year`, it must be inside double quotes. The same goes with `'$dev'`. That will only match developers with the literal name `$dev`. There you can remove the quotes completely though, since you send it in as a separate argument. – M. Eriksson Apr 26 '18 at 08:20
  • Also, be sure to do a `$year = (int)$year;` first (casting the value as an integer to prevent possible SQL injections). – M. Eriksson Apr 26 '18 at 08:23
  • @MagnusEriksson Thanks for the suggestion. – Ho Wei Kang Apr 26 '18 at 08:32

1 Answers1

0

Sometimes you may need to use a raw expression in a query. To create a raw expression, you may use the DB::raw method:

in your case, '$year' PHP think that '$year' is a string character, not a variable and whereRaw meaning use raw expression. This is the reason for your error

$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();

EDIT:

    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);

notice that the third parameter is $operator, so you should do something like this 

join('pams_developer', 'pams_project.dev_id ', '=', 'pams_developer.id')
qskane
  • 481
  • 4
  • 16