filtering versus player type

[SaiYeN]

So i want to filter for only hands played against certain players (like other TAGs). I saw this post: viewtopic.php?f=45&t=8028&p=94574#p94574 and thought maybe something similar to that can be done but instead of checking if the player names match we'll check for something else. I was thinking using the auto-rate for this. For instance i make up a bunch of autorate rules to define a TAG and check for the player.val_icon (the autorate value for the player) to match.

so teh filter expression would be something like this:
( 0 < (SELECT count(hhps2.id_hand) FROM holdem_hand_player_statistics hhps2 WHERE hhps2.id_hand = holdem_hand_player_statistics.id_hand AND hhps2.val_icon = (SELECT p.val_icon FROM player p WHERE val_icon='x')) )

Two specific scenarios i am interested in is
a) when I steal and have two TAGs in the blinds
b) 3bet pots versus other TAGs.

So along with checking the val_icon matches we check for the player(s) to have posted a blind and to have called/made a 3bet.

Can anyone help me out? I dont know enough about SQL to create the filter expression or if it is even possible to do.

[kraada]

This is a bit complicated so I can't put it together for you off the top of my head. However, I'll make a note to work on it and get back to you once I have the answer for you

[carlos50]

Any news on this one?

It would be very useful

[WhiteRider]

Kraada is off for a couple of weeks but I'm sure he'll get back to you when he gets back and gets caught up.

[Stally]

i was also wondering if there was any progress on this?

[WhiteRider]

I'm not sure - Kraada is off for the rest of the day. He must have missed this post when he got back from holiday but I'm sure he'll see it tomorrow.

[kraada]

There are a few custom reports on my list to work on, this is one of them. Once I have it working for you, I'll post it here. Things got a little hectic for a while but hopefully I'll have time this week to take care of it.

[Stally]

hello, bumping this topic to see if any work had been done on it? sorry to nag.

[WhiteRider]

I'm not sure whether Kraada got anywhere with this yet - he's off today but I'm sure he'll tell you where he's up to tomorrow.

[kraada]

Some more recent analysis for related reports makes this filter a lot more easily doable. This filter could apply to either the Holdem Cash Hand or Holdem Cash Player Statistics section. Given that you want overall stats as you say, I'm going to assume we're looking at the HCPS section.

You want to start with a filter like this:

holdem_hand_player_statistics.id_hand in (SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X)

If you use that alone in a report that just had Player Name and VP$IP, it would show you all the names of players and their VP$IP in hands where someone with val_icon = X dealt in.*

Let me break it down for you. The report is set to now only look at specific hands. Which hands? The hands where id_hand is among the results from a certain query. That query is:

SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X

In the query we are joining to the player table on id_player and then making sure that the player's icon is X. Thus if there was a hand with no player who had val_icon of X, it would not come back from this query. Since all stats in this custom report are generated only from hands in the list, we'll see whatever stats only from hands involving players with val_icon of X.

You can then narrow things down further using this rubric as a starting point.

For example, you wanted to analyze stats when there were TAGs in the blinds. To modify the above to require the other TAGs to be in the blinds, change the filter to this:

holdem_hand_player_statistics.id_hand in (SELECT holdem_hand_player_statistics.id_hand from player, holdem_hand_player_statistics WHERE player.id_player = holdem_hand_player_statistics.id_player and player.val_icon = X and holdem_hand_player_statistics.flg_blind_b)

For clarity's sake, the changes from the rubric were bolded. What did we add? We added another condition that in the hand in question the player with val_icon X must also be in the big blind (flg_blind_b).

Now if we run the report with, say, our player name and attempt to steal, we'll see our attempt to steal percentage only for those times a player with val_icon X is in the big blind. To make it for times when there are TAGs in both blinds, add another filter, identical to the above except using flg_blind_s - then you'll filter first for hands with TAGs in the big blind, then for hands with TAGs in the small blind, and the result will be hands with TAGs in both blinds.

You also wanted "3bet pots versus other TAGs". This is going to be a bit more work as you're going to need to clarify exactly what you mean by this. Who is doing the 3betting? Does the hand need to reach a flop? But once you clarify exactly what you mean here, the above rubric should help. You may need to add another filter for hands in which you have done something, depending on exactly what you want.

To refer to yourself replace:
player.val_icon = X
with
player.id_player::int = (SELECT setting_value::int from settings where setting_name = 'my_player_id')

Then you can also limit to only hands in which you've done certain things. Please note that using filters like these currently are incompatible with the simple filters. This is something we are working to fix, although I don't know exactly at what point the fix will become available.



*You can actually change the icon numbers around based on the value in your Settings tab, so you may need to experiment in order to figure out what is the val_icon for TAGs in your setup. It's going to be a number between 1 and 14 in any case, so if you know the name of one TAG and run the query with that number and sort by Player Name, you should be able to go through the options reasonably quickly and figure out which number you want. I'm going to use X through out in order to keep things suitably generic.