MySQL Selecting Info based on timestamp of another table

Question

EDIT: I have put this in both PHP and MySQL categories as I believe there may be a way to do this using multiple queries and to tie them together using PHP. I would rather not do this...but at the end of the day, a solution is a solution...

This will hopefully be easy for someone to think about, but I am certainly stuck here! On my site, I allow users to post feeds/messages, and they are allowed to switch memberships as often as they would like. I would like to run some statistics based on the memberships they were when they posted each feed, rather than just their current membership. For example, I would like to see how many feeds were posted by each membership. Right now, I can run a count and join the users, feeds and memberships table, but this counts every feed by each user based on their current membership which is not necessarily what their membership was when they posted. I hope this makes sense.

As there are many feeds already in our DB, I am unable to add in a column to the feeds table showing which membership type the user who posted is. Here is what my tables (abbreviated) looks like to see if anyone has a query idea of how to do this:

Users Table

id  username  membershipid
1   John Doe  1

Memberships Table

id  membershipname
1   Membership #1
2   Membership #2
3   Membership #3

Memberships History Table

id  membershipsid  usersid  unix_timestamp
1   1              1        1476635544.33
2   2              1        1476641890.11
3   3              1        1476642124.2
4   1              1        1476642161.51

Feeds Table

id  unix_timestamp     usersid
1   1476641716.809361  1
2   1476641783.866863  1
3   1476641822.779324  1
4   1476641904.066237  1
5   1476641973.767174  1
6   1476642182.821472  1

It is difficult to see quickly with the unix_timestamps...but what I would like is for a solution to provide this:

Feed Count by Membership Table

membershipid  feedcount
1             4
2             2
3             0

So far, I have tried many things but they all end up providing the current membership the user has...such as:

SELECT
    a.MembershipName MembershipName,
    COUNT(*) Feeds
FROM (SELECT
        m.membership_name MembershipName
    FROM feeds f
    JOIN users u ON f.usersid = u.id
        JOIN memberships m ON u.membership_id = m.id
    GROUP BY f.id
    ORDER BY f.unix_timestamp DESC) a
    GROUP BY a.MembershipName
ORDER BY a.MembershipName

But this does nothing with membership history table, so my output table is:

Feed Count by Membership Table

membershipid  feedcount
1             6
2             0
3             0

which is wrong, as it should be 1->4, 2->2 & 3->0 as shown in the table above. Any ideas from anyone?

Answer 1

Note #1 - The query you want to perform is extremely expensive, my advice is to simply save the current membershipsid into the feeds table at the time the feed is posted

Note #2 - The query below assumes that the memberships history always contain at least one entry per user, not only when it is changed (so the first time it is assigned the historical record is created)

The way I thought your solution is "how do I query the membership id value at time X?", the answer is simple, it is the most recent changed value BEFORE time X:

SELECT h.membershipsid
FROM membershipshistory h
WHERE h.usersid = {USERID} AND h.unix_timestamp < {X}
ORDER BY h.unix_timestamp DESC
LIMIT 1

Next thing is "How do I list all the feeds together with the memberships ID where X is the time when the feed was posted?

SELECT
  feeds.id,
  feeds.usersid,
  (
    SELECT h.membershipsid
    FROM membershipshistory h
    WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
    ORDER BY h.unix_timestamp DESC
    LIMIT 1
  ) AS historical_membershipsid
FROM
  feeds

Which outputs (from my sample data):

+------+---------+--------------------------+
| id   | usersid | historical_membershipsid |
+------+---------+--------------------------+
|    1 |       1 |                        1 |
|    2 |       1 |                        2 |
|    3 |       1 |                        3 |
+------+---------+--------------------------+

From this one your solution should be trivial, just put the whole query in a view or subquery and group by historical_membershipsid, but remember it is very expensive.

UPDATE

I apologize if the final solution was not that obvious, here is the final query to count feeds per membership using historical data:

SELECT
  hf.historical_membershipsid AS membershipsid,
  COUNT(hf.id) AS feedcount
FROM (
  SELECT
    feeds.id,
    feeds.usersid,
    (
      SELECT h.membershipsid
      FROM membershipshistory h
      WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
      ORDER BY h.unix_timestamp DESC
      LIMIT 1
    ) AS historical_membershipsid
  FROM
    feeds) AS hf
GROUP BY
  hf.id