Tournament Mode – SQL Playlist

[Axilla]

I’m not sure if there’s a better place to start this, but I also didn’t want to hijack  Iain‘s thread, so I figured I start a new one.  We’re a very competitive bunch over here when it comes to pinball, but we always seem to pick a lot of the same tables to beat up on each other (our head-to-head scoring system is another thread for another day).  With the ability to use active SQL lists, it’s allowed us to make things a lot more competitive.  I created an active playlist that will give us 20 random tables ordered by the least number of times played to ensure no one will have an advantage of having mastered the table. Here’s what I’m using for our “Grand Tournament” playlist (and I’ll explain as best I can):

“Select g.*, sum(case when d.numplayed is null then 0 else d.numplayed end) as Played
from games g
left join playlistdetails d on g.gameid = d.gameid
where g.visible = 1
group by g.gameid, g.emuid, g.gamename, g.gamefilename
order by Played, random()
limit 20”

1. Since I’m using multiple tables, I prefer to use table aliases so I don’t have to continually type out the table names.  The alias is listed after the table name (the “g” after games in my “from” statement)
2. I didn’t know fields would be required from the games table, so I just bring in what I thought was necessary.
3. To get the number of times played, this information is stored in the PlayListDetails table.  However, since the tables may never have been played, you must use a “left join” which means everything from the table in the previous statement “games” and matching records from the “left join” table “PlayListDetails”.
4. Since there may not be records in the PlayListDetails table, I check for a null value in my “select” statement and replace that with a zero since I know that a null value means it hasn’t been played (I’m sure there is a variant isnull() function that would be a lot simpler than my case statement, but I’m used to writing everything in MSSQL and isnull() is not a valid sqlite function).
5. I then sum up all of those numbers because the PlayListDetails table is grouped by PlayListID.  Tables could exist in multiple playlists, but I simply want all unique tables.
6. Lastly, I order my results by my number of times played count first, then by “random()”.  This way, if I have 100 tables that have been played zero times, it will give us a random selection from those 100.  Once I only have 10 tables left that haven’t been played, this will return me the 10 tables that haven’t been played before in a random order and then 10 tables that have been played once in a random order.

A couple additional notes:

• You could limit this by g.emuid if you only want it for certain emulators.  I only have VP on my cab, so I haven’t done that.
• You could also limit this for the number of played based on which playlist it was played in, but that’s a takes it a step further and I won’t go into that detail right now (we use this to keep the counts based on the group who played, not as a total number of played by table).

 

Explaining technical things has never been one of my strongest suites, so I can probably answer specific questions a lot better than trying to explain this in one long thought that makes sense in my own head.

Thanks again to NailBuster for this awesome software!  Everyone loves the new tournament mode!

You need to login in order to like this post: click here

[NailBuster]

very cool!

I would suggest though to grab * fields from games… there’s plenty of fields like ‘keepdisplays’, ‘mediasearch’ and a few others that may not be obvious to have in the sql result, but are needed….

here’s a math brain ‘teaser’….  i wonder if theres a math equation that uses current date as a random-seed so that everytime you go into the playlist it will be the same result (with randomness) for the current date, tmrw will be a different set but always same for the 24 hours….. hmmmm.

that would be interesting ;)

thanks for sharing… and Keep them coming!!!  

 

You need to login in order to like this post: click here

[Axilla]

I use the g.* on my setup, but I changed it last minute when posting because I didn’t want to be more confusing with my select/group by then I already might have been.  I’ll update my post.

As far as your brain teaser, that is indeed a great idea.  I’m going to have to think on that one for a minute.  We pretty much stay on that same playlist, but it could be very useful on miscues from my friends that are still used to PBX and exit prematurely.

You need to login in order to like this post: click here

[Author]

Posts