php - 如何使用 Laravel 和 Eloquent 查询具有多种关系的数据透视表

Question

在 Laravel 5 中，我有一个具有以下架构的数据库:

| USERS    | JOBS  | CONVERSATIONS | MESSAGES        | CONVERSATION_USER |
| id       | id    | id            | id              | user_id           |
| username | title | job_id        | conversation_id | conversation_id   |
| password |       |               | user_id         |                   |
|          |       |               | message         |                   |

其中我的conversation_user表是数据透视表。

考试问题:我正在尝试使用conversation_user数据透视表来获取与登录用户相关的所有conversation_id，然后使用这些结果来获取与该 session 相关的最后一条消息以及工作标题对话涉及到。

用 SQL 术语来说，这相当于:

SELECT t1.message, t2.title FROM

(SELECT messages.`message`, messages.conversation_id FROM conversation_user
INNER JOIN messages ON conversation_user.conversation_id=messages.conversation_id
WHERE conversation_user.user_id=$user_id ORDER BY messages.id DESC) t1

INNER JOIN

(SELECT jobs.`title`, conversations.id FROM conversations INNER JOIN jobs ON
conversations.job_id=jobs.id) t2

ON t1.conversation_id=t2.id

但是我不知道如何用 Eloquent 来实现这一点!

我的关系如下:

User model:
$this->hasMany('job');
$this->hasMany('message');
$this->belongsToMany('conversation');

Job model:
$this->belongsTo('user');
$this->hasMany('conversation');
$this->hasManyThrough('message', 'conversation');

Conversation model:
$this->belongsTo('job');
$this->hasMany('message');
$this->belongsToMany('user');

Message model:
$this->belongsTo('user');
$this->belongsTo('conversation');

Answer 1

如果我理解正确的话，这就是你所需要的

    $user = User::find(Auth::id());
    foreach($user->conversations()->with('messages', 'job')->get() as $conversation)
    {
        echo $conversation->job->title;
        echo $conversation->messages->sortBy('id')->last()->message;
    }