def _retrieve_people(self, target_entity: Entity, filter: StickinessFilter, team: Team) -> ReturnDict: parsed_date_from, parsed_date_to, _ = parse_timestamps(filter=filter, team_id=team.pk) prop_filters, prop_filter_params = parse_prop_clauses( filter.properties, team.pk) entity_sql, entity_params = self._format_entity_filter( entity=target_entity) trunc_func = get_trunc_func_ch(filter.interval) params: Dict = { "team_id": team.pk, **prop_filter_params, "stickiness_day": filter.selected_interval, **entity_params, "offset": filter.offset, } content_sql = STICKINESS_PEOPLE_SQL.format( entity_filter=entity_sql, parsed_date_from=parsed_date_from, parsed_date_to=parsed_date_to, filters=prop_filters, trunc_func=trunc_func, ) people = sync_execute( PEOPLE_SQL.format( content_sql=content_sql, query="", latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")), params, ) return ClickhousePersonSerializer(people, many=True).data
def _calculate_stickiness_entity_people(self, team: Team, entity: Entity, filter: Filter, stickiness_day: int): parsed_date_from, parsed_date_to = parse_timestamps(filter=filter) prop_filters, prop_filter_params = parse_prop_clauses( filter.properties, team.pk) entity_sql, entity_params = self._format_entity_filter(entity=entity) params: Dict = { "team_id": team.pk, **prop_filter_params, "stickiness_day": stickiness_day, **entity_params, "offset": filter.offset, } content_sql = STICKINESS_PEOPLE_SQL.format( entity_filter=entity_sql, parsed_date_from=(parsed_date_from or ""), parsed_date_to=(parsed_date_to or ""), filters="{filters}".format( filters=prop_filters) if filter.properties else "", ) people = sync_execute( PEOPLE_SQL.format( content_sql=content_sql, query="", latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")), params, ) serialized_people = ClickhousePersonSerializer(people, many=True).data return serialized_people
def retrieve(self, request, pk=None): if not endpoint_enabled(CH_PERSON_ENDPOINT, request.user.distinct_id): return super().retrieve(request, pk) qres = sync_execute(PEOPLE_SQL.format(content_sql=[pk]), {"offset": 0}) res = ClickhousePersonSerializer(qres[0]).data if len(qres) > 0 else [] return Response(res)
def retrieve_stickiness_people(target_entity: Entity, filter: StickinessFilter, team: Team) -> ReturnDict: content_sql, params = _process_content_sql(target_entity, filter, team) people = sync_execute( PEOPLE_SQL.format(content_sql=content_sql, query="", latest_person_sql=GET_LATEST_PERSON_SQL.format(query="")), params, ) return ClickhousePersonSerializer(people, many=True).data
def _ch_filter_request(self, request: Request, team: Team) -> List: result = [] queryset_category_pass = "" category = request.query_params.get("category") if category == "identified": queryset_category_pass = "******" elif category == "anonymous": queryset_category_pass = "******" if request.GET.get("id"): people = request.GET["id"].split(",") result = sync_execute(PEOPLE_SQL.format(content_sql=people), {"offset": 0}) else: result = sync_execute( PEOPLE_BY_TEAM_SQL.format(filters=queryset_category_pass), { "offset": 0, "team_id": team.pk }, ) # if request.GET.get("search"): # parts = request.GET["search"].split(" ") # contains = [] # for part in parts: # if ":" in part: # queryset = queryset.filter(properties__has_key=part.split(":")[1]) # else: # contains.append(part) # queryset = queryset.filter( # Q(properties__icontains=" ".join(contains)) # | Q(persondistinctid__distinct_id__icontains=" ".join(contains)) # ).distinct("id") # if request.GET.get("cohort"): # queryset = queryset.filter(cohort__id=request.GET["cohort"]) # if request.GET.get("properties"): # queryset = queryset.filter( # Filter(data={"properties": json.loads(request.GET["properties"])}).properties_to_Q(team_id=team.pk) # ) return result
def _ch_filter_request(self, request: Request, team: Team) -> List: result = [] all_filters = "" params = {"offset": 0, "team_id": team.pk} category = request.query_params.get("category") if category == "identified": all_filters += "AND is_identified = 1 " elif category == "anonymous": all_filters += "AND is_identified = 0 " if request.GET.get("search"): parts = request.GET["search"].split(" ") contains = [] for idx, part in enumerate(parts): if ":" in part: key_query_filter = """ AND person_id IN ( SELECT id FROM persons_properties_up_to_date_view WHERE key = %(person_{idx})s ) """.format(idx=idx) all_filters += key_query_filter params = { **params, "person_{idx}".format(idx=idx): part.split(":")[1] } else: contains.append(part) for idx, search in enumerate(contains): search_query_filter = """ AND person_id IN ( SELECT id FROM person WHERE properties LIKE %({arg})s AND team_id = %(team_id)s ) OR person_id IN ( SELECT person_id FROM person_distinct_id WHERE distinct_id LIKE %({arg})s AND team_id = %(team_id)s ) """.format(arg="search_{idx}".format(idx=idx)) all_filters += search_query_filter params = { **params, "search_{idx}".format(idx=idx): "%{}%".format(search) } if request.GET.get("cohort"): cohort_id = request.GET["cohort"] cohort = Cohort.objects.get(pk=cohort_id) cohort_query, cohort_params = format_filter_query(cohort) cohort_query_filter = """ AND person_id IN ( SELECT person_id FROM person_distinct_id WHERE distinct_id IN ( {clause} ) ) """.format(clause=cohort_query) all_filters += cohort_query_filter params = {**params, **cohort_params} # if request.GET.get("properties"): # pass if request.GET.get("id"): people = request.GET["id"].split(",") result = sync_execute(PEOPLE_SQL.format(content_sql=people), {"offset": 0}) else: result = sync_execute( PEOPLE_BY_TEAM_SQL.format(filters=all_filters), params, ) return result