CODEWITHSUNDEEP
phpmysqllaravel
muasx
muasx

Reputation: 29

laravel eloquent complex select inside where statement

halo, i have data and want to display it like picture below

enter image description here

there are two models relationship, Person and Installment.

this is Person model:

class Person extends Model
{
    protected $table = 'person';
    public function angsuran()
    {
        return $this->hasMany(Installment::class);
    }
}

this is Installment model:

class Installment extends Model
{
    protected $table = 'installment';
    public function person()
    {
        return $this->belongsTo(Person::class);
    }
}

and this is my controller to querying and display data

$data = Person::with('angsuran')
        ->whereHas('angsuran', function ($q) {
             $q->whereBetween('installment_date', [\DB::raw('CURDATE()'), \DB::raw('CURDATE() + INTERVAL 7 DAY')])
               ->where('installment_date', '=', function () use ($q) {
                  $q->select('installment_date')
                    ->where('status', 'UNPAID')
                    ->orderBy('installment_date', 'ASC')
                    ->first();
                });
            });
return $data->get();

it show error unknow colum person.id in where clause please help. thanks.

Upvotes: 0

Views: 999

Answers (2)

Aslam H
Aslam H

Reputation: 1801

Using with and whereHas you will end up with two query even if you have limit(1) in your subQuery and the result will show all 4 installment related to the person model. also I don't think you can order on the subquery, it should be before the ->get

so here's i've rewritten your code

$callback = function($query) {
    $query->whereBetween('installment_date', [today(), today()->addDays(7)])
            ->where('status', 'UNPAID')
            ->orderBy('installment_date');
    };

$data = Person::whereHas('angsuran', $callback)->with(['angsuran' => $callback])->get();

or you can use query scope. please see this answer Merge 'with' and 'whereHas' in Laravel 5

Upvotes: 1

IGP
IGP

Reputation: 15786

As the comment said, you need to put $q as a parameter to the Closure.

When using subqueries, it's useful to tell the query builder which table it is supposed to query from.

I've rewritten your query. It should achieve what you're looking for. Also, changed the CURDATE to Carbon objects.

today() returns a datetime to today at 00:00:00 hours. If you need the hours, minutes and seconds, replace today() by now().

$data = Person::with('angsuran')
->whereHas('angsuran', function ($subquery1) {
    $subquery1->where('installment_date', function ($subquery2) {
        $subquery2->from('installment')
        ->select('created_at')
        ->where('status', 'UNPAID')
        ->whereBetween('installment_date', [today(), today()->addWeeks(1)])
        ->orderBy('installment_date')
        ->limit(1);
    });
});

Upvotes: 1

Related Questions