Comparison of two tables with a name in where condition in laravel

I am super bad with this stuff but taking a swing...

FeePayment::where('student_id', 5)
    ->andWhere('feestructure_id', 1)
    ->sum('Amount');

FeePayment::where('student_id', 5)
    ->andWhere('feestructure_id', 2)
    ->sum('Amount');

FeePayment::where('student_id', 5)
    ->andWhere('feestructure_id', 3)
    ->sum('Amount');

If you're looking for the amount left, just have to grab the FeeStructure amounts and subtract these values from that I would think.

Assume you have this model:

class FeePayment extends Model {
    public function feeStructure()
    {
        return $this->belongsTo(FeeStructure::class, 'feestrcutre_id');
    }

    public function student()
    {
        return $this->belongsTo(Student::class);
    }
}

In your route you could have this query:

$router->get('balance', function () {
    $records = FeePayment::query()
        ->with(['feeStructure'])
        ->selectRaw('student_id, feestrcutre_id, SUM(Amount) as paid')
        ->groupBy([ 'student_id', 'feestrcutre_id'])
        ->get()
        ->groupBy('student_id'); // this will group your results

    return view('balance', compact('records'));
});

Then in your view:

@foreach($records as $studentId => $payments)
    <h2>Student ID: {{ $studentId }}</h2>

    <table>
        <thead>
            <tr>
                <th>Term</th>
                <th>Total</th>
                <th>Paid</th>
                <th>Balance</th>
            </tr>
        </thead>
        <tbody>
            @foreach($payments as $payment)
                <tr>
                    <td>{{ $payment->feeStructure->name }}</td>
                    <td>{{ $payment->feeStructure->Amount }}</td>
                    <td>{{ $payment->paid }}</td>
                    <td>
                        {{ $payment->feeStructure->Amount - $balance->paid }}
                    </td>
                </tr>
            @endforeach
        </tbody>
    </table>
@endforeach

Note: I used feestrcutre_id as you typed in your code sample, maybe there is a typo in that

EDIT After re-reading your description, I made some changes to show the missing balance

@abdulbazith I made some edits on my previous post to better adjust my solution to your question

@rodrigo.pedra thank you thank you so much for your response.

i tried your code just editing like this, in my controller.

  $records = FeePayment::query()
                ->with(['feeStructure'])
                ->selectRaw('student_id, feeStructure, SUM(paid_amt + disc_amt - fine_amt) as paid')->where('student_id',74)
                ->groupBy([ 'student_id', 'fee_main_cat_id'])
                ->get()
                ->groupBy('student_id');

Everthing worked fine. i added disc_amount with that, because i have a column in my payment table. and i need a where condition for specifically mention the student so i added that also.

but i have a small problem, if student_id: 5 didnt pay any fee means it should return all the terms fees from feeStrucutre table?? and if a student paid only term1 fee, then the remaining terms fee and its amount should be displayed. so that i will have a link and i will click and pay that in the same payment table.

this is what the output when student:5 doesnt pay any fee means,

Term1 Balance 6000
Term2 Balance 8000
Term3 Balance 10000

Actually this shows the whole,  term and fee from feestrucutre table because this student didnt pay any fee. so i have a link by click the amt and it will redirect to payment page.

if the student paid only term1 partially that is , term1 fee is 6000, but he paid only 4000 means the output should be,

Term1 Balance 2000
Term2 Balance 8000
Term3 Balance 10000

How this comes is for term1 he paid 4000 so its balance is shown and remaining term2 and term3 he not paid a single amount, so it shown fully.

as per your query everything works fine, but it shows only when student pays the fee. because the query projects from FeePayment Table.

can you guide me to achieve my output. thank you.

i think i didnt confuse you.

Hmmm, In this case we should start from the FeeStructure and then fetch the students

Assuming this FeeStructure model ( FeePayment stays the same as previous post)

class FeeStructure extends Model
{
    public function feePaymentFromStudent()
    {
        // should filter student
        return $this->hasOne(FeePayment::class, 'feestrcutre_id')
            ->withDefault();
    }
}

Changing the controller query:

$router->get('balance/{id}', function ($id) {
    $fees = FeeStructure::query()
        ->with([
            'feePaymentFromStudent' => function ($query) use ($id) {
                $query
                    ->selectRaw('student_id, feestrcutre_id, SUM(paid_amt+disc_amt-fine_amt) as paid')
                    ->where('student_id', $id)
                    ->groupBy(['student_id', 'feestrcutre_id']);
            },
        ])
        ->get();

    return view('balance', [
        'fees' => $fees,
        'studentId' => $id,
    ]);
});

And the view:

<h2>Student ID: {{ $studentId }}</h2>

<table><table>
    <thead>
        <tr>
            <th>Term</th>
            <th>Total</th>
            <th>Paid</th>
            <th>Balance</th>
        </tr>
    </thead>
    <tbody>
        @foreach($fees as $fee)
            <tr>
                <td>{{ $fee->name }}</td>
                <td>{{ $fee->Amount }}</td>
                <td>{{ $fee->feePaymentFromStudent->paid ?? 0 }}</td>
                <td>
                    {{ $fee->Amount - ($fee->feePaymentFromStudent->paid ?? 0) }}
                </td>
            </tr>
        @endforeach
    </tbody>
</table>

Notice in this approach we are returning one student per time.

@rodrigo.pedra thank you thank you so much. it worked fine. i made small changes with if condition in my view and thats it.

thank youuu..

if possible kindly suggest your answer for this please

Link: https://laracasts.com/discuss/channels/laravel/generate-serial-number-with-current-year-and-last-id-automatically-laravel