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)