Best way to query this?

[elitis]

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"; 


[Old Pedant]

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?

[elitis]

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

[Old Pedant]

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???

[elitis]

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.

[Old Pedant]

Ahhh...see...you *DID* need to clarify the issue about "following"!

SO...Keep it simple:

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:

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';

[Old Pedant]

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:

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

[elitis]

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.

[Old Pedant]

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:

create table elitis ( name varchar(30), follower boolean );

describe elitis;

And MySQL shows you:

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

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:

mysql> select true;
+------+
| TRUE |
+------+
|    1 |
+------+

mysql> select false;
+-------+
| FALSE |
+-------+
|     0 |
+-------+

So...BOOLEAN in MySQL is a hack, but it's a usable hack.

[elitis]

friends | following | f1 | f2 | status
false | true | Elitis | eTest | 0
-----------------

[Old Pedant]

Originally Posted by elitis

friends | following | f1 | f2 | status
false | true | Elitis | eTest | 0
-----------------

Hmmm...So I gave you:

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.

[Old Pedant]

Since you seem so reluctant to give me any extensive examples, I have created my own:

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:

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.)