mysql - 如何从一张表中获取两个外键值

Question

我有两个表:

用户

table.increments('user_id').primary()
table.string('fname').notNull()
table.string('mname').notNull()
table.string('lname').notNull()
table.integer('level_id').notNull()
table.string('username').notNull()
table.string('password').notNull()
table.boolean('is_deleted').defaultTo(false)
table.unique('username')

消息

table.increments('message_id').primary()
table.integer('sender_id')
table.integer('receiver_id')
table.boolean('is_private').defaultTo(true)
table.text('message')
table.boolean('is_deleted').defaultTo(false)

所以现在的问题是，我想加入表并获取接收者和发送者的名称。

首先，我尝试仅获取消息并循环数据以收集接收者和发送者的姓名，因此另一个查询非常糟糕，所以我想在单个查询中获取所有内容。

我在JOIN上尝试了AND，但结果却是空的。

SELECT * 
FROM messages m 
JOIN users u ON u.user_id = m.sender_id 
    AND u.user_id = m.receiver_id

说实话，我只是在兜圈子，我真的不知道这样的查询背后的逻辑。

预期代码:

[
   {  
      "message_id":54,
      "sender_name": 'Ernie Jeash Vill',
      "receiver_name": 'Karma Morningstar Blackshaw',
      "is_private":1,
      "message":"q",
      "is_deleted":0,
      "created_at":"2019-09-03T08:45:09.000Z",
      "updated_at":null,
   },
   {  
      "message_id":53,
      "sender_name":'Karma Morningstar Blackshaw',
      "receiver_name": 'Ernie Jeash Vill',
      "is_private":1,
      "message":"e",
      "is_deleted":0,
      "created_at":"2019-09-03T08:45:09.000Z",
      "updated_at":null,
   }
]

Answer 1

您需要将消息加入到用户两次，每个接收者和发送者的名称:

SELECT
    m.*,
    u1.lname AS sender_last,
    u1.fname AS sender_first,
    u2.lname AS receiver_last,
    u2.fname AS receiver_first
FROM messages m 
INNER JOIN users u1
    ON u1.user_id = m.sender_id
INNER JOIN users u2
    ON u2.user_id = m.receiver_id;