def _session_list(
self,
base_query: str,
params: Tuple[Any, ...],
team: Team,
date_filter: Dict[str, datetime],
request: request.Request,
) -> List[Dict[str, Any]]:
session_list = "SELECT * FROM (SELECT global_session_id, properties, start_time, length, sessions.distinct_id, event_count, events from\
(SELECT\
global_session_id,\
count(1) as event_count,\
MAX(distinct_id) as distinct_id,\
EXTRACT('EPOCH' FROM (MAX(timestamp) - MIN(timestamp))) AS length,\
MIN(timestamp) as start_time,\
array_agg(json_build_object( 'id', id, 'event', event, 'timestamp', timestamp, 'properties', properties, 'elements_hash', elements_hash) ORDER BY timestamp) as events\
FROM ({}) as count GROUP BY 1) as sessions\
LEFT OUTER JOIN posthog_persondistinctid ON posthog_persondistinctid.distinct_id = sessions.distinct_id\
LEFT OUTER JOIN posthog_person ON posthog_person.id = posthog_persondistinctid.person_id\
ORDER BY start_time DESC) as ordered_sessions OFFSET %s LIMIT 50".format(
base_query
)
with connection.cursor() as cursor:
offset = request.GET.get("offset", 0)
params = params + (offset,)
cursor.execute(session_list, params)
sessions = dict_from_cursor_fetchall(cursor)
hash_ids = []
for session in sessions:
for event in session["events"]:
if event.get("elements_hash"):
hash_ids.append(event["elements_hash"])
groups = self._prefech_elements(hash_ids, team)
for session in sessions:
for event in session["events"]:
try:
event.update(
{
"elements": ElementSerializer(
[group for group in groups if group.hash == event["elements_hash"]][0]
.element_set.all()
.order_by("order"),
many=True,
).data
}
)
except IndexError:
event.update({"elements": []})
result = sessions
return result
def get_postgres_running_queries(self):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT now() - query_start as duration, state, query, query_start
FROM pg_stat_activity
WHERE query NOT LIKE '%pg_stat_activity%'
AND query != ''
AND now() - query_start > INTERVAL '3 seconds'
ORDER BY state, duration DESC
""")
return dict_from_cursor_fetchall(cursor)
def get_elements(self, request: request.Request):
team = request.user.team
all_events = Event.objects.filter(team=team, event="$autocapture")
all_events_SQL, sql_params = all_events.query.sql_with_params()
elements_readble = '\
SELECT tag_name_source as name, group_id as id FROM (SELECT \'<\' || e."tag_name" || \'> \' || e."text" as tag_name_source, e."text" as text_source, e.group_id FROM "posthog_element" e\
JOIN ( SELECT group_id, MIN("posthog_element"."order") as minOrder FROM "posthog_element" GROUP BY group_id) e2 ON e.order = e2.minOrder AND e.group_id = e2.group_id) as element\
JOIN (SELECT id, hash, count FROM posthog_elementgroup as g JOIN (SELECT count(*), elements_hash from ({}) as a group by elements_hash) as e on g.hash = e.elements_hash) as outer_group ON element.group_id = outer_group.id where text_source <> \'\' order by count DESC, name limit 20\
'.format(all_events_SQL)
cursor = connection.cursor()
cursor.execute(elements_readble, sql_params)
rows = dict_from_cursor_fetchall(cursor)
return rows
def _session_list(self, base_query: str, params: Tuple[Any, ...],
team: Team, filter: Filter, limit: int,
offset: int) -> List[Dict[str, Any]]:
session_list = """
SELECT
*
FROM (
SELECT
global_session_id,
properties,
start_time,
end_time,
length,
sessions.distinct_id,
event_count,
events
FROM (
SELECT
global_session_id,
count(1) as event_count,
MAX(distinct_id) as distinct_id,
EXTRACT('EPOCH' FROM (MAX(timestamp) - MIN(timestamp))) AS length,
MIN(timestamp) as start_time,
MAX(timestamp) as end_time,
array_agg(json_build_object( 'id', id, 'event', event, 'timestamp', timestamp, 'properties', properties, 'elements_hash', elements_hash) ORDER BY timestamp) as events
FROM
({base_query}) as count
GROUP BY 1
) as sessions
LEFT OUTER JOIN
posthog_persondistinctid ON posthog_persondistinctid.distinct_id = sessions.distinct_id AND posthog_persondistinctid.team_id = %s
LEFT OUTER JOIN
posthog_person ON posthog_person.id = posthog_persondistinctid.person_id
ORDER BY
start_time DESC
) as ordered_sessions
OFFSET %s
LIMIT %s
""".format(base_query=base_query)
with connection.cursor() as cursor:
params = params + (
team.pk,
offset,
limit,
)
cursor.execute(session_list, params)
sessions = dict_from_cursor_fetchall(cursor)
hash_ids = []
for session in sessions:
for event in session["events"]:
if event.get("elements_hash"):
hash_ids.append(event["elements_hash"])
groups = self._prefetch_elements(hash_ids, team)
for session in sessions:
for event in session["events"]:
try:
event.update({
"elements":
ElementSerializer(
[
group for group in groups
if group.hash == event["elements_hash"]
][0].element_set.all().order_by("order"),
many=True,
).data
})
except IndexError:
event.update({"elements": []})
return add_session_recording_ids(team, sessions)
