def format_action_filter( team_id: int, action: Action, prepend: str = "action", use_loop: bool = False, filter_by_team=True, table_name: str = "", person_properties_mode: PersonPropertiesMode = PersonPropertiesMode. USING_SUBQUERY, ) -> Tuple[str, Dict]: # get action steps params = {"team_id": action.team.pk} if filter_by_team else {} steps = action.steps.all() if len(steps) == 0: # If no steps, it shouldn't match this part of the query return "1=2", {} or_queries = [] for index, step in enumerate(steps): conditions: List[str] = [] # filter element if step.event == AUTOCAPTURE_EVENT: from ee.clickhouse.models.property import filter_element # prevent circular import el_condition, element_params = filter_element( model_to_dict(step), prepend=f"{action.pk}_{index}{prepend}") params = {**params, **element_params} if len(el_condition) > 0: conditions.append(el_condition) # filter event conditions (ie URL) event_conditions, event_params = filter_event( step, f"{action.pk}_{index}{prepend}", index, table_name) params = {**params, **event_params} conditions += event_conditions if step.properties: from ee.clickhouse.models.property import parse_prop_grouped_clauses prop_query, prop_params = parse_prop_grouped_clauses( team_id=team_id, property_group=Filter(data={ "properties": step.properties }).property_groups, prepend=f"action_props_{action.pk}_{step.pk}", table_name=table_name, person_properties_mode=person_properties_mode, ) conditions.append(prop_query.replace("AND", "", 1)) params = {**params, **prop_params} if len(conditions) > 0: or_queries.append(" AND ".join(conditions)) if use_loop: formatted_query = "SELECT uuid FROM events WHERE {} AND team_id = %(team_id)s".format( ") OR uuid IN (SELECT uuid FROM events WHERE team_id = %(team_id)s AND " .join(or_queries)) else: formatted_query = "(({}))".format(") OR (".join(or_queries)) return formatted_query, params
def format_action_filter(action: Action, prepend: str = "action", use_loop: bool = False, filter_by_team=True) -> Tuple[str, Dict]: # get action steps params = {"team_id": action.team.pk} if filter_by_team else {} steps = action.steps.all() if len(steps) == 0: # If no steps, it shouldn't match this part of the query return "1=2", {} or_queries = [] for index, step in enumerate(steps): conditions: List[str] = [] # filter element if step.event == AUTOCAPTURE_EVENT: from ee.clickhouse.models.property import filter_element # prevent circular import el_conditions, element_params = filter_element( model_to_dict(step), "{}_{}{}".format(action.pk, index, prepend)) params = {**params, **element_params} conditions += el_conditions # filter event conditions (ie URL) event_conditions, event_params = filter_event( step, "{}_{}{}".format(action.pk, index, prepend), index) params = {**params, **event_params} conditions += event_conditions if step.properties: from ee.clickhouse.models.property import parse_prop_clauses prop_query, prop_params = parse_prop_clauses( Filter(data={ "properties": step.properties }).properties, team_id=action.team.pk if filter_by_team else None, prepend="action_props_{}_{}".format(action.pk, step.pk), ) conditions.append(prop_query.replace("AND", "", 1)) params = {**params, **prop_params} if len(conditions) > 0: or_queries.append(" AND ".join(conditions)) if use_loop: formatted_query = "SELECT uuid FROM events WHERE {} AND team_id = %(team_id)s".format( ") OR uuid IN (SELECT uuid FROM events WHERE team_id = %(team_id)s AND " .join(or_queries)) else: formatted_query = "(({}))".format(") OR (".join(or_queries)) return formatted_query, params
def _get_props(self, filters: List[Property], allow_denormalized_props: bool = False) -> Tuple[str, Dict]: filter_test_accounts = self._filter.filter_test_accounts team_id = self._team_id table_name = f"{self.EVENT_TABLE_ALIAS}." prepend = "global" final = [] params: Dict[str, Any] = {} if filter_test_accounts: test_account_filters = Team.objects.only( "test_account_filters").get(id=team_id).test_account_filters filters.extend([Property(**prop) for prop in test_account_filters]) for idx, prop in enumerate(filters): if prop.type == "cohort": person_id_query, cohort_filter_params = self._get_cohort_subquery( prop) params = {**params, **cohort_filter_params} final.append(f"AND {person_id_query}") elif prop.type == "person": filter_query, filter_params = prop_filter_json_extract( prop, idx, "{}person".format(prepend), allow_denormalized_props=allow_denormalized_props, prop_var=self._PERSON_PROPERTIES_ALIAS, ) final.append(filter_query) params.update(filter_params) elif prop.type == "element": query, filter_params = filter_element( {prop.key: prop.value}, prepend="{}_".format(idx)) final.append("AND {}".format(query[0])) params.update(filter_params) else: filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var="properties", allow_denormalized_props=allow_denormalized_props, ) final.append(filter_query) params.update(filter_params) return " ".join(final), params