Get rid of ambiguous columns like object_id. Use log_type to determine what you are querying on. So something like:
CREATE TABLE logs (
log_id bigint AUTO_INCREMENT, -- not sure if this is the mysql syntax
user_id int references users(id),
post_id bigint references posts(id)
);
A second point is I would suggest changing the friend/unfriend to be immediate so it doesn't hit this table. This should be only for logging actions to posts, not friendships and the like. If you must log and propagate, create a separate table for this.
Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:
INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`)
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);
After you have done that your query should be more like the result you are after. Running this query:
select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;
returns the following result set:
user_id username friend_id friend status_id message created_at privacy user_id is_reply device
1 admin 2 demo 1 dasdasdasdasdasd 2012-08-13 19:45:37 NULL 1 0
2 demo 1 admin 2 dasdasdasdasdasd 2012-08-13 19:46:03 NULL 2 0
1 admin 2 demo 3 dasdsad344hbvnbnhjhgjhjghjhj 2012-08-13 21:54:53 NULL 1 0
Is this anywhere near what you are looking for?
Best Answer
If you want to get the names of the friends for a user perhaps try something like so:
This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1
I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?