doushenyi9104 2013-12-15 22:18
浏览 103

too long

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
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 msix packaging tool打包问题
    • ¥28 微信小程序开发页面布局没问题,真机调试的时候页面布局就乱了
    • ¥15 python的qt5界面
    • ¥15 无线电能传输系统MATLAB仿真问题
    • ¥50 如何用脚本实现输入法的热键设置
    • ¥20 我想使用一些网络协议或者部分协议也行,主要想实现类似于traceroute的一定步长内的路由拓扑功能
    • ¥30 深度学习,前后端连接
    • ¥15 孟德尔随机化结果不一致
    • ¥15 apm2.8飞控罗盘bad health,加速度计校准失败
    • ¥15 求解O-S方程的特征值问题给出边界层布拉休斯平行流的中性曲线