def actor_query(self, limit_actors: Optional[bool] = True):
if not self._filter.correlation_person_entity:
raise ValidationError("No entity for persons specified")
assert isinstance(self._filter.correlation_person_entity, Entity)
funnel_persons_query, funnel_persons_params = self._funnel_correlation.get_funnel_actors_cte(
)
prop_filters = self._filter.correlation_person_entity.property_groups
# TRICKY: We use "events" as an alias here while the eventquery uses "e" by default
event_query = FunnelEventQuery(self._filter, self._team.pk)
event_query.EVENT_TABLE_ALIAS = "events"
prop_query, prop_params = event_query._get_prop_groups(prop_filters)
conversion_filter = (
f'AND actors.steps {"=" if self._filter.correlation_persons_converted else "<>"} target_step'
if self._filter.correlation_persons_converted is not None else "")
event_join_query = self._funnel_correlation._get_events_join_query()
recording_event_select_statement = (
", any(actors.matching_events) AS matching_events"
if self._filter.include_recordings else "")
query = f"""
WITH
funnel_actors as ({funnel_persons_query}),
toDateTime(%(date_to)s) AS date_to,
toDateTime(%(date_from)s) AS date_from,
%(target_step)s AS target_step,
%(funnel_step_names)s as funnel_step_names
SELECT
actors.actor_id AS actor_id
{recording_event_select_statement}
FROM events AS event
{event_join_query}
AND event.event = %(target_event)s
{conversion_filter}
{prop_query}
GROUP BY actor_id
ORDER BY actor_id
{"LIMIT %(limit)s" if limit_actors else ""}
{"OFFSET %(offset)s" if limit_actors else ""}
"""
params = {
**funnel_persons_params,
**prop_params,
"target_event": self._filter.correlation_person_entity.id,
"funnel_step_names": [entity.id for entity in self._filter.events],
"target_step": len(self._filter.entities),
"limit": self._filter.correlation_person_limit,
"offset": self._filter.correlation_person_offset,
}
return query, params
def _get_inner_event_query(self,
entities=None,
entity_name="events",
skip_entity_filter=False,
skip_step_filter=False) -> str:
entities_to_use = entities or self._filter.entities
event_query, params = FunnelEventQuery(
filter=self._filter,
team=self._team,
extra_fields=self._extra_event_fields,
extra_event_properties=self._extra_event_properties,
).get_query(entities_to_use,
entity_name,
skip_entity_filter=skip_entity_filter)
self.params.update(params)
if skip_step_filter:
steps_conditions = "1=1"
else:
steps_conditions = self._get_steps_conditions(
length=len(entities_to_use))
all_step_cols: List[str] = []
for index, entity in enumerate(entities_to_use):
step_cols = self._get_step_col(entity, index, entity_name)
all_step_cols.extend(step_cols)
for exclusion_id, entity in enumerate(self._filter.exclusions):
step_cols = self._get_step_col(entity, entity.funnel_from_step,
entity_name,
f"exclusion_{exclusion_id}_")
# every exclusion entity has the form: exclusion_<id>_step_i & timestamp exclusion_<id>_latest_i
# where i is the starting step for exclusion on that entity
all_step_cols.extend(step_cols)
steps = ", ".join(all_step_cols)
breakdown_select_prop = self._get_breakdown_select_prop()
if len(breakdown_select_prop) > 0:
select_prop = f", {breakdown_select_prop}"
else:
select_prop = ""
extra_join = ""
if self._filter.breakdown:
if self._filter.breakdown_type == "cohort":
extra_join = self._get_cohort_breakdown_join()
else:
values = self._get_breakdown_conditions()
self.params.update({"breakdown_values": values})
return FUNNEL_INNER_EVENT_STEPS_QUERY.format(
steps=steps,
event_query=event_query,
extra_join=extra_join,
steps_condition=steps_conditions,
select_prop=select_prop,
)
def _get_inner_event_query(self,
entities=None,
entity_name="events",
skip_entity_filter=False,
skip_step_filter=False) -> str:
entities_to_use = entities or self._filter.entities
event_query, params = FunnelEventQuery(
filter=self._filter, team_id=self._team.pk).get_query(
entities_to_use,
entity_name,
skip_entity_filter=skip_entity_filter)
self.params.update(params)
if skip_step_filter:
steps_conditions = "1=1"
else:
steps_conditions = self._get_steps_conditions(
length=len(entities_to_use))
all_step_cols: List[str] = []
for index, entity in enumerate(entities_to_use):
step_cols = self._get_step_col(entity, index, entity_name)
all_step_cols.extend(step_cols)
for entity in self._filter.exclusions:
step_cols = self._get_step_col(entity, entity.funnel_from_step,
entity_name, "exclusion_")
# every exclusion entity has the form: exclusion_step_i & timestamp exclusion_latest_i
# where i is the starting step for exclusion on that entity
all_step_cols.extend(step_cols)
steps = ", ".join(all_step_cols)
select_prop = self._get_breakdown_select_prop()
breakdown_conditions = ""
extra_conditions = ""
extra_join = ""
if self._filter.breakdown:
if self._filter.breakdown_type == "cohort":
extra_join = self._get_cohort_breakdown_join()
else:
breakdown_conditions = self._get_breakdown_conditions()
extra_conditions = "AND prop != ''" if select_prop else ""
extra_conditions += f"AND {breakdown_conditions}" if breakdown_conditions and select_prop else ""
return FUNNEL_INNER_EVENT_STEPS_QUERY.format(
steps=steps,
event_query=event_query,
extra_join=extra_join,
steps_condition=steps_conditions,
select_prop=select_prop,
extra_conditions=extra_conditions,
)
def _get_inner_event_query(self,
entities=None,
entity_name="events",
skip_entity_filter=False,
skip_step_filter=False) -> str:
entities_to_use = entities or self._filter.entities
event_query, params = FunnelEventQuery(
filter=self._filter, team_id=self._team.pk).get_query(
entities_to_use,
entity_name,
skip_entity_filter=skip_entity_filter)
self.params.update(params)
if skip_step_filter:
steps_conditions = "1=1"
else:
steps_conditions = self._get_steps_conditions(
length=len(self._filter.entities))
all_step_cols: List[str] = []
for index, entity in enumerate(entities_to_use):
step_cols = self._get_step_col(entity, index, entity_name)
all_step_cols.extend(step_cols)
steps = ", ".join(all_step_cols)
select_prop = self._get_breakdown_select_prop()
breakdown_conditions = self._get_breakdown_conditions()
extra_conditions = "AND prop != ''" if select_prop else ""
extra_conditions += f"AND {breakdown_conditions}" if breakdown_conditions and select_prop else ""
return FUNNEL_INNER_EVENT_STEPS_QUERY.format(
steps=steps,
event_query=event_query,
steps_condition=steps_conditions,
select_prop=select_prop,
extra_conditions=extra_conditions,
)