CODEWITHSUNDEEP
laravellaravel-5
user11124425
user11124425

Reputation: 971

How to include a column from another table in the query?

I have a table named Payments and another table named Students with the field name.

In my Controller Payment, I want to retrieve the student names.

I have this:

public function index(Request $request)
{
    $user = $request->user();
    $payments = Payment::query()
        ->when($user->hasRole('admin') !== true, function (Builder $query) use ($user) {
            $query->where('email', $user->email);
        })
        ->when($request->has('search'), function (Builder $query) use ($request) {
            $query->where('name', 'like', '%' . $request->input('search') . '%');
        })
        ->paginate(5);

    return view('admin.payments.index', compact('payments'))
        ->with('display_search', $user->hasRole('admin'));
}

How do I join the students table?

payment::whereHas('students'

I don't know where I have to put this line

Edit:

When I add a payment, I always have the same student which appears ???

enter image description here

I think the problem is here ?? In my request ??? 

$query->where('name', 'like', '%' . $request->input('search') . '%');
         })->with('students:id,name') // Here
        ->paginate(5);

Index blade

<th>Date payment</th>
<th>Number seance</th>
<th>Price</th>
<th>Total</th>
 <th>Name</th>

</tr>
</thead>
@foreach($payments as $payment)
<tr>
   <td> {{$payment->date_payment->format('d/m/Y') }}</td>
   <td> {{$payment->number_seance}}</td>
   <td> {{$payment->price}}</td>
   <td> {{$payment->total}}</td>
   <td> {{$payment->students->first()->name}}</td> 
   <td>

Thank you for your help.

Upvotes: 2

Views: 201

Answers (1)

Salim Djerbouh
Salim Djerbouh

Reputation: 11034

Eager load the name column from the relationship

$payments = Payment::query()
    ->when($user->hasRole('admin') !== true, function (Builder $query) use ($user) {
        $query->where('email', $user->email);
    })
    ->when($request->has('search'), function (Builder $query) use ($request) {
        $query->where('name', 'like', '%' . $request->input('search') . '%');
    })->with('students:id,name') // Here
    ->paginate(5);

Provided you have the relationship setup in the Payment model

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

And a payment_id column in the students table referencing the id in the payments table as a foreign key

Schema::create('students', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name');
    $table->unsignedBigInteger('payment_id')->nullable();
    $table->foreign('payment_id')->references('id')->on('payments');
    $table->timestamps();
});

Access a single student in the collection

<td>{{ $payment->students->first()->name }}</td>

Hope this helps

Upvotes: 1

Related Questions