Question 15738 in Laravel Question posted in
The official Laravel Documentation can be found here.

Left Join with models and condition in laravel9

How can I change this query [email protected]?

$query = "select * from nozzle a left join nozzlereading b on a.id = b.nozzle 
          and b.date=" . $date . " where a.meter=" . $meterid;
$reading = DB::select($query);

My models are Nozzle and Nozzlereading. But left join is not working properly.

I tried with code written below. But its not working properly.

$nozzleReadings = Nozzle::select('nozzle.id', 'nozzle.name', 'nozzle.meter', 'nozzlereading.date', 'nozzlereading.reading as myreading')
               ->leftJoin('nozzlereading', function ($join) use ($date, $meterid) {
                  $join->on('nozzle.id', '=', 'nozzlereading.nozzle')
                    ->where('nozzlereading.date', '=', $date)
                    ->where('nozzle.meter', '=', $meterid);
               })->get();

TIA

Tags:

2

Answers


  1. 0

    Please try this code.
    I hope it helps.

    $nozzleReadings = Nozzle::select('nozzle.id', 'nozzle.name', 'nozzlereading.date', 'nozzlereading.reading as myreading')
        ->leftJoin('nozzlereading', 'nozzlereading.nozzle', '=', 'nozzle.id')
        ->where('nozzlereading.date', '=', $date)
        ->where('nozzle.meter', '=', $meterid);
        ->get();
    Login or Signup to reply.
  2. 0

    You can try specifying the relationships to simplify the Eloquent model queries.

    // in Models/Nozzle.php
...
    public function nozzleReadings(): HasMany
    {
        return $this->hasMany(NozzleReading::class);
    }
// in Models/NozzleReading.php
...
    public function nozzle(): BelongsTo
    {
        return $this->belongsTo(Nozzle::class);
    }

// in the query
$nozzleReadings = NozzleReading::with('nozzle') //with performs eager loading
                    ->where('date', $date)
                    ->whereHas('nozzle', function ($query) {
                        $query->where('meter', $meterId);
                    })
                    ->get();

    The structure of the output will be slightly different so be aware of the different nested arrays.

    You can refer to https://laravel.com/docs/10.x/eloquent-relationships for more inspiration and guidance.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search