def _build_step_query(self, entity: Entity, index: int, entity_name: str,
step_prefix: str) -> str:
filters = self._build_filters(entity, index)
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = entity.get_action()
for action_step in action.steps.all():
if entity_name not in self.params[entity_name]:
self.params[entity_name].append(action_step.event)
action_query, action_params = format_action_filter(
team_id=self._team.pk,
action=action,
prepend=f"{entity_name}_{step_prefix}step_{index}")
if action_query == "":
return ""
self.params.update(action_params)
content_sql = "{actions_query} {filters}".format(
actions_query=action_query,
filters=filters,
)
else:
if entity.id not in self.params[entity_name]:
self.params[entity_name].append(entity.id)
event_param_key = f"{entity_name}_{step_prefix}event_{index}"
self.params[event_param_key] = entity.id
content_sql = f"event = %({event_param_key})s {filters}"
return content_sql
def _get_entity_query(event_id: Optional[str], action_id: Optional[int],
team_id: int,
group_idx: int) -> Tuple[str, Dict[str, str]]:
if event_id:
return "event = %(event)s", {"event": event_id}
elif action_id:
action = Action.objects.get(pk=action_id, team_id=team_id)
action_filter_query, action_params = format_action_filter(
team_id=team_id,
action=action,
prepend="_{}_action".format(group_idx))
return action_filter_query, action_params
else:
raise ValidationError("Cohort query requires action_id or event_id")
def _get_events_for_action(action: Action) -> List[MockEvent]:
formatted_query, params = format_action_filter(team_id=action.team_id,
action=action,
prepend="")
query = f"""
SELECT
events.uuid,
events.distinct_id
FROM events
WHERE {formatted_query}
AND events.team_id = %(team_id)s
ORDER BY events.timestamp DESC
"""
events = sync_execute(query, {"team_id": action.team_id, **params})
return [MockEvent(str(uuid), distinct_id) for uuid, distinct_id in events]
def _get_entity_query(self, entity: Entity):
prepend = self._event_query_type
if entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=entity.id)
action_query, params = format_action_filter(team_id=self._team_id,
action=action,
prepend=prepend,
use_loop=False)
condition = action_query
elif entity.type == TREND_FILTER_TYPE_EVENTS:
condition = f"{self.EVENT_TABLE_ALIAS}.event = %({prepend}_event)s"
params = {f"{prepend}_event": entity.id}
else:
condition = f"{self.EVENT_TABLE_ALIAS}.event = %({prepend}_event)s"
params = {f"{prepend}_event": PAGEVIEW_EVENT}
return condition, params
def count(self, request: request.Request, **kwargs) -> Response:
action = self.get_object()
query, params = format_action_filter(team_id=action.team_id,
action=action)
if query == "":
return Response({"count": 0})
results = sync_execute(
"SELECT count(1) FROM events WHERE team_id = %(team_id)s AND timestamp < %(before)s AND timestamp > %(after)s AND {}"
.format(query),
{
"team_id":
action.team_id,
"before":
now().strftime("%Y-%m-%d %H:%M:%S.%f"),
"after":
(now() -
relativedelta(months=3)).strftime("%Y-%m-%d %H:%M:%S.%f"),
**params,
},
)
return Response({"count": results[0][0]})
def _query_events_list(self,
filter: Filter,
team: Team,
request: request.Request,
long_date_from: bool = False,
limit: int = 100) -> List:
limit += 1
limit_sql = "LIMIT %(limit)s"
order = "DESC" if self._parse_order_by(
self.request)[0] == "-timestamp" else "ASC"
conditions, condition_params = determine_event_conditions(
team,
{
"after": (now() - timedelta(days=1)).isoformat(),
"before": (now() + timedelta(seconds=5)).isoformat(),
**request.GET.dict(),
},
long_date_from,
)
prop_filters, prop_filter_params = parse_prop_grouped_clauses(
team_id=team.pk,
property_group=filter.property_groups,
has_person_id_joined=False)
if request.GET.get("action_id"):
try:
action = Action.objects.get(pk=request.GET["action_id"],
team_id=team.pk)
except Action.DoesNotExist:
return []
if action.steps.count() == 0:
return []
action_query, params = format_action_filter(team_id=team.pk,
action=action)
prop_filters += " AND {}".format(action_query)
prop_filter_params = {**prop_filter_params, **params}
if prop_filters != "":
return sync_execute(
SELECT_EVENT_BY_TEAM_AND_CONDITIONS_FILTERS_SQL.format(
conditions=conditions,
limit=limit_sql,
filters=prop_filters,
order=order),
{
"team_id": team.pk,
"limit": limit,
**condition_params,
**prop_filter_params
},
)
else:
return sync_execute(
SELECT_EVENT_BY_TEAM_AND_CONDITIONS_SQL.format(
conditions=conditions, limit=limit_sql, order=order),
{
"team_id": team.pk,
"limit": limit,
**condition_params
},
)
def get_actions_query(self) -> Tuple[str, Dict[str, Any]]:
if self._entity.type == TREND_FILTER_TYPE_ACTIONS:
return format_action_filter(team_id=self._team_id,
action=self._entity.get_action())
else:
return "event = %(event)s", {"event": self._entity.id}
def get_query(self) -> Tuple[str, Dict, Callable]:
interval_annotation = get_trunc_func_ch(self.filter.interval)
num_intervals, seconds_in_interval, round_interval = get_time_diff(
self.filter.interval, self.filter.date_from, self.filter.date_to, self.team_id
)
_, parsed_date_to, date_params = parse_timestamps(filter=self.filter, team_id=self.team_id)
props_to_filter = self.filter.property_groups.combine_property_group(
PropertyOperatorType.AND, self.entity.property_groups
)
outer_properties = self.column_optimizer.property_optimizer.parse_property_groups(props_to_filter).outer
prop_filters, prop_filter_params = parse_prop_grouped_clauses(
team_id=self.team_id,
property_group=outer_properties,
table_name="e",
person_properties_mode=PersonPropertiesMode.USING_PERSON_PROPERTIES_COLUMN,
)
aggregate_operation, _, math_params = process_math(self.entity, self.team, event_table_alias="e")
action_query = ""
action_params: Dict = {}
if self.entity.type == TREND_FILTER_TYPE_ACTIONS:
action = self.entity.get_action()
action_query, action_params = format_action_filter(team_id=self.team_id, action=action, table_name="e")
self.params = {
**self.params,
**math_params,
**prop_filter_params,
**action_params,
"event": self.entity.id,
"key": self.filter.breakdown,
**date_params,
}
breakdown_filter_params = {
"parsed_date_from": date_from_clause(interval_annotation, round_interval),
"parsed_date_to": parsed_date_to,
"actions_query": "AND {}".format(action_query) if action_query else "",
"event_filter": "AND event = %(event)s" if not action_query else "",
"filters": prop_filters if props_to_filter.values else "",
}
_params, _breakdown_filter_params = {}, {}
if self.filter.breakdown_type == "cohort":
_params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_cohort_params()
else:
_params, breakdown_filter, _breakdown_filter_params, breakdown_value = self._breakdown_prop_params(
"count(*)" if self.entity.math == "dau" else aggregate_operation, math_params,
)
if len(_params["values"]) == 0:
# If there are no breakdown values, we are sure that there's no relevant events, so instead of adjusting
# a "real" SELECT for this, we only include the below dummy SELECT.
# It's a drop-in replacement for a "real" one, simply always returning 0 rows.
# See https://github.com/PostHog/posthog/pull/5674 for context.
return (
"SELECT [now()] AS date, [0] AS data, '' AS breakdown_value LIMIT 0",
{},
lambda _: [],
)
person_join_condition, person_join_params = self._person_join_condition()
groups_join_condition, groups_join_params = GroupsJoinQuery(
self.filter, self.team_id, self.column_optimizer
).get_join_query()
self.params = {**self.params, **_params, **person_join_params, **groups_join_params}
breakdown_filter_params = {**breakdown_filter_params, **_breakdown_filter_params}
if self.filter.display in TRENDS_DISPLAY_BY_VALUE:
breakdown_filter = breakdown_filter.format(**breakdown_filter_params)
content_sql = BREAKDOWN_AGGREGATE_QUERY_SQL.format(
breakdown_filter=breakdown_filter,
person_join=person_join_condition,
groups_join=groups_join_condition,
aggregate_operation=aggregate_operation,
breakdown_value=breakdown_value,
)
time_range = enumerate_time_range(self.filter, seconds_in_interval)
return (
content_sql,
self.params,
self._parse_single_aggregate_result(self.filter, self.entity, {"days": time_range}),
)
else:
breakdown_filter = breakdown_filter.format(**breakdown_filter_params)
if self.entity.math in [WEEKLY_ACTIVE, MONTHLY_ACTIVE]:
active_user_params = get_active_user_params(self.filter, self.entity, self.team_id)
conditions = BREAKDOWN_ACTIVE_USER_CONDITIONS_SQL.format(
**breakdown_filter_params, **active_user_params
)
inner_sql = BREAKDOWN_ACTIVE_USER_INNER_SQL.format(
breakdown_filter=breakdown_filter,
person_join=person_join_condition,
groups_join=groups_join_condition,
aggregate_operation=aggregate_operation,
interval_annotation=interval_annotation,
breakdown_value=breakdown_value,
conditions=conditions,
GET_TEAM_PERSON_DISTINCT_IDS=get_team_distinct_ids_query(self.team_id),
**active_user_params,
**breakdown_filter_params,
)
elif self.filter.display == TRENDS_CUMULATIVE and self.entity.math == "dau":
inner_sql = BREAKDOWN_CUMULATIVE_INNER_SQL.format(
breakdown_filter=breakdown_filter,
person_join=person_join_condition,
groups_join=groups_join_condition,
aggregate_operation=aggregate_operation,
interval_annotation=interval_annotation,
breakdown_value=breakdown_value,
**breakdown_filter_params,
)
else:
inner_sql = BREAKDOWN_INNER_SQL.format(
breakdown_filter=breakdown_filter,
person_join=person_join_condition,
groups_join=groups_join_condition,
aggregate_operation=aggregate_operation,
interval_annotation=interval_annotation,
breakdown_value=breakdown_value,
)
breakdown_query = BREAKDOWN_QUERY_SQL.format(
interval=interval_annotation, num_intervals=num_intervals, inner_sql=inner_sql,
)
self.params.update(
{"seconds_in_interval": seconds_in_interval, "num_intervals": num_intervals,}
)
return breakdown_query, self.params, self._parse_trend_result(self.filter, self.entity)