I have written a MySQL query for social networking stream.
Here it is:
SELECT distinct(d.id), d.directory, u.name, u.pic
FROM data as d, follow as f, users as u
WHERE f.flwid = $id
AND f.status = 1
AND d.blocked = '0'
AND (d.upld_id = $id OR d.timeline_id = $id OR d.upld_id = f.acid OR d.timeline_id = f.acid)
AND (d.`with`<if(f.is_con=1,3,2) or d.`with`<if(d.upld_id=$id,4,2))
AND u.id = d.upld_id
ORDER BY
d.id DESC
LIMIT 18
Now the problem above is that when user doesn't follow anyone (i.e follow table doesn't have any rows for a specific user) it doesn't show user's own post in home stream because both table doesn't join because of no rows in follow table.
I can't configure how I can change this query I just use follow table's corredentials 4-5 places and i cant use IN with subquery because for subqueries will overburden the server :)
Anyone help please It will be appreciated very much.
Currently it gives data like this If user have followed one or more peoples/pages Than it shows user's own and followed account's posts.
**But ** if user doesn't follow any other account means a newly registered user than he/she share any post it doesn't show in home stream because the follow table doesn't join with other two tables because it doesn't have any rows for session user.
Edit The Above SQL query returns the rows which contains unique post by user and his followings. I show these posts in home of users's account with the help of PHP . But the problem i getting is that When user is new or he/she doesn't follow someone than than the follow table doesn't join with data table thats why users's own posts also not shown up in her/his account's home. Someone help me in solving my SQL query. I have found just a Non-SQL idea in which in users table i will store a column named n_followings and at the time of fetching data with query i will use PHP condition and if n_followings==0 than i will use this sql query
SELECT <columns> FROM data where timeline_id='$;id' or upld_id='$id'
Use a LEFT JOIN:
SELECT d.id, d.directory, u.name, u.pic
FROM data AS d
JOIN users AS u ON u.id = d.upld_id
LEFT JOIN follow AS f
ON f.flwid = $id
AND f.status = 1
AND (d.upld_id = $id OR d.timeline_id = $id OR d.upld_id = f.acid OR d.timeline_id = f.acid)
WHERE d.`with` < IF(f.is_con = 1, 3, 2)
OR d.`with` < IF(d.upld_id = $id, 4, 2)
ORDER BY d.id DESC
LIMIT 18