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)