CODEWITHSUNDEEP
mysqlsql
user796443
user796443

Reputation:

mysql query - count words in common between users

I've a very nice query that selects friends of the current user. user_id = 2 in the example. His friend is user_id = 4.

I want the same query to fetch the number of words user_id 2 has with selected friends. In this case they have word = love, and this is also word they both have, so I want in_common row to say = 1.

Is it possible without changing too much current query?

Should I start from scratch?

SQL FIDDLE

Upvotes: 0

Views: 108

Answers (1)

Kickstart
Kickstart

Reputation: 21533

Assuming that both users would have an entry for 'love' in the words_en table then something like this maybe:-

SELECT b.name_surname,
     b.avatar, 
     b.friend_words, 
     (b.friend_msg_id) AS friend_msg_id, 
     words_common.words_in_common,
     COUNT(m.id) AS unread_msg 
FROM 
(
    SELECT a.name_surname as name_surname,
        a.avatar as avatar,
        GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS friend_words,
        (a.friend_id) AS friend_msg_id
    FROM 
    (
        SELECT f1.asked_user_id AS friend_id,
            f1.created,
            u.name_surname,
            u.avatar
        FROM friends AS f1 
        INNER JOIN friends AS f2 
        ON f1.asked_user_id = f2.asker_user_id 
        AND f1.asker_user_id = f2.asked_user_id
        INNER JOIN users AS u ON f1.asked_user_id = u.id 
        WHERE f1.status = 1 AND f2.status = 1
        AND f1.asker_user_id = 2
    ) a
    LEFT JOIN connections c ON c.user_id = a.friend_id 
    AND c.invisible <> 1 AND c.deleted <> 1
    LEFT JOIN words_en w ON c.word_id = w.id 
    GROUP BY 1
) b
LEFT JOIN messages m ON m.to_user_id = 2
 AND m.from_user_id = b.friend_msg_id 
 AND m.seen = 0
LEFT OUTER JOIN 
(
    SELECT b.user_id AS friend_id, GROUP_CONCAT(a.word) AS words_in_common
    FROM words_en a
    INNER JOIN words_en b
    ON a.word = b.word
    WHERE a.user_id = 2
    GROUP BY b.user_id
) words_common
ON b.friend_msg_id = words_common.friend_id
GROUP BY b.name_surname, b.avatar, b.friend_words, b.friend_msg_id
ORDER BY unread_msg DESC

EDIT - modification to use connections table to find common words:-

SELECT b.name_surname,
     b.avatar, 
     b.friend_words, 
     (b.friend_msg_id) AS friend_msg_id, 
     words_common.words_in_common,
     COUNT(m.id) AS unread_msg 
FROM 
(
    SELECT a.name_surname as name_surname,
        a.avatar as avatar,
        GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS friend_words,
        (a.friend_id) AS friend_msg_id
    FROM 
    (
        SELECT f1.asked_user_id AS friend_id,
            f1.created,
            u.name_surname,
            u.avatar
        FROM friends AS f1 
        INNER JOIN friends AS f2 
        ON f1.asked_user_id = f2.asker_user_id 
        AND f1.asker_user_id = f2.asked_user_id
        INNER JOIN users AS u ON f1.asked_user_id = u.id 
        WHERE f1.status = 1 AND f2.status = 1
        AND f1.asker_user_id = 2
    ) a
    LEFT JOIN connections c ON c.user_id = a.friend_id 
    AND c.invisible <> 1 AND c.deleted <> 1
    LEFT JOIN words_en w ON c.word_id = w.id 
    GROUP BY 1
) b
LEFT JOIN messages m ON m.to_user_id = 2
 AND m.from_user_id = b.friend_msg_id 
 AND m.seen = 0
LEFT OUTER JOIN 
(
    SELECT b.user_id AS friend_id, GROUP_CONCAT(c.word) AS words_in_common
    FROM connections a
    INNER JOIN connections b
    ON a.word_id = b.word_id
    INNER JOIN words_en c
    ON b.word_id = c.id
    WHERE a.user_id = 2
    GROUP BY b.user_id
) words_common
ON b.friend_msg_id = words_common.friend_id
GROUP BY b.name_surname, b.avatar, b.friend_words, b.friend_msg_id
ORDER BY unread_msg DESC

Upvotes: 1

Related Questions