PDA

View Full Version : Want to get ALL hands that follow a certain pattern



guy101
09-28-2008, 12:10 PM
I'm trying to run a report on a getting ALL hands (observed hands included) that follow a certain pattern, for instance:

guy in position calls pre-flop raise/ calls flop bet / calls turn bet / raises river bet

the filter only seems to work for hands that selected player does the above, not if any hand has the following actions...

how do I do this, can hold'em manager do this?

morny
09-29-2008, 12:26 PM
You cant do this with HM filters currently as you can only filter things based on your actions not all actions although we will implement something like this at a later stage in the Players TAB

cantona
10-20-2008, 08:35 PM
Not sure if this would work, but will suggest it as I have done something similar with PO in the past and it may help.

Copy database, and merge all player alias names, then run report.

Rvg72
10-23-2008, 02:46 PM
close Hm and go into the holdemmanager.config and set LogDatabaseCommands = True. Then launch HM and run the report you want. Make sure you have the stats and report type you want. Let's say you want to see the persons hand value at showdown with the line of Check Call flop, Check Raise turn and Bet river. I set those filters for myself and choose the Hand at Showdown Report. I also get rid of all the stats except for Won hand% and $ for this particular example.

After running it close down HM and open the dbtrace.log file in your logs folder and go to the bottom and you will see the query.

In my case it looks something like this:

select (case when river.madehandvalue between -1 and -1 then ' 0) unknown' when river.madehandvalue between 0 and 11 then ' 1) high card' when river.madehandvalue between 20 and 39 then ' 2) one pair' when river.madehandvalue between 40 and 59 then ' 3) two pair' when river.madehandvalue between 60 and 79 then ' 4) three of a kind' when river.madehandvalue between 80 and 99 then ' 5) straight' when river.madehandvalue between 100 and 119 then ' 6) flush' when river.madehandvalue between 120 and 139 then ' 7) full house' when river.madehandvalue between 140 and 159 then ' 8) four of a kind' when river.madehandvalue between 160 and 179 then ' 9) straight flush' end) as "rivermadehandvalue",count(ph.*) as totalhands,sum(case when ph.netamountwon > 0 then 1 else 0 end) as wonhand,sum(ph.netamountwon)/100.0 as netamountwon from playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) left join playerhandsriver_hero river on ph.playerhand_id = river.playerhand_id where (ph.player_id = 1243) and (ph.gametype_id = 1 or ph.gametype_id = 2 or ph.gametype_id = 3 or ph.gametype_id = 4 or ph.gametype_id = 5 or ph.gametype_id = 6 or ph.gametype_id = 7 or ph.gametype_id = 8 or ph.gametype_id = 9 or ph.gametype_id = 10 or ph.gametype_id = 11 or ph.gametype_id = 17 or ph.gametype_id = 18 or ph.gametype_id = 19 or ph.gametype_id = 20 or ph.gametype_id = 21 or ph.gametype_id = 22 or ph.gametype_id = 35 or ph.gametype_id = 36 or ph.gametype_id = 39 or ph.gametype_id = 40 or ph.gametype_id = 41 or ph.gametype_id = 42 or ph.gametype_id = 49 or ph.gametype_id = 50 or ph.gametype_id = 51 or ph.gametype_id = 52 or ph.gametype_id = 53 or ph.gametype_id = 54 or ph.gametype_id = 62 or ph.gametype_id = 63 or ph.gametype_id = 68 or ph.gametype_id = 69 or ph.gametype_id = 71 or ph.gametype_id = 72 or ph.gametype_id = 73 or ph.gametype_id = 74 or ph.gametype_id = 75 or ph.gametype_id = 77 or ph.gametype_id = 78 or ph.gametype_id = 79 or ph.gametype_id = 81 or ph.gametype_id = 82 or ph.gametype_id = 83 or ph.gametype_id = 84 or ph.gametype_id = 85 or ph.gametype_id = 86 or ph.gametype_id = 87 or ph.gametype_id = 88 or ph.gametype_id = 89) and ph.flopplayeractiontype_id in (11) and ph.turnplayeractiontype_id in (21) and ph.riverplayeractiontype_id in (44) and maxstreetseen=4 group by (case when river.madehandvalue between -1 and -1 then ' 0) unknown' when river.madehandvalue between 0 and 11 then ' 1) high card' when river.madehandvalue between 20 and 39 then ' 2) one pair' when river.madehandvalue between 40 and 59 then ' 3) two pair' when river.madehandvalue between 60 and 79 then ' 4) three of a kind' when river.madehandvalue between 80 and 99 then ' 5) straight' when river.madehandvalue between 100 and 119 then ' 6) flush' when river.madehandvalue between 120 and 139 then ' 7) full house' when river.madehandvalue between 140 and 159 then ' 8) four of a kind' when river.madehandvalue between 160 and 179 then ' 9) straight flush' end) order by netamountwon desc

Now you need to make 3 changes.

1) Remove _hero wherever you see it since that is using the optimized tables which do not work if you want to query for everyone.
2) Remove "where (ph.player_id = 1243)" or whatever yours says so that it no longer reports just on your player id.
3) Remove "join players pl on (pl.player_id = ph.player_id) " it just isn't needed any more so why make it do more work

So now we have

select (case when river.madehandvalue between -1 and -1 then ' 0) unknown' when river.madehandvalue between 0 and 11 then ' 1) high card' when river.madehandvalue between 20 and 39 then ' 2) one pair' when river.madehandvalue between 40 and 59 then ' 3) two pair' when river.madehandvalue between 60 and 79 then ' 4) three of a kind' when river.madehandvalue between 80 and 99 then ' 5) straight' when river.madehandvalue between 100 and 119 then ' 6) flush' when river.madehandvalue between 120 and 139 then ' 7) full house' when river.madehandvalue between 140 and 159 then ' 8) four of a kind' when river.madehandvalue between 160 and 179 then ' 9) straight flush' end) as "rivermadehandvalue",count(ph.*) as totalhands,sum(case when ph.netamountwon > 0 then 1 else 0 end) as wonhand,sum(ph.netamountwon)/100.0 as netamountwon from playerhandscashkeycolumns ph left join playerhandsriver river on ph.playerhand_id = river.playerhand_id where (ph.gametype_id = 1 or ph.gametype_id = 2 or ph.gametype_id = 3 or ph.gametype_id = 4 or ph.gametype_id = 5 or ph.gametype_id = 6 or ph.gametype_id = 7 or ph.gametype_id = 8 or ph.gametype_id = 9 or ph.gametype_id = 10 or ph.gametype_id = 11 or ph.gametype_id = 17 or ph.gametype_id = 18 or ph.gametype_id = 19 or ph.gametype_id = 20 or ph.gametype_id = 21 or ph.gametype_id = 22 or ph.gametype_id = 35 or ph.gametype_id = 36 or ph.gametype_id = 39 or ph.gametype_id = 40 or ph.gametype_id = 41 or ph.gametype_id = 42 or ph.gametype_id = 49 or ph.gametype_id = 50 or ph.gametype_id = 51 or ph.gametype_id = 52 or ph.gametype_id = 53 or ph.gametype_id = 54 or ph.gametype_id = 62 or ph.gametype_id = 63 or ph.gametype_id = 68 or ph.gametype_id = 69 or ph.gametype_id = 71 or ph.gametype_id = 72 or ph.gametype_id = 73 or ph.gametype_id = 74 or ph.gametype_id = 75 or ph.gametype_id = 77 or ph.gametype_id = 78 or ph.gametype_id = 79 or ph.gametype_id = 81 or ph.gametype_id = 82 or ph.gametype_id = 83 or ph.gametype_id = 84 or ph.gametype_id = 85 or ph.gametype_id = 86 or ph.gametype_id = 87 or ph.gametype_id = 88 or ph.gametype_id = 89) and ph.flopplayeractiontype_id in (11) and ph.turnplayeractiontype_id in (21) and ph.riverplayeractiontype_id in (44) and maxstreetseen=4 group by (case when river.madehandvalue between -1 and -1 then ' 0) unknown' when river.madehandvalue between 0 and 11 then ' 1) high card' when river.madehandvalue between 20 and 39 then ' 2) one pair' when river.madehandvalue between 40 and 59 then ' 3) two pair' when river.madehandvalue between 60 and 79 then ' 4) three of a kind' when river.madehandvalue between 80 and 99 then ' 5) straight' when river.madehandvalue between 100 and 119 then ' 6) flush' when river.madehandvalue between 120 and 139 then ' 7) full house' when river.madehandvalue between 140 and 159 then ' 8) four of a kind' when river.madehandvalue between 160 and 179 then ' 9) straight flush' end) order by netamountwon desc

Next copy that into pgadmin and run the query and wait... it might take a very long time. Mines running now. Part 2 of this article when it finishes :)