by kraada » Mon Sep 07, 2009 12:15 pm
I answered your question via PM but just to put things on the record for everyone else, I thought I'd post here.
phonix4 was looking for a custom report that showed only players that have hands imported from them today, but he wanted the report to show their lifetime statistics.
So you won't be able to filter using the regular Simple Filters on the custom report tab as that will show you only data from today - so you wouldn't have lifetime statistics.
Instead, you need to create a custom report and add this as a filter:
player.id_player IN (SELECT p.id_player FROM player p, holdem_hand_player_statistics hhps WHERE p.id_player = hhps.id_player AND hhps.date_played::date = localtimestamp::date)
What's going on here? We're filter for times the id_player (that is, the unique identifier associated with each player) is in a certain list. This will limit players to a certain group but won't limit hands later. What are we looking for id_player to be in? We built it as a subquery:
SELECT p.id_player FROM player p, holdem_hand_player_statistics hhps WHERE p.id_player = hhps.id_player AND hhps.date_played::date = localtimestamp::date
So anytime the player ID matches one of the p.id_player values, we see data for that player. We also need the holdem_hand_player_statistics table as that table is the one we are going to use to see if the player played any hands today. So we also select from a holdem_hand_player_statistics table (called hhps). We make sure that the player_ids match (p.id_player = hhps.id_player) because we want data from the same player. Then we make one more check:
hhps.date_played::date = localtimestamp::date
This is checking that the date_played field in the hhps table when turned into a date (e.g. 9/5/2009) matches the localtimestamp when turned into a date. localtimestamp is a reserved local variable that is always available in PostgreSQL and it always returns the current time right now. So when we cast it as a date, it will return today's date.
Putting everything back together, the subquery pulls out the id_player values for players who played today, and the whole query tests for whether someone's player ID is in that list. If it is, we display them. If it isn't, we don't.
I've attached this report (and uploaded it to the repository) and have the current version just display a player's name and the number of hands we have on the player. You can of course add any other data you would like to see.
- Attachments
-
- Today's Players.zip
- This report shows lifetime data only for players who have played today.
- (694 Bytes) Downloaded 179 times