Mysql – Get the id_user_app if not exists in id_user_rq

MySQLperformancequeryquery-performance

I'm building a application and have this table for users relationships. I need to build some queries like for example:

Who invited me as a friend?
Who I invited to be my friend?
Who I accept as friend?

And need some help because for me are complex queries :(. Also I don't know if open trhead for each of the question or leave all them here in this topic, what's the best?

For the first question: I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_app if not exists in id_user_rq as the title said. I try this query:

SELECT DISTINCT A.*, p.*, u.email FROM default_relations_users A 
    LEFT JOIN default_relations_users B ON (A.id_user_rq = B.id_user_ap
    AND A.id_user_ap = B.id_user_rq AND A.id_user_rq = 1)
    LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
    LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
    GROUP BY A.id_user_rq

But it's not working, any advice or help?

Best Answer

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_ap IS NULL
              GROUP BY A.id_user_rq

I changed A and B to be subqueries using 1 for id_user_rq and id_user_ap

If you want the opposite, just reverse A and B subqueries to something like this:

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_rq IS NULL
              GROUP BY A.id_user_rq

Related Solutions

Mysql – Get the rows where id_user_rq exists in id_user_ap and viceversa

Please try this:

SELECT A.* FROM
(SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
LEFT JOIN
(SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
ON
(A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
WHERE B.id_user_ap IS NOT NULL;

Mysql – Need some help with this query get all status and their associated comments of me and all the friends

Ok, so based on our conversation if you make sure that when a friend's request is created you also create an entry in the other direction: (i.e. Friend_A asks Friend_B, but you also need a relationship between Friend_B and Friend_A) then you can run the following query to obtain the results that you need:

select distinct
s.user_id, 
u.username,
p.display_name,
p.first_name,
p.last_name,
s.message
from default_status as s
join default_friend as f on f.user_id = s.user_id
join default_users as u on u.id = s.user_id
join default_profiles as p on p.user_id = u.id
where (f.user_id = 1 or f.friend_id = 1)
and f.approved = 1
order by s.created_at desc;

This will also make it easier to obtain a list of all of a persons friend's:

select *
from default_users as u
join default_friends as f on f.friend_id = u.id
where f.user_id = 1;

Just remember to put an index over user_id and friend_id in the default friends_table otherwise you will find your query may take a while:

create unique index idx_friend on default_friend(user_id,friend_id);

I hope this helps you. ;-)

Best Answer

Related Solutions

Mysql – Get the rows where id_user_rq exists in id_user_ap and viceversa

Mysql – Need some help with this query get all status and their associated comments of me and all the friends

Related Question