I'm trying to build an intelligent news feed for users on my website.
The news feed shows photos of what a users followers have liked or commented on. Also it shows people who a user's followers have just followed. Sort of like news feed on Instagram.
I have the following Tables:
user follow comment notifications photo
user table has the following fields:
userid, username, avatar
followtable has the following fields:
userid( the user), followingid(whom the user is following) datefollwed
comment table has the following fields:
comment, commentid, datecommented, commenter, photoowner
Notifications table has the following fields:
id, type( type could be 1 for like, 2 for comment, 3 for new follow), datenotified, who(who made the action), whom( whom the action is affecting), what ( the id of the action so like the commentid, likeid, can be 0 for followid)
prhoto table has the following fields:
photo( filename), photoid, dateuploaded, userid (who uploaded it)
Now I want to build an intelligent news feed that I can echo out through a multidimensional array in php.
For instance
if John with user id 1 is following Anne (2), Jane(3) and Mark(4)
And Mark like 5 photos of Bruce(5), commented on Jane' photo and followed Anne and Jane like James(6) photo and Johns (8) photo and Anne followed Mary(7)
I would want to be able to query a result that groups similar action by users so it would read something like
Mary liked 5 photos of bruce (and show the first photo in the sub array)
Mary commented on Janes photo ( with the comment)
Mary followed Anne
Jane like 2 photos - ( James photo) and ( Johns photo)
Anne followed mary.
I have a query that is able to get the results but it is not intelligent. I would want to make it group by the user axis. Also the query that I have does not show whom the followers action id performed on
So i would Mary liked 5 photo of (5 the id and not the name)
Here is my query
SELECT userTable.username,
userTable.avatar,
userTable.userid,
userTable.verified,
followTable.followingid,
notificationsTable.who,
notificationsTable.whom,
notificationsTable.what,
notificationsTable.id,
notificationsTable.timestamp,
notificationsTable.type,
notificationsTable.status,
notificationsTable.date,
commentTable.comment,
commentTable.commentid,
photoTable.photo,
photoTable.title,
photoTable.postid
FROM userTable
INNER JOIN followTable
INNER JOIN notificationsTable
ON followTable.followingid = userTable.userid
AND notificationsTable.whom = followTable.followingid
LEFT OUTER JOIN photoTable ON shareTable.postid = notificationsTable.what
LEFT OUTER JOIN commentTable
ON commentTable.commentid = notificationsTable.commentid
WHERE followTable.userid = '1'
order by notificationsTable.date