Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The first thing is that <strong>you should not implement my first answer</strong>. Instead, you should <strong>change or constrain your schema</strong>. See below for my suggestions.</p> <p>As I understand your schema, it is:</p> <pre><code>create table Friends ( user_Id1 int, user_Id2 int, status int); </code></pre> <p>Where anytime there is a friend relationship, one of the id's is in position 1 and 1 is in position 2.</p> <p>Now, assuming that my id is 1212, my list of friend's ids is:</p> <pre><code> select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1; </code></pre> <p>The list of my Friends' Friends' Ids is:</p> <pre><code>select f1.user_id1 as friends_of_friends from Friends f1 where f1.user_Id2 in (select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1) union select user_id2 as friends_of_friends from Friends f1 where f1.user_Id1 in ( select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id1 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1); </code></pre> <p>And then to add exclusions for my own friends and friends I've blocked, this becomes:</p> <pre><code>select f1.user_id1 as friends_of_friends from Friends f1 where f1.user_Id2 in (select user_Id1 as my_friends_userId /* sub-query for friends of friends */ from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1) and f1.user_id1 not in /* exclusion of my own friends */ (select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1 ) and f1.user_id1 != '1212' /* exclusion of myself. */ and f1.user_id1 not in (select user_Id1 as my_friends_userId /* exlusion of people I've blocked. */ from Friends f where f.user_Id2 = '1212' and status = 3 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 3 ) union /* Now do it all over again for user_id2 */ select f2.user_id2 as friends_of_friends from Friends f2 where f2.user_Id1 in (select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1) and f2.user_id2 not in (select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 1 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 1 ) and f2.user_id2 != '1212' and f2.user_id2 not in (select user_Id1 as my_friends_userId from Friends f where f.user_Id2 = '1212' and status = 3 union select user_Id2 as my_friends_userId from Friends f where f.user_Id1 = '1212' and status = 3 ) </code></pre> <p>where I've marked the first time for each of these conditions. Now, you can see the mess of <code>union</code>'s I had to do for this. (Which should probably be <code>union distinct</code>)</p> <p>You should not be creating the in-clause with a group-concat. Despite the length here, it's faster.</p> <p>You can ask what the individual pieces do. But again, my advise is <strong>DON'T DO THIS</strong>. This is why good table design up front makes things a lot easier.</p> <p>SQL Fiddle for reference and to show results: <a href="http://sqlfiddle.com/#!2/e6376/13" rel="nofollow">http://sqlfiddle.com/#!2/e6376/13</a></p> <hr> <p>EDIT: Just to add about how I would change this schema.</p> <p>It's unclear if in your app, relationships between friends is a Google one (asymmetric relationships allowed), or a Facebook one (only symmetric relationships allowed).</p> <p>In both cases, I'd change the schema to be:</p> <pre><code>create table Friends ( individual_userId int, friend_userId int, status int); </code></pre> <p>In the Google, case, you're done. In the Facebook case, I'd use this structure, but require that for every relationship, two rows go into the table. So if '1212' is Facebook friends w/ '0415', then there are (individual_userid, friend_userId) rows of ('1212', '0415') &amp; ('0415','1212'). Ensuring that this works and is maintained would require stored procedures for insertions/deletions to make sure both rows are added and deleted. (There's no update -- these are unique ids.)</p> <p>If we're sure that these relationships are maintained and that the friend initiating a relationship is always present in individual_userId, then, my final query becomes:</p> <pre><code>select f1.friend_userId as friends_of_friends from Friends f1 where f1.individual_userId in ( /* retrieve my friend list */ select friend_userId as my_friends_userId from Friends f where f.individual_userId = '1212' and status = 1) and f1.friend_userId not in ( /* exclusion of my own friends */ select friend_userId as my_friends_userId from Friends f where f.individual_userId = '1212' and status = 1 ) and f1.friend_userId not in ( /* exlusion of people I have blocked. */ select friend_userId as my_friends_userId from Friends f where f.individual_userId = '1212' and status = 3 ) and f1.friend_userId != '1212' /* exclusion of myself. */ </code></pre> <p>which is much easier to deal with. You could also rewrite this as a series of joins instead, but I suspect that as a first step, using <code>in</code> and <code>not in</code> clauses like this is easier to read.</p> <p>Revised sqlfiddle: <a href="http://sqlfiddle.com/#!2/92ff2/1" rel="nofollow">http://sqlfiddle.com/#!2/92ff2/1</a></p> <p>(I'd have to test it with a large data set, but my gut says that the joins will be faster -- but for code like this, I suspect learning/getting the right answer is more important than optimizing initially for speed.)</p>
    singulars
    1. This table or related slice is empty.
    1. POMysql Query Help for Recommended friends
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Ryan
    1. USMike Ryan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMysql Query Help for Recommended friends
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO+1 for the dirty solution. However I'm curious about the "good design". Can you give a few pointers?
      singulars
      1. PO
      1. USCengiz Can
    2. COThat depends upon whether you're Google or Facebook, which the OP doesn't say. If you're Google, in which having you in my circle doesn't imply the reverse, user_id1 becomes me, and user_id2 becomes my_friends. The Facebook case, in which you must be friends with each other, is more complicated -- personally, I'd do the same thing as the Google case, and by Business logic insert two rows for each relationship with a trigger (and similarly for the delete). That way you can avoid all the unions. I'll think about this a little more during lunch. :)
      singulars
      1. PO
      1. USMike Ryan
    3. COwell i implemented the solution from you {2nd one}, changed the schema and now its working fine.. thanks. Can you please guide me if your or DRapp's way of doing this stuff would be more efficient in term of performance and Why. Thanks once again
      singulars
      1. PO
      1. USJunaid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload