3 Replies Latest reply: Jan 11, 2013 5:44 AM by 984103 RSS

    Searching Criteria to find out the friends of friend

    984103
      Senior DBA'S

      i need to make database where i need to search the person from the current user. Current user can have many friends in his friend list but whom current user searching may be he is not a friend of current user and find out how current user known to that person. Like a friend's Chain.
      Suppose that i m Aman finding out "San". my friends are Abhi, johan, satish etc,
      further abhi's friend is "San" and
      Satish is friend of Mohan, Mohan further friend of Sanju and Sanju will be friend of "San"

      so "Aman" known to "San" by two ways one way by Abhi and 2nd way by Satish. Like link will be

      Aman -> Abhi -> San
      Aman -> Satish -> Mohan -> Sanju -> San

      which way i am asking that is used by linkedin (www.linkedin.com)

      So, Please tell me what best searching criteria will be used to achieve the target with performance because Records can be in billions.
        • 1. Re: Searching Criteria to find out the friends of friend
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Searching Criteria to find out the friends of friend
            Mark Malakanov (user11181920)
            should be like that

            create table PALS (
            pal_id number primary key ,
            Name varchar2(100));

            drop table FRIENDS;
            create table FRIENDS(
            pal_id number ,
            friend_id number ,
            primary key(pal_id , friend_id),
            foreign key (pal_id) references PALS(pal_id),
            foreign key (friend_id) references PALS(pal_id)
            );

            insert into pals values (1,'A');
            insert into pals values (2,'B');
            insert into pals values (3,'C');
            insert into pals values (4,'D');
            insert into pals values (5,'E');
            insert into pals values (6,'F');
            insert into pals values (7,'G');

            insert into friends values (1,2);
            insert into friends values (1,3);
            insert into friends values (2,5);
            insert into friends values (5,7);
            insert into friends values (3,4);
            insert into friends values (4,6);
            insert into friends values (6,7);
            commit;

            -- find leads to G
            with PF as (
            select p.pal_id, p.name, f.friend_id, pf.name friend
            from pals p
            join friends f on p.pal_id=f.pal_id
            join pals pf on pf.pal_id=f.friend_id)
            select level, pf.name, pf.friend
            from PF
            connect by prior pal_id = friend_id start with friend='G';
                 LEVEL NAME       FRIEND   
            ---------- ---------- ----------
                     1 E          G          
                     2 B          E          
                     1 F          G          
                     2 D          F          
                     3 C          D          
            then you have to unwrap it in application.
            May be it is possible in SQL also.

            Edited by: Mark Malakanov (user11181920) on Jan 10, 2013 10:56 AM


            I've found how to do it

            with PF as (
            select p.pal_id, p.name, f.friend_id, pf.name friend
            from pals p
            join friends f on p.pal_id=f.pal_id
            join pals pf on pf.pal_id=f.friend_id),
            W as (
            select level, pf.name, pf.friend,
            CONNECT_BY_ROOT name R,
            SYS_CONNECT_BY_PATH(friend,' -> ') P,
            CONNECT_BY_ISLEAF IsLeaf
            from PF
            connect by pal_id = prior friend_id start with name='A'
            )
            select R || P P from W where IsLeaf=1 and friend='G';
            P                                                                              
            --------------------------------------------------------------------------------
            A -> B -> E -> G                                                                 
            A -> C -> D -> F -> G  
            Edited by: Mark Malakanov (user11181920) on Jan 10, 2013 11:28 AM

            Edited by: Mark Malakanov (user11181920) on Jan 10, 2013 11:33 AM
            So, Please tell me what best searching criteria will be used to achieve the target with performance because Records can be in billions.
            create index PALS_NAME on PALS(NAME);
            or use PAL_ID in
            connect by pal_id = prior friend_id start with PAL_ID=1

            Edited by: Mark Malakanov (user11181920) on Jan 10, 2013 11:42 AM

            Edited by: Mark Malakanov (user11181920) on Jan 10, 2013 11:44 AM
            • 3. Re: Searching Criteria to find out the friends of friend
              984103
              Thanx its really nice and appriciate your efforts........... thanx a lot again

              Edited by: 981100 on Jan 11, 2013 3:43 AM