I would say again though it seems unlikely it was a performance issue to begin with. They just changed some logic around and could improve it.
It is not correct to pick some large number of episodes and then filter though. The point of me suggesting that query was to give an alternative where you are working with the set of everything in the last 7 days, and the query itself will flatten them down into shows and limit them. No multi-step processing or magic number tweaking needed. And faster.
I understand what your query does, I’m just saying what Plex clients need back from the server is more than just the name of the show and the number of new episodes. What you did is fine and produces an appropriate result, but that is not what Plex wants. If all that was returned from the server was the result of your query, you would not be able to perform any of the functions you can now such as play the episode (since it wouldn’t know which episosde), or be able to show the in progress status of a show, or a bunch of other features. Well, it could be made to work with a bunch of new coding and a rework of the way Plex handles this information. If you would like to see the info that PMS needs to return, enter the following into your browser. You would need a series of queries (one for each category) that would reproduce this.
http://127.0.0.1:32400/hubs?X-Plex-Token=xxxxxxx
You can find your token by looking up the xml for anything you have in Plex.
I don’t get your point though. My point was to demonstrate that the query that generates the “Recently Added” view can be fixed and made more efficient. That example query is just one piece that generates a list of metadata item IDs. The rest of the stuff, like pulling the metadata and organizing the XML file or whatever is already done in the existing Plex code. You just need to swap out the query/code you’re using to generate the “Recently Added” list of IDs – trust me, it’s there. There is no rewrite/rework needed here. Just changing that little piece of code that generates that list.
tl;dr I wish I had code base access. This could be implemented in 5 minutes. Plenty of ways to skin this cat though
We keep getting things like this pushed out to us, but not the stuff we’re actually waiting on. Reliable per client speed limits. Server side tools and controls, collections that make sense for movies and are usable.
Obviously people aren’t happy with the way the new UI is going.
When do we get the features WE WANT? I’m guessing it’s going to happen when pigs fly after the last few months of waiting on server side speed limits… 
I’ve been bothered by this for quite some time. Good to see that others are already talking about it.
The way it now works is dysfunctional and counter-intuitive. I end up not seeing a show that was added the day before just because the new one has too many episodes associated with it. Despite only taking up one slot, it still appears like no TV shows are added with any regularity.
Everyone please +1 this request: https://forums.plex.tv/discussion/215330/recently-added-for-tv-give-us-more-shows-in-recently-added-dashboard
@honkdazzle You’re so focused on grouping that you conveniently managed to skip over in your SQL example the point of contention I mentioned in my original rebuttal; plex code returns a single episode or aggregates multiple episodes (or seasons if need be) of the same show. Your solution is just to pull what shows\seasons have new entries, which is not a solution to the current results (most recent chronologically by episode added) but a different result (most recent by show\season added) all together.
And I never said it was impossible, just that your way wasn’t the answer. If we wanted to look at a pure SQL method minus post aggregation code that’s a step above a quick and dirty query, one off the cuff method would be -:
Pull x number of most recent episodes and corresponding date based on a shows season (to keep it simple), dump that into a temp table (id,parent_id,date,season_items(1)). Use a basic aggregation query for seasons like you so graciously typed out for us that’s limited to the max min date range in previous query, update previous temp table’s season_items. Pull the ID or parent id from the table based on whether season_item>1. Apply those consolidated ID’s into another query to feed the XML parser.
Huh. (actually that’s not bad eh, @“MovieFan.Plex”)? One could probably figure out the necessary subqueries needed to skip temp tables all together (at the cost of time on lower end hardware?). Either way, it’s more complicated than an simple aggregate.
I agree, this needs to be different. I added The Big Bang Theory S02-S08 and everything else was wiped out. The Recently Added section is a good place for users of my server, myself included, to discover something new to watch. I don’t want to think about when I should add something so I can keep my recently added list populated with several shows/seasons. I just want to add them as I get them. This is the only gripe I have with everything new from Plex. I personally love the direction that Plex is going lately, but this one little problem seems like it’s easy enough to fix. I can’t even remember exactly how it worked before because, well, it just worked well. Hopefully Plex will have the incentive to change this behavior if we all don’t like it.
Alright, one last time.
I don’t have access to the Plex source code but I can tell you it’s extremely likely the “recently added TV” thing works like this:
The only item that has to change is #1. Most likely, #1 is already just creating a list of IDs, and then those IDs are populated with metadata information.
My suggestion all along has been to adjust only step 1, because that is the only thing that exhibits a problem. It doesn’t require rewriting large amounts of code. It really doesn’t require a ton of discussion about the whole chain of stuff going on, either. The only thing that matters here is the first query that determines which items were added most recently.
For some reason the reaction of people here has been “well what about steps 2 and 3?” Ok? What about them? This is a simple bugfix. Fix the query in step 1. Done. You merging 1 and 2 already with a super complex query? Great! Adjust this query to add your joins and columns. Done.
@random.server said:
@honkdazzle You’re so focused on grouping that you conveniently managed to skip over in your SQL example the point of contention I mentioned in my original rebuttal; plex code returns a single episode or aggregates multiple episodes (or seasons if need be) of the same show.
No, the query I posted solved that problem. I don’t think you ever ran it. One thing I did notice was missing was season grouping, but only because I thought Plex deprecated it. I fixed it below.
Pull x number of most recent episodes and corresponding date based on a shows season (to keep it simple), dump that into a temp table (id,parent_id,date,season_items(1)). Use a basic aggregation query for seasons like you so graciously typed out for us that’s limited to the max min date range in previous query, update previous temp table’s season_items. Pull the ID or parent id from the table based on whether season_item>1. Apply those consolidated ID’s into another query to feed the XML parser.
This is not helpful – neither your solution nor your attitude. First you didn’t understand or actually test/try what I did, then you came up with a weird and convoluted solution instead of just using the tools you’re provided in the language.
I may have not been clear enough since I only included “series ID” as a column in the example, and I also realized Plex does in fact still bucketize by individual season if only episodes from that season were recently added, so I’m updating it one last time. The rest is just fetching metadata. And once again a properly designed piece of software isn’t going to go and write complicated custom joins on every query to do a common task; it’s going to generate a single list of IDs and then fetch all the metadata at once, so don’t ramble on about how I am not thinking about the metadata part.
“But the page has to be rendered in a browser! You are wrong because you didn’t write me a web browser too!”
Also note there’s a lot of extra stuff here to help you understand what’s going on. This can be pared down considerably; the type column for example is purely visual. Still fast though and covers everything. Unfortunately the formatting is being eaten by the forums but you can copy/paste this into your editor of choice to clean it up.
select
(case when new_seasons > 1 then series_id else (case when new_items > 1 then season_id else episode_id end) end) as item_id,
(case when new_seasons > 1 then ‘multi-season’ else (case when new_items > 1 then ‘single-season’ else ‘single-episode’ end) end) as type,
new_items,new_seasons,last_created_at,title from (
select st.title as title,
count(1) as new_items,
count(distinct ss.id) as new_seasons,
max(i.created_at) as last_created_at,
i.id as episode_id,
ss.id as season_id,
ss.parent_id as series_id
from
metadata_items i
join metadata_items ss on ss.id=i.parent_id
join metadata_items st on st.id=ss.parent_id
where i.parent_id not null and
i.library_section_id=2 and
i.metadata_type=4 and
i.created_at > datetime(‘now’, ‘-7 days’)
group by series_id
order by last_created_at desc
);
Here’s the output on a sample server:
For comparison here is that server’s “Recently Added TV” dash view:
They match perfectly, with the exception of Adventure Time because it was victim to the episode cutoff (there are two new episodes in the last 7 days, not one). You can also see that Girls was grouped into a series, whereas Clarence was properly grouped into only one season. But the important thing is that this query doesn’t limit by some arbitrary number of episodes like Plex is doing now. It’s purely by time (last 7 days) so it won’t run into display issues from episode starvation caused by bulk imports.
I don’t think I have it in me to explain any further man. I was just really insulted by your disrespectful know-it-all attitude. Also your off-the-cuff idea of a solution is inefficient and doesn’t solve the same problem. I hope this is useful to someone. Plex engs, easy fix if you just mess with your query and do a group by.
I’ll leave you with this quote, brought to you by Storage Spaces:
@honkdazzle you don’t know enough Sql or coding in general if you think that is the solution. To do it in one call, be able to group the resulting data into sets, and show the most recent episode if there was no sets; they’d have to just query a number (how many would probably be dependant on speed) of items, then reduce that number by matching a similar metric (seasons, show).
@honkdazzle Yeah, I actually did type out what your initial code (my test library section was 56 not 2), and it was nothing more than an aggregate query based on parent_id. But never mind that. I’m proud of you, not only did you prove my point (that it’s not just a simple group by), you included a code sample for us.
You went from simple aggregate queries based on the parent_id-:
select count(1) as num_items,max(created_at) as last_created_at, parent_id from metadata_items where parent_id not null and library_section_id=2 and metadata_type = 4 and created_at > datetime('now', '-7 days') group by parent_id order by last_created_at desc
and
select count(1) as num_items, max(i.created_at) as last_created_at, ss.parent_id as series_id from metadata_items i join metadata_items ss on ss.id=i.parent_id where i.parent_id not null and i.library_section_id=2 and i.metadata_type=4 and i.created_at > datetime('now', '-7 days') group by series_id order by last_created_at desc
to a query that almost did this “To do it in one call, be able to group the resulting data into sets, and show the most recent episode if there was no sets; they’d have to just query a number (how many would probably be dependant on speed) of items, then reduce that number by matching a similar metric (seasons, show).”. So kudos.
A couple of issues in you code, recently added uses “added_at” not “created_at”, added is when it went into the library, created does the same but updates for new versions. My quick code outline (that you couldn’t understand?) was built around Plex’s current method of recent by number, not the date method you keep using, hence the temp tables for recursion. Your code is easily fixed by adding another sub query (like i mentioned before). To simulate the initial deck, I’ve removed the library section limiter so it pulls from all tv show libraries (205ms over 406 shows in my VM), and added a limit 50 so it’ll only show the recents from the last 50 shows (I’d imagine with this, they wouldn’t have to use such highish number) -:
select
(case when new_seasons > 1 then series_id else (case when new_items > 1 then season_id else episode_id end) end) as item_id,
(case when new_seasons > 1 then 'multi-season' else (case when new_items > 1 then 'single-season' else 'single-episode' end) end) as type,
new_items,new_seasons,added_at,title from (
select st.title as title, count(1) as new_items, count(distinct ss.id) as new_seasons, max(i.added_at) as added_at, i.id as episode_id, ss.id as season_id, ss.parent_id as series_id
from
metadata_items i
join metadata_items ss on ss.id=i.parent_id
join metadata_items st on st.id=ss.parent_id
join
(
select max(added_at) as max_date, min(added_at) as min_date from
(
select
max(i.added_at) as added_at, ss.parent_id as series_id
from
metadata_items i
join metadata_items ss on ss.id=i.parent_id
join metadata_items st on st.id=ss.parent_id
where i.parent_id not null and
i.metadata_type=4
group by ss.id
order by added_at desc
LIMIT 50
)
) date_range on i.added_at >= date_range.min_date and i.added_at <= date_range.max_date
where i.parent_id not null and
i.metadata_type=4
group by series_id
order by added_at desc
LIMIT 50
);
Now I don’t know why you got all angry about this, but I’m glad to see you learned something. However, I do have one piece of unsolicited advice for you, your reading comprehension is awful, you should probably work on that. Cheers.
@random.server said:
@honkdazzle Yeah, I actually did type out what your initial code (my test library section was 56 not 2), and it was nothing more than an aggregate query based on parent_id. But never mind that. I’m proud of you, not only did you prove my point (that it’s not just a simple group by), you included a code sample for us.You went from simple aggregate queries based on the parent_id to a query that almost did this “To do it in one call, be able to group the resulting data into sets, and show the most recent episode if there was no sets; they’d have to just query a number (how many would probably be dependant on speed) of items, then reduce that number by matching a similar metric (seasons, show).”. So kudos.
I think you are being disingenuous. The query has barely changed. I’ve just edited it to account for bugs or lack of clarity for you to understand. It’s still a single, simple group by. There are no temp tables. Only one join, to work up the chain from episode->season->series id. It’s fast and simple.
I hope that by adjusting the query it became more clear to you why this is a simple long-term solution that works better than just increasing the number of episodes to filter.
My quick code outline (that you couldn’t understand?) was built around Plex’s current method of recent by number, not the date method you keep using, hence the temp tables for recursion.
My original point, repeated ad nauseam, is that using an arbitrarily-long list of episodes is not the right way to solve this problem. You are always going to run into starvation scenarios. It is better to use a fixed date. After all, “recent” is your constraint.
Your code is easily fixed by adding another sub query (like i mentioned before).
There’s nothing wrong with what I posted. I guess I used the wrong column for created_at/added_at. You got me?
To simulate the initial deck, I’ve removed the library section limiter so it pulls from all tv show libraries (205ms over 406 shows in my VM), and added a limit 50 so it’ll only show the recents from the last 50 shows (I’d imagine with this, they wouldn’t have to use such highish number) -:
205ms is extremely slow. I don’t have the time right now to run an explain and figure out how badly you butchered that query or if it’s a platform specific problem, but 205ms is not OK for that query unless it’s performed on item addition and cached.
Now I don’t know why you got all angry about this, but I’m glad to see you learned something. However, I do have one piece of unsolicited advice for you, your reading comprehension is awful, you should probably work on that. Cheers.
I didn’t learn anything except that you’re a really mean, bitter person. I looked around at some of your other posts (especially your bizarro anti-Linux posts) and it made me sad.
Now that we’re off the SQL tangent…
I was just thinking, to the Plex poster’s point earlier about having to change stuff around to accommodate: if it’s the case that the XML file fed to Plex/Web for “recently added” is literally just a list of the last 50 added episodes, and Plex/Web is doing the rest of the processing, I can see why that would take extra coding steps to fix this. It may not just be a query fix, since Plex/Web may be expecting a list of only episode IDs. It still wouldn’t be a lot of work – a list of metadata items is still a list of metadata items – and you’d really just be deleting a couple dozen lines of aggregation code off of Plex/Web. But I get the point now. And this is purely speculation on my part as I don’t know how the internals work exactly.
I can see the desire to want to process the visual display portion separately on Plex/Web. It looks like an architectural decision. So having simple XML APIs to the server make sense. However, there are use cases like “recently added” where aggregation at the source is a more efficient solution than sending large XML files around; yeah, it may violate MVC a little, but to be performant it could and should have a dedicated API call. The problem is that this model breaks down pretty badly when you split Plex/Web onto another network or internet connection and you’re sending a lot of unnecessary XML around. Could there be other places where XML fat could be trimmed?
Edit (much later): I noticed the “recently added” TV section is brought in by the “hubs” API endpoint, which is definitely doing the processing on the server side and bucketizing there before generating the XML. This really is as simple as fixing the query generating it. Too bad nobody will ever read this
Samething happening here. Family always used the recently added to the dashboard to see what I had added since their last visit. With this new version only two or three show up. Not good. I have 450 different series on my server so to go browse or search just to see if I had added one is dumb. I would vote to change it back the way it always worked. Emphasis on the word worked.
Did this get fixed?! I noticed the web interface no longer gets “show starvation” when you dump a lot of stuff in. Either they put in a better query or just cranked up the limit. Either way, I am happy.
Edit: Fixed in PMS 1.4.1!
“(Hubs) Adding many episodes from a single show could swamp Recently Added hubs. (#5687)”
