CODEWITHSUNDEEP
phplaravellaravel-5eloquentrelationship
MegaBeetle
MegaBeetle

Reputation: 15

How to use Eloquent to retrieve data from two different tables

I'm setting up a system to check if the user has sent a message to another user.

MessagesController.php

use App\Message;
use App\User;

class MessagesController extends Controller
{
    public function index()
    {
        return view('admin.messages')->with('messages', Message::all())
                                     ->with('users', User::all());

    }

The message migration has following data:

Schema::create('messages', function (Blueprint $table) {
    $table->increments('id');
            $table->integer('from');
            $table->integer('to');
            $table->mediumText('text');
            $table->integer('status');
            $table->timestamps();
});

And the web.php

Route::get('/admin/messages', [
        'uses' => 'MessagesController@index',
        'as' => 'messages'
    ]);

So the idea is to present a panel with the data of messages, showing:

<tbody>
    <tr>
        @foreach ($messages as $message)

        <td>
            {{ $message->from}}
        </td>
        <td>
            {{ $message->to}}
        </td>
        <td>
            {{ $message->text}}
        </td>
        <td>
            {{ $message->status}}
        </td>
        <td>
            {{ $message->created_at}}
        </td>

        @endforeach
    </tr>
</tbody>

This loads correctly all the information on the Message table. However, it will display the 'from' and 'to' as and ID, as it should.

I expect to have the table populated with not the ID of the user, but the Name of the user, via a relationship between the Message table and the Users table.

What am I missing?

Upvotes: 1

Views: 72

Answers (2)

Kenny Horna
Kenny Horna

Reputation: 14271

First of all, you need to define your relationships. It looks like a User has many Messages, so:

Message.php

public function sender()
{
    return $this->belongsTo(User::class, 'from');
}

public function receiver()
{
    return $this->belongsTo(User::class, 'to');
}

Note: Here I'm using two belongsTo relationships with a custom foreign key, this is because Laravel will look for the {model}_id column by default, but this way Laravel will know the column to search for.

Then in your controller you can return your messages but Eager Loading the relationships that you need in order to be able to show them in your view:

use App\Message;
use App\User;

class MessagesController extends Controller
{
    public function index()
    {
        $messages = Message::with(['sender', 'receiver'])->get();

        return view('admin.messages')->with('messages', $messages);

    }
}

So then in your view:

<tbody>
<tr>
    @foreach ($messages as $message)
        <td>
            {{ $message->sender->name}}
        </td>
        <td>
            {{ $message->receiver->name}}
        </td>

        // ...

    @endforeach
</tr>
</tbody>

Observation

Given that primmary keys are positive integers, update your foreign key types to this:

Schema::create('messages', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('from'); // <-----
        $table->unsignedInteger('to');  // <------
        $table->mediumText('text');
        $table->integer('status');
        $table->timestamps();
    });

This will help you avoid some trouble in the future. Also, from Laravel 5.8 the primary keys are Big Increments by default. So in case you use those, your foreign keys should be like this: $table->unsignedBigIncrement('column_name');

Upvotes: 1

mrhn
mrhn

Reputation: 18976

In your message model you will have to define relations. We define from and to relations, these will need an extra parameter to define what the key name is, as it is not part of the Laravel standard you are following.

public class Message
{
    public function from()
    {
        return $this->belongsTo(User::class, 'from');
    }

    public function to()
    {
        return $this->belongsTo(User::class, 'to');
    }
}

With this done, you will be able to do something similar like this.

public function index()
{
    return view('admin.messages')->with('messages', Message::all())
}

If access the user like this $message->from, you will get an object. If you access the method, you will get the query builder, like so $message->from().

Now you can utilise it in the blade template.

<tbody>
  <tr>
    @foreach ($messages as $message)
      <td>
        {{ $message->from->name}}
      </td>
      <td>
        {{ $message->to->name}}
      </td>

      ....
    @endforeach
  </tr>
</tbody>

Upvotes: 1

Related Questions