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
