def parse_prop_clauses(filters: List[Property], team_id: int, prepend: str = "global", table_name: str = "") -> Tuple[str, Dict]: final = [] params: Dict[str, Any] = {"team_id": team_id} if table_name != "": table_name += "." for idx, prop in enumerate(filters): if prop.type == "cohort": cohort = Cohort.objects.get(pk=prop.value) person_id_query, cohort_filter_params = format_filter_query(cohort) params = {**params, **cohort_filter_params} final.append("AND {table_name}distinct_id IN ({clause})".format( table_name=table_name, clause=person_id_query)) elif prop.type == "person": filter_query, filter_params = prop_filter_json_extract( prop, idx, "{}person".format(prepend)) final.append( "AND {table_name}distinct_id IN ({filter_query})".format( filter_query=GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=filter_query), table_name=table_name)) params.update(filter_params) else: filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var="{}properties".format(table_name)) final.append( "{filter_query} AND {table_name}team_id = %(team_id)s".format( table_name=table_name, filter_query=filter_query)) params.update(filter_params) return " ".join(final), params
def parse_prop_clauses(key: str, filters: List[Property], team: Team, prepend: str = "") -> Tuple[str, Dict]: final = "" params: Dict[str, Any] = {} for idx, prop in enumerate(filters): if prop.type == "cohort": cohort = Cohort.objects.get(pk=prop.value) person_id_query, cohort_filter_params = format_filter_query(cohort) params = {**params, **cohort_filter_params} final += "{cond} ({clause}) ".format(cond="AND distinct_id IN", clause=person_id_query) elif prop.type == "person": prepend = "person" filter = "(ep.key = %(k{prepend}_{idx})s) AND (ep.value {operator} %(v{prepend}_{idx})s)".format( idx=idx, operator=get_operator(prop.operator), prepend=prepend ) clause = GET_DISTINCT_IDS_BY_PROPERTY_SQL.format(filters=filter) final += "{cond} ({clause}) ".format(cond="AND distinct_id IN", clause=clause) params.update( {"k{}_{}".format(prepend, idx): prop.key, "v{}_{}".format(prepend, idx): _pad_value(prop.value)} ) else: filter = "(ep.key = %(k{prepend}_{idx})s) AND (ep.value {operator} %(v{prepend}_{idx})s)".format( idx=idx, operator=get_operator(prop.operator), prepend=prepend ) clause = EVENT_PROP_CLAUSE.format(team_id=team.pk, filters=filter) final += "{cond} ({clause}) ".format(cond="AND {key} IN".format(key=key), clause=clause) params.update( {"k{}_{}".format(prepend, idx): prop.key, "v{}_{}".format(prepend, idx): _pad_value(prop.value)} ) return final, params
def parse_prop_clauses( filters: List[Property], team_id: Optional[int], prepend: str = "global", table_name: str = "", allow_denormalized_props: bool = False, filter_test_accounts=False, ) -> Tuple[str, Dict]: final = [] params: Dict[str, Any] = {} if team_id is not None: params["team_id"] = team_id if table_name != "": table_name += "." 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": cohort = Cohort.objects.get(pk=prop.value, team_id=team_id) person_id_query, cohort_filter_params = format_filter_query(cohort) params = {**params, **cohort_filter_params} final.append("AND {table_name}distinct_id IN ({clause})".format( table_name=table_name, clause=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) final.append( "AND {table_name}distinct_id IN ({filter_query})".format( filter_query=GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=filter_query), table_name=table_name)) 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".format(table_name), allow_denormalized_props=allow_denormalized_props, ) final.append( f"{filter_query} AND {table_name}team_id = %(team_id)s" if team_id else filter_query) params.update(filter_params) return " ".join(final), params
def format_filter_query(cohort: Cohort) -> Tuple[str, Dict[str, Any]]: filters = [] params: Dict[str, Any] = {} for group_idx, group in enumerate(cohort.groups): if group.get("action_id"): action = Action.objects.get(pk=group["action_id"], team_id=cohort.team.pk) action_filter_query, action_params = format_action_filter(action) extract_person = "SELECT distinct_id FROM events WHERE uuid IN ({query})".format( query=action_filter_query) params = {**params, **action_params} filters.append("(" + extract_person + ")") elif group.get("properties"): filter = Filter(data=group) for idx, prop in enumerate(filter.properties): prepend = "{}_cohort_group_{}".format(cohort.pk, group_idx) arg = "v{}_{}".format(prepend, idx) operator_clause, value = get_operator(prop, arg) prop_filters = "(ep.key = %(k{prepend}_{idx})s) AND {operator_clause}".format( idx=idx, operator_clause=operator_clause, prepend=prepend) clause = GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=prop_filters, negation="NOT " if prop.operator and "not" in prop.operator else "") filters.append("(" + clause + ")") params.update({ "k{}_{}".format(prepend, idx): prop.key, arg: value }) separator = " OR distinct_id IN " joined_filter = separator.join(filters) person_id_query = CALCULATE_COHORT_PEOPLE_SQL.format(query=joined_filter) return person_id_query, params
def parse_prop_clauses( key: str, filters: List[Property], team: Team, prepend: str = "", json_extract: bool = False ) -> Tuple[str, Dict]: final = "" params: Dict[str, Any] = {} for idx, prop in enumerate(filters): if prop.type == "cohort": cohort = Cohort.objects.get(pk=prop.value) person_id_query, cohort_filter_params = format_filter_query(cohort) params = {**params, **cohort_filter_params} final += "AND distinct_id IN ({clause}) ".format(clause=person_id_query) elif prop.type == "person": filter_query, filter_params = prop_filter_json_extract(prop, idx, "{}person".format(prepend)) final += " AND distinct_id IN ({filter_query})".format( filter_query=GET_DISTINCT_IDS_BY_PROPERTY_SQL.format(filters=filter_query) ) params.update(filter_params) else: filter_query, filter_params = prop_filter_json_extract(prop, idx, prepend) final += " {filter_query} AND team_id = %(team_id)s".format(filter_query=filter_query) params.update(filter_params) return final, params
def parse_prop_clauses(key: str, filters: List[Property], team: Team, prepend: str = "") -> Tuple[str, Dict]: final = "" params: Dict[str, Any] = {} for idx, prop in enumerate(filters): if prop.type == "cohort": cohort = Cohort.objects.get(pk=prop.value) person_id_query, cohort_filter_params = format_filter_query(cohort) params = {**params, **cohort_filter_params} final += "AND distinct_id IN ({clause}) ".format( clause=person_id_query) elif prop.type == "person": prepend = "person" arg = "v{}_{}".format(prepend, idx) operator_clause, value = get_operator(prop, arg) filter = "(ep.key = %(k{prepend}_{idx})s) {and_statement} {operator_clause}".format( idx=idx, and_statement="AND" if operator_clause else "", operator_clause=operator_clause, prepend=prepend, ) clause = GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=filter, negation="NOT " if prop.operator and "not" in prop.operator else "") final += "AND distinct_id IN ({clause}) ".format(clause=clause) params.update({ "k{}_{}".format(prepend, idx): prop.key, arg: value }) else: arg = "v{}_{}".format(prepend, idx) operator_clause, value = get_operator(prop, arg) filter = "(ep.key = %(k{prepend}_{idx})s) {and_statement} {operator_clause}".format( idx=idx, and_statement="AND" if operator_clause else "", operator_clause=operator_clause, prepend=prepend, ) clause = EVENT_PROP_CLAUSE.format(team_id=team.pk, filters=filter) final += "{cond} ({clause}) AND team_id = %(team_id)s ".format( cond="AND {key} {negation}IN".format( key=key, negation="NOT " if prop.operator and "not" in prop.operator else "", ), clause=clause, ) params.update({ "k{}_{}".format(prepend, idx): prop.key, arg: value }) return final, params
def parse_prop_clauses( filters: List[Property], team_id: Optional[int], prepend: str = "global", table_name: str = "", allow_denormalized_props: bool = True, has_person_id_joined: bool = True, person_properties_mode: PersonPropertiesMode = PersonPropertiesMode. USING_SUBQUERY, ) -> Tuple[str, Dict]: final = [] params: Dict[str, Any] = {} if team_id is not None: params["team_id"] = team_id if table_name != "": table_name += "." for idx, prop in enumerate(filters): if prop.type == "cohort": try: cohort = Cohort.objects.get(pk=prop.value, team_id=team_id) except Cohort.DoesNotExist: final.append( "AND 0 = 13") # If cohort doesn't exist, nothing can match else: person_id_query, cohort_filter_params = format_filter_query( cohort, idx) params = {**params, **cohort_filter_params} final.append( "AND {table_name}distinct_id IN ({clause})".format( table_name=table_name, clause=person_id_query)) elif prop.type == "person" and person_properties_mode != PersonPropertiesMode.EXCLUDE: # :TODO: Clean this up by using ClickhousePersonQuery over GET_DISTINCT_IDS_BY_PROPERTY_SQL to have access # to materialized columns # :TODO: (performance) Avoid subqueries whenever possible, use joins instead is_direct_query = person_properties_mode == PersonPropertiesMode.USING_PERSON_PROPERTIES_COLUMN filter_query, filter_params = prop_filter_json_extract( prop, idx, "{}person".format(prepend), prop_var="person_props" if is_direct_query else "properties", allow_denormalized_props=allow_denormalized_props and is_direct_query, ) if is_direct_query: final.append(filter_query) params.update(filter_params) else: final.append( "AND {table_name}distinct_id IN ({filter_query})".format( filter_query=GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=filter_query), table_name=table_name, )) params.update(filter_params) elif prop.type == "element": query, filter_params = filter_element({prop.key: prop.value}, operator=prop.operator, prepend="{}_".format(idx)) if query: final.append(f" AND {query}") params.update(filter_params) elif prop.type == "event": filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var="{}properties".format(table_name), allow_denormalized_props=allow_denormalized_props, ) final.append( f"{filter_query} AND {table_name}team_id = %(team_id)s" if team_id else filter_query) params.update(filter_params) elif prop.type == "group": # :TRICKY: This assumes group properties have already been joined, as in trends query filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var=f"group_properties_{prop.group_type_index}", allow_denormalized_props=False) final.append(filter_query) params.update(filter_params) elif prop.type in ("static-cohort", "precalculated-cohort"): cohort_id = cast(int, prop.value) method = format_static_cohort_query if prop.type == "static-cohort" else format_precalculated_cohort_query filter_query, filter_params = method( cohort_id, idx, prepend=prepend, custom_match_field="person_id") # type: ignore if has_person_id_joined: final.append(f" AND {filter_query}") else: # :TODO: (performance) Avoid subqueries whenever possible, use joins instead subquery = GET_DISTINCT_IDS_BY_PERSON_ID_FILTER.format( filters=filter_query) final.append(f"AND {table_name}distinct_id IN ({subquery})") params.update(filter_params) return " ".join(final), params
def parse_prop_clauses( team_id: int, filters: List[Property], prepend: str = "global", table_name: str = "", allow_denormalized_props: bool = True, has_person_id_joined: bool = True, person_properties_mode: PersonPropertiesMode = PersonPropertiesMode. USING_SUBQUERY, person_id_joined_alias: str = "person_id", group_properties_joined: bool = True, property_operator: PropertyOperatorType = PropertyOperatorType.AND, ) -> Tuple[str, Dict]: final = [] params: Dict[str, Any] = {} if table_name != "": table_name += "." for idx, prop in enumerate(filters): if prop.type == "cohort": try: cohort = Cohort.objects.get(pk=prop.value) except Cohort.DoesNotExist: final.append( f"{property_operator} 0 = 13" ) # If cohort doesn't exist, nothing can match, unless an OR operator is used else: if person_properties_mode == PersonPropertiesMode.USING_SUBQUERY: person_id_query, cohort_filter_params = format_filter_query( cohort, idx) params = {**params, **cohort_filter_params} final.append( f"{property_operator} {table_name}distinct_id IN ({person_id_query})" ) else: person_id_query, cohort_filter_params = format_cohort_subquery( cohort, idx, custom_match_field=f"{person_id_joined_alias}") params = {**params, **cohort_filter_params} final.append(f"{property_operator} {person_id_query}") elif prop.type == "person" and person_properties_mode != PersonPropertiesMode.DIRECT: # :TODO: Clean this up by using ClickhousePersonQuery over GET_DISTINCT_IDS_BY_PROPERTY_SQL to have access # to materialized columns # :TODO: (performance) Avoid subqueries whenever possible, use joins instead is_direct_query = person_properties_mode == PersonPropertiesMode.USING_PERSON_PROPERTIES_COLUMN filter_query, filter_params = prop_filter_json_extract( prop, idx, "{}person".format(prepend), prop_var="person_props" if is_direct_query else "properties", allow_denormalized_props=allow_denormalized_props and is_direct_query, property_operator=property_operator, ) if is_direct_query: final.append(filter_query) params.update(filter_params) else: # Subquery filter here always should be blank as it's the first filter_query = filter_query.replace(property_operator, "", 1) final.append( " {property_operator} {table_name}distinct_id IN ({filter_query})" .format( filter_query=GET_DISTINCT_IDS_BY_PROPERTY_SQL.format( filters=filter_query, GET_TEAM_PERSON_DISTINCT_IDS= get_team_distinct_ids_query(team_id), ), table_name=table_name, property_operator=property_operator, )) params.update(filter_params) elif prop.type == "person" and person_properties_mode == PersonPropertiesMode.DIRECT: # this setting is used to generate the ClickhousePersonQuery SQL. # When using direct mode, there should only be person properties in the entire # property group filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend=f"personquery_{prepend}", allow_denormalized_props=True, transform_expression=lambda column_name: f"argMax(person.{column_name}, _timestamp)", property_operator=property_operator, ) final.append(filter_query) params.update(filter_params) elif prop.type == "element": query, filter_params = filter_element({prop.key: prop.value}, operator=prop.operator, prepend="{}_".format(idx)) if query: final.append(f"{property_operator} {query}") params.update(filter_params) elif prop.type == "event": filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var="{}properties".format(table_name), allow_denormalized_props=allow_denormalized_props, property_operator=property_operator, ) final.append(f" {filter_query}") params.update(filter_params) elif prop.type == "group": if group_properties_joined: filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var=f"group_properties_{prop.group_type_index}", allow_denormalized_props=False, property_operator=property_operator, ) final.append(filter_query) params.update(filter_params) else: # :TRICKY: offer groups support for queries which don't support automatically joining with groups table yet (e.g. lifecycle) filter_query, filter_params = prop_filter_json_extract( prop, idx, prepend, prop_var=f"group_properties", allow_denormalized_props=False) group_type_index_var = f"{prepend}_group_type_index_{idx}" groups_subquery = GET_GROUP_IDS_BY_PROPERTY_SQL.format( filters=filter_query, group_type_index_var=group_type_index_var) final.append( f"{property_operator} {table_name}$group_{prop.group_type_index} IN ({groups_subquery})" ) params.update(filter_params) params[group_type_index_var] = prop.group_type_index elif prop.type in ("static-cohort", "precalculated-cohort"): cohort_id = cast(int, prop.value) method = format_static_cohort_query if prop.type == "static-cohort" else format_precalculated_cohort_query filter_query, filter_params = method( cohort_id, idx, prepend=prepend, custom_match_field=person_id_joined_alias) # type: ignore if has_person_id_joined: final.append(f"{property_operator} {filter_query}") else: # :TODO: (performance) Avoid subqueries whenever possible, use joins instead subquery = GET_DISTINCT_IDS_BY_PERSON_ID_FILTER.format( filters=filter_query, GET_TEAM_PERSON_DISTINCT_IDS=get_team_distinct_ids_query( team_id), ) final.append( f"{property_operator} {table_name}distinct_id IN ({subquery})" ) params.update(filter_params) if final: # remove the first operator return " ".join(final).replace(property_operator, "", 1), params return "", params