Pushing pointers to followers with the metadata

I’ve seen the following question on StackOverflow, http://stackoverflow.com/questions/14004322/intelligent-mysql-group-by-for-activity-streams posted by Christian Owens 12/12/12.

So I decided to try out the same approach, make two tables similar to
those of his. And then I pretty much copied his query which I do
understand. This is what I get out from my sandbox:

Array(
    [0] => Array(
        [id] => 0
        [user_id] => 1
        [verb] => published_post
        [object_id] => 776286559146635
        [type] => post
        [stream_date] => 2015-11-24 12:28:09
        [rows_in_group] => 1
        [in_collection] => 0
    )
)

I am curious, since looking at the results in Owens
question, I am not able to fully get something, and does he perform
additional queries to grab the actual metadata? And if yes, does this
mean that one can do it from that single query or does one need to run
different optimized sub-queries and then loop through the arrays of data
to render the stream itself.

Thanks a lot in advanced.

My DB Schema:

I am still waiting for a reply! I am most curious if I need to left-join the metadata, and then using some PHP magic render a stream of activities for the follower (“subscriber”) to view in
his/her feed. I’d be super glad if someone could reply! Thanks!

he is using,
INNER JOIN follows ON stream.user_id = follows.following_user

now if you want to do a second query on followers, use something like

INNER JOIN follows AS stream
ON stream.user_id = follows.following_user
...
INNER JOIN follows AS username
ON stream.user_id = follows.following_user
...

Use print_r to see the results

1 Like

Thanks for your reply! My concern isn’t the inner join of follows.following_user, what he does it get the stuff from users where INNER JOIN follows ON stream.user_id = follows.following_user.

I am wondering, why is he lacking another join, you never get the metadata. If you checked the link on stackoverflow, he shows some screenshots:


He renders the above using PHP. I know how to render stuff etc. But he doesn’t include the metadata, with this query. For example: Brian Kramer hearted 2 sellers.

Brain Kramer has a user id, hearted is the verb of action taken, and two sellers represent the objects. See my DB schema again.

BTW, the array output I pasted into my question was printed with print_r.

P.s. Made this: http://sqlfiddle.com/#!9/4ddf0

http://sqlfiddle.com/#!9/4ddf0/3 (Updated)

SELECT stream.*,
   COUNT(stream.id) AS rows_in_group,
   GROUP_CONCAT(stream.id) AS in_collection
FROM stream
INNER JOIN follows ON stream.user_id = follows.following_user
WHERE follows.user_id = '0'
GROUP BY stream.user_id,
     stream.verb,
     stream.object_id,
     stream.type,
     date(stream.stream_date)
ORDER BY stream.stream_date DESC;

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.