MySQL Selecting information based on the timestamp of another table

Question

EDIT: I put this in both the PHP and MySQL categories, as I believe there might be a way to do this with a few queries and link them together using PHP. I would rather not do this ... but in the end the solution is the solution ...

This, I hope, will be easy for someone to think about, but of course I'm stuck here! On my site, I allow users to post feeds / posts, and they are allowed to change their membership as often as they would like. I would like to run some statistics based on the memberships they had when they posted each channel, not just their current membership. For example, I would like to see how many feeds were published by each membership. Right now, I can start an account and join the table of users, feeds and memberships, but this counts each channel by each user based on their current membership, which is not necessarily what their membership was when they were posted. Hope this makes sense.

Since there are many channels in our database, I cannot add a feed table to the column that indicates the type of membership that the user published. Here's how my tables look (in abbreviated form) to see if anyone has an idea on how to do this:

User table

id username membershipid 1 John Doe 1 

Membership Table

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

Membership 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 

Feed 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 

With unix_timestamps it's hard to see quickly ... but I would like this to provide this:

Number of feeds according to the participants table

 membershipid feedcount 1 4 2 2 3 0 

So far I have tried many things, but all of them ultimately provide current user membership ... for example:

 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 the membership history table, so my output table:

Number of feeds according to the participants table

 membershipid feedcount 1 6 2 0 3 0 

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

Answer 1

Note # 1 . The query you want to fulfill is extremely expensive, my advice is simply to keep the current membership in the feed table at the time the feed is published.

Note # 2 . The query below assumes that the membership history always contains at least one entry for each user, and not only when it is changed (therefore, the first appointment creates a historical record for it)

As I thought, your decision “how do I request the value of the membership identifier at time X?”, The answer is simple, this is the last changed value BEFORE BEGINNING 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: "How can I list all the feeds along with the membership ID, where X is the time the feed was sent?

 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 

What are the outputs (from my sample data):

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

Of this, one of your decisions should be trivial, just put the entire request in a view or subquery and group by historical_membershipsid, but remember that it is very expensive.

UPDATE

I apologize if the final decision was not so obvious, here is the final request for calculating membership feeds 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