myquery.sql

select usernames.* from (
      select u.username, u.id, p.first_name, p.last_name, p.avatar, p.political_affiliation
        from users u 
        join profiles p on p.user_id = u.id
        join messages mf on mf.from_profile_id = p.id
        join messages mt on mt.to_profile_id = p.id
        where 
          mf.from_profile_id = (select p.id from users u
              join profiles p ON u.id = p.user_id
              join tokens t ON t.user_id = u.id
              where t.token = ?) or
          mf.to_profile_id = (select p.id from users u
              join profiles p ON u.id = p.user_id
              join tokens t ON t.user_id = u.id
              where t.token = ?) or
          mt.from_profile_id = (select p.id from users u
              join profiles p ON u.id = p.user_id
              join tokens t ON t.user_id = u.id
              where t.token = ?) or
          mt.to_profile_id = (select p.id from users u
              join profiles p ON u.id = p.user_id
              join tokens t ON t.user_id = u.id
              where t.token = ?)
        group by u.id
      ) as usernames
      join users on users.id = usernames.id
      left join tokens on tokens.user_id = users.id
      where tokens.token != ? or ISNULL(tokens.token)

set @token = '7678a29f-b124-4a42-af91-5ce3e0d2918f';
#set @token = 'asdf';




SELECT distinct
    u.username, u.id, p.first_name, p.last_name, p.avatar, p.political_affiliation
FROM
    (SELECT 
        m.id, m.from_profile_id AS 'profileid'
    FROM
        messages m UNION SELECT 
        m.id, m.to_profile_id AS 'profileid'
    FROM
        messages m) AS m
        join users u on m.profileid = u.id
        join profiles p on p.user_Id = u.id
        join tokens t on m.profileid != t.user_id and t.token = @token
WHERE
    m.id IN (SELECT 
            parties.id AS messageid
        FROM
            (SELECT 
                m.*, p.user_Id AS 'fromID', NULL AS 'toID'
            FROM
                messages m
            LEFT JOIN profiles p ON m.from_profile_id = p.id UNION (SELECT 
                m.*, NULL AS 'fromID', p.user_Id AS 'toID'
            FROM
                messages m
            LEFT JOIN profiles p ON m.to_profile_id = p.id)) AS parties
                JOIN
            users u ON parties.fromID = u.id
                OR parties.toID = u.id
                JOIN
            tokens t ON t.user_id = u.id
        WHERE
            t.token = @token)