title: Intelligent MySQL GROUP BY for Activity Streams
body: <p>I'm building an activity stream for our site, and have made some decent headway with something that works pretty well.</p>
  
  <p>It's powered by two tables:</p>
  
  <p><strong>stream</strong>:</p>
  
  <ul>
  <li><code>id</code> - Unique Stream Item ID</li>
  <li><code>user_id</code> - ID of the user who created the stream item</li>
  <li><code>object_type</code> - Type of object (currently 'seller' or 'product')</li>
  <li><code>object_id</code> - Internal ID of the object (currently either the seller ID or the product ID)</li>
  <li><code>action_name</code> - The action taken against the object (currently either 'buy' or 'heart')</li>
  <li><code>stream_date</code> - Timestamp that the action was created.</li>
  <li><code>hidden</code> - Boolean of if the user has chosen to hide the item.</li>
  </ul>
  
  <p><strong>follows</strong>:</p>
  
  <ul>
  <li><code>id</code> - Unique Follow ID</li>
  <li><code>user_id</code> - The ID of the user initiating the 'Follow' action.</li>
  <li><code>following_user</code> - The ID of the user being followed.</li>
  <li><code>followed</code> - Timestamp that the follow action was executed.</li>
  </ul>
  
  <p>Currently I'm using the following query to pull content from the database:</p>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>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 = '1'
    AND stream.hidden = '0'
  GROUP BY stream.user_id,
       stream.action_name,
       stream.object_type,
       date(stream.stream_date)
  ORDER BY stream.stream_date DESC;
  </code></pre>
  
  <p>This query actually works pretty well, and using a little PHP to parse the data that MySQL returns we can create a nice activity stream with actions of the same type by the same user being grouped together if the time between the actions isn't too great (see below example).</p>
  
  <p><img src="https://i.stack.imgur.com/RcFlZ.png" alt="Current Stream Output Example"></p>
  
  <p>My question is, how do I make this smarter? Currently it groups by one axis, "user" activity, when there are multiple items by a particular user within a certain timeframe the MySQL knows to group them.</p>
  
  <p>How can I make this even smarter and group by another axis, such as "object_id" so if there are multiple actions for the same object in sequence these items are grouped, but maintain the grouping logic we currently have for grouping actions/objects by user. And implementing this without data duplication?</p>
  
  <p>Example of multiple objects appearing in sequence:</p>
  
  <p><img src="https://i.stack.imgur.com/ERG3P.png" alt="Multiple Objects Appearing in Sequence"></p>
  
  <p>I understand solutions to problems like this can get very complex, very quickly but I'm wondering if there's an elegant, and fairly simple solution to this (hopefully) in MySQL.</p>