Simple Query Problem

$residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) {
    $query->latest()->first();
}])->get();

Greeting Guide

What are you trying to do just get a list of current renters are renters who owe, or what.

SaeedPrez, Maybe im confused on the Syntax I am writing. when using ">with(['rent' " rent is my table name, correct?

Below is what DD gives me

        #relations: array:1 [▼

         "rent" => Collection {#162 ▼

          #items: []

        }

        ]

jlrdw , for each resident I am trying to pull their most recent update to the table by their id

@Mfrancik in ->with('rent') the rent is the name of the relationship.

Thank you for the clairifcation. This still applys, rent is the name of the relationship on my Resident Model

1. Is the query returning the residents who have currentResident set to true?
2. Do any of those residents actually have a related rent row in the database?

Both yes.

When I create a new resident I am setting the forign key immediately after, when I DD the rent table it shows "resident_id" => "["2"]" on the rent table. Could this be the issue?

$resident->save();

        $forgenKey = Resident::orderBy('created_at', 'DESC')->take(1);
        $forgenKey= $forgenKey->pluck('id');

        $rent = new Rent();

        $rent->balance = $amountDue;
        $rent->paid = null;
        $rent->currentBalance = $rent->balance;
        $rent->resident_id = $forgenKey;
$rent->save();

@Mfrancik try dd() this and see if it shows the relationships..

$residents = Resident::where('currentResident', 'true')->with('rent')->get();

It shows the relationship dropdown but no values in it

        #relations: array:1 [▼
        "rent" => Collection {#169 ▼
        #items: []
        }
        ]

@Mfrancik looks like your relationship is not working or there is no data in your database.

Can you show us the relationships of both models and also the migrations..

Resident Model:

public function rent()
        {
            return $this->hasMany('App\Rent');
        }

rent Migration:

    public function up()
    {
        Schema::create('rent', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('resident_id');
            $table->integer('balance')->nullable();;
            $table->integer('paid')->nullable();
            $table->integer('currentBalance')->nullable();;
            $table->timestamps();

            $table->foreign('resident_id')->references('id')->on('residents')->onDelete('cascade');

        });
    }

@Mfrancik I think I see the problem now....

        $resident->save();

        // $forgenKey = Resident::orderBy('created_at', 'DESC')->take(1);
        // $forgenKey= $forgenKey->pluck('id');

        $rent = new Rent();

        $rent->balance = $amountDue;
        $rent->paid = null;
        $rent->currentBalance = $rent->balance;
        // $rent->resident_id = $forgenKey;
        $resident->rent()->save($rent);

Or if you'd rather do it your way..

        // I'm assuming you have the $resident already here
        $resident->save();

        // So we can just get the Id
        $forgenKey = $resident->id;

        // $forgenKey = Resident::orderBy('created_at', 'DESC')->take(1);
        // $forgenKey= $forgenKey->pluck('id');

        // However, if you wanted to query the latest Resident, this is how
        // $forgenKey = Resident::latest()->first()->id;

        $rent = new Rent();

        $rent->balance = $amountDue;
        $rent->paid = null;
        $rent->currentBalance = $rent->balance;
        $rent->resident_id = $forgenKey;
        $rent->save();

I get an error because when I do that Integrity constraint violation: 19 NOT NULL constraint failed: rent.resident_id so I tried to work around and explicitly define the forgen key

@Mfrancik hm, what does your relationship look like in the Rent model?

@SaeedPrez I have set it up that way but now my query is only pulling the most recent programReview from one resident.

        $residents = Resident::where('currentResident', 'true')
                ->with(['rent' => function ($query) {
                $query->orderBy('created_at', 'DESC');
                $query->take(1);
            }])->get();

@Mfrancik so all the code I gave you, you're still using your own original code... why?

That code is not correct, use this code :/

I copy and pasted the wrong thing, ...

    $residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) {
        $query->latest()->first();
    }])->get();

I have set it up that way but now my query is only pulling the most recent programReview from one resident.

What is programReview? It's the first time I see it mentioned..

rent, I appologize, im moving too quickly.

Using the above provided code, It is working properly if I create one resident, it properly pulls that one residents rent. If I create a second resident, this will only pull the most recent Residents rent.

When I dd the rent table, I can see both residents have a value and the foreign keys are correct

Hm, that is really strange behavior...

@SaeedPrez $residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) { $query->first(); }])->get();

will pull only the most recent residents rent. so this query will only pull one record. Any suggestions on where to look to solve why this is happening?

It might be a bug in the framework, I don't know.. but you can always do this..

$residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) {
    $query->latest()->get();
}])->get();

This will get all rents for each resident, but you can only use the first one.

This is working for what I need to do. Thank you for your patience and help!

This should work too, and only return one result per Resident.

$residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) {
    $query->latest()->groupBy('resident_id')->get();
}])->get();

Yes!

Thank you Saeed!

@Mfrancik update your query to this, it should do less queries to the database even..

$residents = Resident::where('currentResident', 'true')->with(['rent' => function ($query) {
    $query->latest()->groupBy('resident_id');
}])->get();