Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts

    Best way to query this?

    I need to get all records from my posts table with the author field equal to either f1 or f2 in the friends table, the user is the opposite (i.e if the post.author is f1, the user is f2), AND the status field in the friends table is equal to 1. Or I need to get all records from the posts table with the author field equal to either f1 or f2, the user is the opposite, BUT instead of the status field being checked, in this case I need the `following` field equal to true.

    Basically something similar to facebook or twitter.
    This is what I have but this is a little too complex as at the moment all this does is get all posts.
    PHP Code:
    $getFP "SELECT posts.author, posts.post, posts.date, friends.friends, friends.following, friends.f1, friends.f2, friends.status FROM `posts` INNER JOIN `friends` ON posts.author=friends.f1 AND friends.status=1 OR posts.author=friends.f2 AND friends.status=1 ORDER BY `date` DESC"
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    Surely you don't mean what you wrote.

    *ALL* the records???

    Surely you want to limit this to all the records RELEVANT to a *PARTICULAR* person, no?

    So you have nothing in that code to limit you to a single person.

    But I still don't understand your f1, f2, and author requirements.

    Maybe you could give a few examples?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts
    Well no lol, not literally all records. Hmm, examples.

    Well, if I send you a friend request in the friends table my username would be logged as f1 (short for friend 1 or follower 1; depending on if you are friends with or following f2). Likewise if I start following you (think twitter), I would logged as f1 and you as f2. F1 follows or is following (or subscribed to) f2. Or f1 can be friends with f2.

    In the posts table, author would be the creator of the post(s). The query is to get posts from any and only everyone f1 is friends with or following (f2).

    Like I said, the best examples are facebook and twitter as this is for a social networking website. Its not necessarily a single person the query needs to get results from. The author field can/could vary depending on who f1 is friends with or following.

    For another example:
    Lets say I'm friends (I'm f1 in this case) with Ed and following Sarah (ed and sarah are f2) but there are other members on the site with posts named...Carl and Fred. For me it should only show ed and sarah's posts (the author of these posts would be ed and sarah). For maybe another user, it should show Carl and Fred's posts. But maybe Carl is following me. For him it should also show my posts (in this case I am f2) Does this explain it better?

    Ah, yes and in order to "officially" be friends with someone status must be 1; as 0 is an unconfirmed friend request while 2 is a denied request. Status is irrelevant though when f1 is following f2
    Last edited by elitis; 01-20-2013 at 08:07 AM.
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    I meant show some sample data from your tables and then what results you would want to see from that sample data.

    I still don't see any example in the words you used there where the primary user we are concerned with is f2. Will that ever happen???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts
    50-50% chance. If someone else sends another user a friend request, in that case that user would be f2. In simpler terms, if you as a user send another a friend request you are f1. If someone sends you a friend request you are f2.

    As for sample data,
    A record in the friends table (*fields on top, record on bottom)
    friends | following | f1 | f2 | status
    true | false | Elitis | MChris | 0
    true | false | eTest | Elitis | 1

    A record in the posts table
    id | author | post | date
    6 | eTest | @Elitis should be able to see this. @MChris should not be able to. | 2013-01-20 01:12:15
    7 | LilEph | Blah Blah Blah | 2013-01-20 02:56:58

    For the query, personally I should be able to see all of eTest's posts but not MChris' since the status isn't confirmed (i.e 1). 'LilEph' and 'MChris' shouldn't see any posts since they don't have any records in the friends table where they are either f1 or f2 and a confirmed status of 1 OR a record where they are f1 (i.e the user will NEVER be f2 *IF* the user is following someone else) AND the `following` field is true
    If you have a better way of of structuring the table, I'm open to ideas.
    Last edited by elitis; 01-21-2013 at 03:44 AM.
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    Ahhh...see...you *DID* need to clarify the issue about "following"!

    SO...Keep it simple:
    Code:
    SELECT P.author, P.post, P.date, F.friends, F.following, F.f1, F.f2, F.status 
    FROM posts AS P, friends AS F
    WHERE ( F.f1 = '$me' AND F.f2 = P.author AND F.status = 1 )
       OR ( F.f2 = '$me' AND F.f1 = P.author AND F.status = 1 )
       OR ( F.f1 = '$me' AND F.f2 = P.author AND F.following = True )
    ORDER BY `date` DESC
    But you can simplify that a little:
    Code:
    SELECT P.author, P.post, P.date, F.friends, F.following, F.f1, F.f2, F.status 
    FROM posts AS P, friends AS F
    WHERE ( F.f1 = '$me' AND F.f2 = P.author AND ( F.status = 1 OR F.following = True ) )
       OR ( F.f2 = '$me' AND F.f1 = P.author AND F.status = 1 )
    ORDER BY `date` DESC
    You *need* the parentheses around ( F.status = 1 PR F.following = True ), but the others aren't truly needed. I would certainly leave them in, though, for clarity.

    $me can of course be any one person you are trying to match. Example: $me = 'Elitis';
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    By the by, MySQL doesn't really make any distinction between TRUE and the number 1. So you could treat your status fields as a True/False field if you wanted. Or you could treat following as 1/0.

    If you treated following as 1/0, you could rewrite that second version even more compactly:
    Code:
    SELECT P.author, P.post, P.date, F.friends, F.following, F.f1, F.f2, F.status 
    FROM posts AS P, friends AS F
    WHERE ( F.f1 = '$me' AND F.f2 = P.author AND 1 IN ( F.status, F.following ) )
       OR ( F.f2 = '$me' AND F.f1 = P.author AND F.status = 1 )
    ORDER BY `date` DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts
    That's interesting, without going into too much detail is there is any real reason behind MySQL's lack of distinction between the two?

    And the code you gave is almost perfect. It is not displaying any posts from 'followers'. But works perfectly if the users are confirmed friends.
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    If it is not showing posts from followers, then you need to show me sample records *with* followers. You showed samples with friends, but not followers.

    ********

    Historically, MySQL did not have the BOOLEAN data type until relatively late in its development. To add it in, without messing over thousands and thousands of existing queries, the essentially just made BOOLEAN a synonym for TINYINT(1).

    Try it yourself:
    Code:
    create table elitis ( name varchar(30), follower boolean );
    
    describe elitis;
    And MySQL shows you:
    Code:
    mysql> describe elitis;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | name     | varchar(30) | YES  |     | NULL    |       |
    | follower | tinyint(1)  | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    Well, clearly if BOOLEAN is actually *implemented* as TINYINT(1), then such a field *must* accept integer values. So, of course, it is legal to do
    Code:
    INSERT INTO elitis VALUES( 'whatever', 1 );
    INSERT INTO elitis VALUES( 'moreover', 0 );
    (and, incidentally, it's also legal to do INSERT INTO elitis VALUES( 'yowser', 113 ) because the (1) part of TINYINT(1) is *NOT* enforced!).

    And if you want further proof, just do this:
    Code:
    mysql> select true;
    +------+
    | TRUE |
    +------+
    |    1 |
    +------+
    
    mysql> select false;
    +-------+
    | FALSE |
    +-------+
    |     0 |
    +-------+
    So...BOOLEAN in MySQL is a hack, but it's a usable hack.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Sep 2010
    Posts
    331
    Thanks
    9
    Thanked 6 Times in 6 Posts
    friends | following | f1 | f2 | status
    false | true | Elitis | eTest | 0
    -----------------
    Coding is a challenge, get used to it
    Always remember to debug
    Try the guess & check method
    Break it down into simple steps

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    Quote Originally Posted by elitis View Post
    friends | following | f1 | f2 | status
    false | true | Elitis | eTest | 0
    -----------------
    Hmmm...So I gave you:
    Code:
    SELECT P.author, P.post, P.date, F.friends, F.following, F.f1, F.f2, F.status 
    FROM posts AS P, friends AS F
    WHERE ( F.f1 = '$me' AND F.f2 = P.author AND F.status = 1 )
       OR ( F.f2 = '$me' AND F.f1 = P.author AND F.status = 1 )
       OR ( F.f1 = '$me' AND F.f2 = P.author AND F.following = True )
    ORDER BY `date` DESC
    Looks to me like that should work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,437 Times in 4,402 Posts
    Since you seem so reluctant to give me any extensive examples, I have created my own:
    Code:
    mysql> select * from posts;
    +--------+------------------------------+
    | author | post                         |
    +--------+------------------------------+
    | eTest  | eTest is following elitis    |
    | joe    | joe and elitis are friends   |
    | harry  | harry and elitis are friends |
    | john   | harry and john are friends   |
    +--------+------------------------------+
    
    mysql> select * from friends;
    +---------+-----------+--------+--------+--------+
    | friends | following | f1     | f2     | status |
    +---------+-----------+--------+--------+--------+
    |       0 |         1 | Elitis | eTest  |      0 |
    |       1 |         0 | Elitis | joe    |      1 |
    |       1 |         0 | harry  | Elitis |      1 |
    |       1 |         0 | harry  | john   |      1 |
    +---------+-----------+--------+--------+--------+
    And then I duplicated the query, leaving out only the P.date stuff:
    Code:
    mysql> SELECT P.author, P.post, F.friends, F.following, F.f1, F.f2, F.status
        -> FROM posts AS P, friends AS F
        -> WHERE ( F.f1 = 'Elitis' AND F.f2 = P.author AND F.status = 1 )
        ->    OR ( F.f2 = 'Elitis' AND F.f1 = P.author AND F.status = 1 )
        ->    OR ( F.f1 = 'Elitis' AND F.f2 = P.author AND F.following = True );
    +--------+------------------------------+---------+-----------+--------+--------+--------+
    | author | post                         | friends | following | f1     | f2     | status |
    +--------+------------------------------+---------+-----------+--------+--------+--------+
    | eTest  | eTest is following elitis    |       0 |         1 | Elitis | eTest  |      0 |
    | joe    | joe and elitis are friends   |       1 |         0 | Elitis | joe    |      1 |
    | harry  | harry and elitis are friends |       1 |         0 | harry  | Elitis |      1 |
    +--------+------------------------------+---------+-----------+--------+--------+--------+
    And it sure worked for me.

    So maybe you need to re-examine your data???

    (And I may have misunderstood you; perhaps "eTest is following elitis" should read "elitis is following eTest". But that doesn't change the fact that the query found the post.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •