mardi 30 juin 2020

I want the follower key to be true too... in my mutual friend query

Here is the query. Basically i am passing two parameters user_id (logged in user) and friend_id so the query will fetch all friends imagine you are click on your own friend and seeing the friend on another screen some are mutaul friend.. in this query mutaul is fine.. but when i want to follow the person that is not followed by the firned_id i need that to be true when there is exists data against the user_id

here im checkign relationship between user_id=334 and friend_id=312 image i have friend_id have these friend 1,2,3 and user(logged in user) has 3, 4, 5 now 3 is mutal friend that is toally fine and my ismutaul=1

now there is a record against the friend 2 in followers table in database so friend 2 actually is the friend of 312 and 312 not following. so how can i make it true because 334 is already follwoing him i need the isfollower varaible to be true.

im stuck and i dont know what to do any help please?

SELECT 
    users.id AS user_id,
    users.username,
    CONCAT('/',
            IF(users.photo = '',
                'default_image.png',
                users.photo)) AS user_image,
    users.phone,
    (SELECT 
            COUNT(user)
        FROM
            meal_rating
        WHERE
            user = users.id AND comment <> '') AS checkin_count,
    (CASE
        WHEN
            uf.receiver = (SELECT 
                    receiver
                FROM
                    user_friends
                WHERE
                    sender = 312 AND receiver = uf.receiver
                        AND user_friends.receiver IN (SELECT 
                            receiver
                        FROM
                            user_friends
                        WHERE
                            sender = 334))
        THEN
            1
        ELSE 0
    END) AS isMututal,
    CASE
        WHEN
            uf.receiver = (SELECT 
                    receiver
                FROM
                    user_followers
                WHERE
                    sender = 312 AND receiver = uf.receiver)
        THEN
            1
        ELSE 0
    END AS isfollowers,
    users.*
FROM
    `users`
        INNER JOIN
    `user_friends` `uf` ON `users`.`id` = `uf`.`receiver`
WHERE
    `uf`.`sender` = 312 
UNION SELECT 
    users.id AS user_id,
    users.username,
    CONCAT('/',
            IF(users.photo = '',
                'default_image.png',
                users.photo)) AS user_image,
    users.phone,
    (SELECT 
            COUNT(user)
        FROM
            meal_rating
        WHERE
            user = users.id AND comment <> '') AS checkin_count,
    (CASE
        WHEN
            sender = (SELECT 
                    sender
                FROM
                    user_friends
                WHERE
                    receiver = 312 AND sender = uf.sender
                        AND sender IN (SELECT 
                            sender
                        FROM
                            user_friends
                        WHERE
                            receiver = 334))
        THEN
            1
        ELSE 0
    END) AS isMututal,
    CASE
        WHEN
            uf.sender = (SELECT 
                    sender
                FROM
                    user_followers
                WHERE
                    sender = 312
                        AND user_followers.receiver = uf.sender)
        THEN
            1
        ELSE 0
    END AS isfollowers,
    users.*
FROM
    `users`
        INNER JOIN
    `user_friends` uf ON `users`.`id` = `uf`.`sender`
WHERE
    `uf`.`receiver` = 312


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire