加入两个表以列出用户的所有会话

Question

I've four tables:

chat (users to users)

id | id_user_from | id_user_to
1  | 1            | 2
2  | 1            | 3
3  | 3            | 2
4  | 4            | 1

message (users to users)

id | content | date                | id_chat | id_user_from | id_user_to
1  | hi      | 2017-10-04 23:14:41 | 1       | 1            | 2
2  | hello   | 2017-10-04 23:15:03 | 1       | 2            | 1
3  | heey    | 2017-10-04 23:40:00 | 3       | 3            | 2
4  | ops     | 2018-01-04 20:00:00 | 4       | 4            | 1

page_chat (users to pages / pages to user)

id | id_user | id_page
1  | 1       | 1
2  | 1       | 3
3  | 4       | 3
4  | 4       | 2

page_message (users to pages / pages to user)

id | content | date                | id_page_chat | id_user | id_page | from (0 = user; 1 = page)
1  | from pg | 2017-07-04 23:14:41 | 1            | 1       | 1       | 1
2  | from usr| 2018-10-04 23:15:03 | 2            | 1       | 3       | 0
3  | to usr  | 2018-10-04 23:40:00 | 2            | 1       | 3       | 1
4  | hi page | 2018-10-04 23:40:00 | 3            | 4       | 3       | 0

And I'm using the following code to get last message of each conversation:

(users to users) ($userId is the user logged id; $idsChat are id_chat's that already loaded - using because of infinite scrolling):

select m1.*
from message m1
join
(
  SELECT MAX(id) as id
  FROM message
  WHERE $userId IN (id_user_from, id_user_to) AND id_chat NOT IN (".implode(",", $idsChat).")
  GROUP BY id_chat
  ORDER BY id DESC
) m2 on m1.id = m2.id

(users to pages / pages to user) ($userId is the user logged id; $idsChat are id_page_chat's that already loaded - using because of infinite scrolling):

select m1.*
from page_message m1
join
(
  SELECT MAX(id) as id
  FROM page_message
  WHERE id_user = $userId AND id_page_chat NOT IN (".implode(",", $idsChat).")
  GROUP BY id_page_chat
  ORDER BY id DESC
) m2 on m1.id = m2.id

And they work perfectly. But now I want to join (I don't know if by PHP or SQL itself) these two queries, to display to user your conversations with users and pages together. How can I do this?

Answer 1

So first, you combine your messages into one query:

select `content`, `date`, id as m_id, id_chat, id_user_from, id_user_to,
  null as p_id, null as id_page_chat, null as id_user, null as id_page, null as `from`
from message
union all
select `content`, `date`, null as m_id, null as id_chat, null as id_user_from, null as id_user_to,
  id as p_id, id_page_chat, id_user, id_page, `from`
from page_message

Then you can use that as the base table for your query to look up the most recent message from each conversation. (This code would be much cleaner if you used the above query to create a view, and referenced that instead.)

select *
from (select `content`, `date`, id as m_id, id_chat, id_user_from, id_user_to,
      null as p_id, null as id_page_chat, null as id_user, null as id_page, null as `from`
    from message
    union all
    select `content`, `date`, null as m_id, null as id_chat, null as id_user_from, null as id_user_to,
      id as p_id, id_page_chat, id_user, id_page, `from`
    from page_message) m1
join
(select max(m_id) as m_id, max(p_id) as p_id
   from (select `content`, `date`, id as m_id, id_chat, id_user_from, id_user_to,
          null as p_id, null as id_page_chat, null as id_user, null as id_page, null as `from`
        from message
        union all
        select `content`, `date`, null as m_id, null as id_chat, null as id_user_from, null as id_user_to,
          id as p_id, id_page_chat, id_user, id_page, `from`
        from page_message) all_msgs
   where $userId IN (id_user_from, id_user_to, id_user) 
     AND IFNULL(id_chat, id_page_chat) NOT IN (".implode(",", $idsChat).")
   group by id_chat, id_page_chat
   order by m_id desc, p_id desc
) m2 on (m1.m_id = m2.m_id or m1.p_id = m2.p_id)

sqlfiddle