Coverage for chalicelib/core/sessions/sessions_ch.py: 0%
819 statements
« prev ^ index » next coverage.py v7.15.2, created at 2026-10-10 12:56 +0000
1import logging
3import schemas
4from chalicelib.core import metadata
5from chalicelib.utils import helper, exp_ch_helper
6from chalicelib.utils import sql_helper as sh
7from chalicelib.utils.exp_ch_helper import get_sub_condition, get_col_cast
8from . import performance_event
10logger = logging.getLogger(__name__)
12RESOLUTION_RANGES = [
13 (800, 600),
14 (1280, 720),
15 (1920, 1080),
16 (2560, 1440),
17 (3840, 2160),
18]
22def __is_valid_event(is_any: bool, event: schemas.SessionSearchEventSchema):
23 return not (not is_any and len(event.value) == 0 and event.name not in [schemas.EventType.REQUEST_DETAILS,
24 schemas.EventType.GRAPHQL] \
25 or event.name in [schemas.PerformanceEventType.LOCATION_DOM_COMPLETE,
26 schemas.PerformanceEventType.LOCATION_LARGEST_CONTENTFUL_PAINT_TIME,
27 schemas.PerformanceEventType.LOCATION_TTFB,
28 schemas.PerformanceEventType.LOCATION_AVG_CPU_LOAD,
29 schemas.PerformanceEventType.LOCATION_AVG_MEMORY_USAGE
30 ] and (event.source is None or len(event.source) == 0) \
31 or event.name in [schemas.EventType.REQUEST_DETAILS, schemas.EventType.GRAPHQL] and (
32 event.filters is None or len(event.filters) == 0))
35def json_condition(table_alias, json_column, json_key, op, values, value_key, check_existence=False,
36 numeric_check=False, numeric_type="float"):
37 """
38 Constructs a condition to filter a JSON column dynamically in SQL queries.
40 Parameters:
41 table_alias (str): Alias of the table (e.g., 'main', 'sub').
42 json_column (str): Name of the JSON column (e.g., '$properties').
43 json_key (str): Key in the JSON object to extract.
44 op (str): SQL operator to apply (e.g., '=', 'ILIKE', etc.).
45 values (str | list | tuple): Single value, list of values, or tuple to compare.
46 value_key (str): The parameterized key for SQL (e.g., 'custom').
47 check_existence (bool): Whether to include a JSONHas condition to check if the key exists.
48 numeric_check (bool): Whether to include a numeric check on the extracted value.
49 numeric_type (str): Type for numeric extraction, "int" or "float".
51 Returns:
52 str: The constructed condition.
53 """
54 if isinstance(values, tuple):
55 values = list(values)
56 elif not isinstance(values, list):
57 values = [values]
59 conditions = []
61 # Add JSONHas condition if required
62 if check_existence:
63 conditions.append(f"JSONHas(toString({table_alias}.`{json_column}`), '{json_key}')")
65 # Determine the extraction function for numeric checks
66 if numeric_check:
67 extract_func = "JSONExtractFloat" if numeric_type == "float" else "JSONExtractInt"
68 conditions.append(f"{extract_func}(toString({table_alias}.`{json_column}`), '{json_key}') > 0")
70 # Add the main condition for value comparison
71 if numeric_check:
72 extract_func = "JSONExtractFloat" if numeric_type == "float" else "JSONExtractInt"
73 condition = f"{extract_func}(toString({table_alias}.`{json_column}`), '{json_key}') {op} %({value_key})s"
74 else:
75 # condition = f"JSONExtractString(toString({table_alias}.`{json_column}`), '{json_key}') {op} %({value_key})s"
76 condition = get_sub_condition(
77 col_name=f"JSONExtractString(toString({table_alias}.`{json_column}`), '{json_key}')",
78 val_name=value_key, operator=op
79 )
81 conditions.append(sh.multi_conditions(condition, values, value_key=value_key))
83 return " AND ".join(conditions)
86# this function generates the query and return the generated-query with the dict of query arguments
87def search_query_parts_ch(data: schemas.SessionsSearchPayloadSchema, error_status, errors_only, favorite_only, issue,
88 project_id, user_id, platform="web", extra_event=None, extra_deduplication=[],
89 extra_conditions=None):
90 if issue:
91 data.filters.append(
92 schemas.SessionSearchFilterSchema(value=[issue['type']],
93 name=schemas.FilterType.ISSUE.value,
94 operator=schemas.SearchEventOperator.IS.value)
95 )
96 ss_constraints = []
97 full_args = {"project_id": project_id, "startDate": data.startTimestamp, "endDate": data.endTimestamp,
98 "projectId": project_id, "userId": user_id}
100 MAIN_EVENTS_TABLE = exp_ch_helper.get_main_events_table(timestamp=data.startTimestamp, platform=platform)
101 MAIN_SESSIONS_TABLE = exp_ch_helper.get_main_sessions_table(data.startTimestamp)
103 full_args["MAIN_EVENTS_TABLE"] = MAIN_EVENTS_TABLE
104 full_args["MAIN_SESSIONS_TABLE"] = MAIN_SESSIONS_TABLE
105 extra_constraints = [
106 "s.project_id = %(project_id)s",
107 "isNotNull(s.duration)"
108 ]
109 if favorite_only:
110 extra_constraints.append(f"""s.session_id IN (SELECT session_id
111 FROM {exp_ch_helper.get_user_favorite_sessions_table()} AS user_favorite_sessions
112 WHERE user_id = %(userId)s)""")
113 extra_from = ""
114 events_query_part = ""
115 issues = []
116 __events_where_basic = ["project_id = %(projectId)s",
117 "created_at >= toDateTime(%(startDate)s/1000)",
118 "created_at <= toDateTime(%(endDate)s/1000)"]
119 events_conditions_where = ["main.project_id = %(projectId)s",
120 "main.created_at >= toDateTime(%(startDate)s/1000)",
121 "main.created_at <= toDateTime(%(endDate)s/1000)"]
122 any_incident = False
123 for i, e in enumerate(data.events):
124 if e.name == schemas.EventType.INCIDENT and e.operator == schemas.SearchEventOperator.IS_ANY:
125 any_incident = True
126 data.events.pop(i)
127 # don't stop here because we could have multiple filters looking for any incident
129 if any_incident:
130 any_incident = False
131 for f in data.filters:
132 if f.name == schemas.FilterType.ISSUE:
133 any_incident = True
134 if f.value.index(schemas.IssueType.INCIDENT) < 0:
135 f.value.append(schemas.IssueType.INCIDENT)
136 if f.operator == schemas.SearchEventOperator.IS_ANY:
137 f.operator = schemas.SearchEventOperator.IS
138 break
140 if not any_incident:
141 data.filters.append(schemas.SessionSearchFilterSchema(**{
142 "type": "issue",
143 "isEvent": False,
144 "value": [
145 "incident"
146 ],
147 "operator": "is"
148 }))
149 global_properties = []
150 global_properties_negative = []
151 if len(data.filters) > 0:
152 meta_keys = None
153 # to reduce include a sub-query of sessions inside events query, in order to reduce the selected data
154 include_in_events = False
155 for i, f in enumerate(data.filters):
156 filter_type = f.name
157 f.value = helper.values_for_operator(value=f.value, op=f.operator)
158 f_k = f"f_value{i}"
159 full_args |= {f_k: sh.single_value(f.value)} | sh.multi_values(f.value, value_key=f_k)
160 op = sh.get_sql_operator(f.operator) \
161 if filter_type not in [schemas.FilterType.EVENTS_COUNT] else f.operator.value
162 is_any = sh.isAny_opreator(f.operator)
163 is_undefined = sh.isUndefined_operator(f.operator)
164 if not is_any and not is_undefined and len(f.value) == 0:
165 continue
166 is_not = False
167 if sh.is_negation_operator(f.operator):
168 is_not = True
170 if not f.auto_captured:
171 # global custom property
172 cast = get_col_cast(data_type=f.data_type, value=f.value)
173 if is_any:
174 global_properties.append(f'isNotNull(e.properties.`{f.name}`)')
175 else:
176 if is_not:
177 op = sh.reverse_sql_operator(op)
178 global_properties_negative.append(sh.multi_conditions(get_sub_condition(
179 col_name=f"accurateCastOrNull(e.properties.`{f.name}`,'{cast}')",
180 val_name=f_k, operator=op), f.value, is_not=False, value_key=f_k))
181 else:
182 global_properties.append(sh.multi_conditions(get_sub_condition(
183 col_name=f"accurateCastOrNull(e.properties.`{f.name}`,'{cast}')",
184 val_name=f_k, operator=f.operator), f.value, is_not=False, value_key=f_k))
186 continue
187 if f.auto_captured:
188 if filter_type == schemas.FilterType.USER_BROWSER:
189 if is_any:
190 extra_constraints.append('isNotNull(s.user_browser)')
191 ss_constraints.append('isNotNull(ms.user_browser)')
192 else:
193 extra_constraints.append(
194 sh.multi_conditions(f's.user_browser {op} %({f_k})s', f.value, is_not=is_not,
195 value_key=f_k))
196 ss_constraints.append(
197 sh.multi_conditions(f'ms.user_browser {op} %({f_k})s', f.value, is_not=is_not,
198 value_key=f_k))
200 elif filter_type in [schemas.FilterType.USER_OS, schemas.FilterType.USER_OS_MOBILE]:
201 if is_any:
202 extra_constraints.append('isNotNull(s.user_os)')
203 ss_constraints.append('isNotNull(ms.user_os)')
204 else:
205 extra_constraints.append(
206 sh.multi_conditions(f's.user_os {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
207 ss_constraints.append(
208 sh.multi_conditions(f'ms.user_os {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
210 elif filter_type in [schemas.FilterType.USER_DEVICE, schemas.FilterType.USER_DEVICE_MOBILE]:
211 if is_any:
212 extra_constraints.append('isNotNull(s.user_device)')
213 ss_constraints.append('isNotNull(ms.user_device)')
214 else:
215 extra_constraints.append(
216 sh.multi_conditions(f's.user_device {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
217 ss_constraints.append(
218 sh.multi_conditions(f'ms.user_device {op} %({f_k})s', f.value, is_not=is_not,
219 value_key=f_k))
221 elif filter_type in [schemas.FilterType.USER_COUNTRY, schemas.FilterType.USER_COUNTRY_MOBILE]:
222 if is_any:
223 extra_constraints.append('isNotNull(s.user_country)')
224 ss_constraints.append('isNotNull(ms.user_country)')
225 else:
226 extra_constraints.append(
227 sh.multi_conditions(f's.user_country {op} %({f_k})s', f.value, is_not=is_not,
228 value_key=f_k))
229 ss_constraints.append(
230 sh.multi_conditions(f'ms.user_country {op} %({f_k})s', f.value, is_not=is_not,
231 value_key=f_k))
233 elif filter_type in schemas.FilterType.USER_CITY:
234 if is_any:
235 extra_constraints.append('isNotNull(s.user_city)')
236 ss_constraints.append('isNotNull(ms.user_city)')
237 else:
238 extra_constraints.append(
239 sh.multi_conditions(f's.user_city {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
240 ss_constraints.append(
241 sh.multi_conditions(f'ms.user_city {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
243 elif filter_type in schemas.FilterType.USER_STATE:
244 if is_any:
245 extra_constraints.append('isNotNull(s.user_state)')
246 ss_constraints.append('isNotNull(ms.user_state)')
247 else:
248 extra_constraints.append(
249 sh.multi_conditions(f's.user_state {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
250 ss_constraints.append(
251 sh.multi_conditions(f'ms.user_state {op} %({f_k})s', f.value, is_not=is_not, value_key=f_k))
253 elif filter_type in [schemas.FilterType.UTM_SOURCE]:
254 if is_any:
255 extra_constraints.append('isNotNull(s.utm_source)')
256 ss_constraints.append('isNotNull(ms.utm_source)')
257 elif is_undefined:
258 extra_constraints.append('isNull(s.utm_source)')
259 ss_constraints.append('isNull(ms.utm_source)')
260 else:
261 extra_constraints.append(
262 sh.multi_conditions(f's.utm_source {op} toString(%({f_k})s)', f.value, is_not=is_not,
263 value_key=f_k))
264 ss_constraints.append(
265 sh.multi_conditions(f'ms.utm_source {op} toString(%({f_k})s)', f.value, is_not=is_not,
266 value_key=f_k))
267 elif filter_type in [schemas.FilterType.UTM_MEDIUM]:
268 if is_any:
269 extra_constraints.append('isNotNull(s.utm_medium)')
270 ss_constraints.append('isNotNull(ms.utm_medium)')
271 elif is_undefined:
272 extra_constraints.append('isNull(s.utm_medium)')
273 ss_constraints.append('isNull(ms.utm_medium')
274 else:
275 extra_constraints.append(
276 sh.multi_conditions(f's.utm_medium {op} toString(%({f_k})s)', f.value, is_not=is_not,
277 value_key=f_k))
278 ss_constraints.append(
279 sh.multi_conditions(f'ms.utm_medium {op} toString(%({f_k})s)', f.value, is_not=is_not,
280 value_key=f_k))
281 elif filter_type in [schemas.FilterType.UTM_CAMPAIGN]:
282 if is_any:
283 extra_constraints.append('isNotNull(s.utm_campaign)')
284 ss_constraints.append('isNotNull(ms.utm_campaign)')
285 elif is_undefined:
286 extra_constraints.append('isNull(s.utm_campaign)')
287 ss_constraints.append('isNull(ms.utm_campaign)')
288 else:
289 extra_constraints.append(
290 sh.multi_conditions(f's.utm_campaign {op} toString(%({f_k})s)', f.value, is_not=is_not,
291 value_key=f_k))
292 ss_constraints.append(
293 sh.multi_conditions(f'ms.utm_campaign {op} toString(%({f_k})s)', f.value, is_not=is_not,
294 value_key=f_k))
296 elif filter_type == schemas.FilterType.DURATION:
297 if len(f.value) > 0 and f.value[0] is not None:
298 extra_constraints.append("s.duration >= %(minDuration)s")
299 ss_constraints.append("ms.duration >= %(minDuration)s")
300 full_args["minDuration"] = f.value[0]
301 if len(f.value) > 1 and f.value[1] is not None and int(f.value[1]) > 0:
302 extra_constraints.append("s.duration <= %(maxDuration)s")
303 ss_constraints.append("ms.duration <= %(maxDuration)s")
304 full_args["maxDuration"] = f.value[1]
305 elif filter_type == schemas.FilterType.REFERRER:
306 if is_any:
307 extra_constraints.append('isNotNull(s.base_referrer)')
308 ss_constraints.append('isNotNull(ms.base_referrer)')
309 else:
310 extra_constraints.append(
311 sh.multi_conditions(f"s.base_referrer {op} toString(%({f_k})s)", f.value, is_not=is_not,
312 value_key=f_k))
313 ss_constraints.append(
314 sh.multi_conditions(f"ms.base_referrer {op} toString(%({f_k})s)", f.value, is_not=is_not,
315 value_key=f_k))
316 elif filter_type == schemas.FilterType.METADATA:
317 # to support old metadata-filter structure
318 # get metadata list only if you need it
319 if meta_keys is None:
320 meta_keys = metadata.get(project_id=project_id)
321 meta_keys = {m["key"]: m["index"] for m in meta_keys}
322 if f.source in meta_keys.keys():
323 if is_any:
324 extra_constraints.append(f"isNotNull(s.{metadata.index_to_colname(meta_keys[f.source])})")
325 ss_constraints.append(f"isNotNull(ms.{metadata.index_to_colname(meta_keys[f.source])})")
326 elif is_undefined:
327 extra_constraints.append(f"isNull(s.{metadata.index_to_colname(meta_keys[f.source])})")
328 ss_constraints.append(f"isNull(ms.{metadata.index_to_colname(meta_keys[f.source])})")
329 else:
330 extra_constraints.append(
331 sh.multi_conditions(
332 f"s.{metadata.index_to_colname(meta_keys[f.source])} {op} toString(%({f_k})s)",
333 f.value, is_not=is_not, value_key=f_k))
334 ss_constraints.append(
335 sh.multi_conditions(
336 f"ms.{metadata.index_to_colname(meta_keys[f.source])} {op} toString(%({f_k})s)",
337 f.value, is_not=is_not, value_key=f_k))
338 elif filter_type.startswith(schemas.FilterType.METADATA):
339 # to support new metadata-filter structure
341 if is_any:
342 extra_constraints.append(f"isNotNull(s.{filter_type})")
343 ss_constraints.append(f"isNotNull(ms.{filter_type})")
344 elif is_undefined:
345 extra_constraints.append(f"isNull(s.{filter_type})")
346 ss_constraints.append(f"isNull(ms.{filter_type})")
347 else:
348 extra_constraints.append(
349 sh.multi_conditions(f"s.{filter_type} {op} toString(%({f_k})s)",
350 f.value, is_not=is_not, value_key=f_k))
351 ss_constraints.append(
352 sh.multi_conditions(f"ms.{filter_type} {op} toString(%({f_k})s)",
353 f.value, is_not=is_not, value_key=f_k))
355 elif filter_type in [schemas.FilterType.USER_ID, schemas.FilterType.USER_ID_MOBILE]:
356 if is_any:
357 extra_constraints.append('isNotNull(s.user_id)')
358 ss_constraints.append('isNotNull(ms.user_id)')
359 elif is_undefined:
360 extra_constraints.append('isNull(s.user_id)')
361 ss_constraints.append('isNull(ms.user_id)')
362 else:
363 extra_constraints.append(
364 sh.multi_conditions(f"s.user_id {op} toString(%({f_k})s)", f.value, is_not=is_not,
365 value_key=f_k))
366 ss_constraints.append(
367 sh.multi_conditions(f"ms.user_id {op} toString(%({f_k})s)", f.value, is_not=is_not,
368 value_key=f_k))
369 elif filter_type in [schemas.FilterType.USER_ANONYMOUS_ID,
370 schemas.FilterType.USER_ANONYMOUS_ID_MOBILE]:
371 if is_any:
372 extra_constraints.append('isNotNull(s.user_anonymous_id)')
373 ss_constraints.append('isNotNull(ms.user_anonymous_id)')
374 elif is_undefined:
375 extra_constraints.append('isNull(s.user_anonymous_id)')
376 ss_constraints.append('isNull(ms.user_anonymous_id)')
377 else:
378 extra_constraints.append(
379 sh.multi_conditions(f"s.user_anonymous_id {op} toString(%({f_k})s)", f.value, is_not=is_not,
380 value_key=f_k))
381 ss_constraints.append(
382 sh.multi_conditions(f"ms.user_anonymous_id {op} toString(%({f_k})s)", f.value,
383 is_not=is_not,
384 value_key=f_k))
385 elif filter_type in [schemas.FilterType.REV_ID, schemas.FilterType.REV_ID_MOBILE]:
386 if is_any:
387 extra_constraints.append('isNotNull(s.rev_id)')
388 ss_constraints.append('isNotNull(ms.rev_id)')
389 elif is_undefined:
390 extra_constraints.append('isNull(s.rev_id)')
391 ss_constraints.append('isNull(ms.rev_id)')
392 else:
393 extra_constraints.append(
394 sh.multi_conditions(f"s.rev_id {op} toString(%({f_k})s)", f.value, is_not=is_not,
395 value_key=f_k))
396 ss_constraints.append(
397 sh.multi_conditions(f"ms.rev_id {op} toString(%({f_k})s)", f.value, is_not=is_not,
398 value_key=f_k))
399 elif filter_type == schemas.FilterType.PLATFORM:
400 # op = sh.get_sql_operator(f.operator)
401 extra_constraints.append(
402 sh.multi_conditions(f"s.user_device_type {op} %({f_k})s", f.value, is_not=is_not,
403 value_key=f_k))
404 ss_constraints.append(
405 sh.multi_conditions(f"ms.user_device_type {op} %({f_k})s", f.value, is_not=is_not,
406 value_key=f_k))
407 elif filter_type == schemas.FilterType.ISSUE:
408 if is_any:
409 extra_constraints.append("notEmpty(s.issue_types)")
410 ss_constraints.append("notEmpty(ms.issue_types)")
411 else:
412 if f.source:
413 issues.append(f)
415 extra_constraints.append(f"hasAny(s.issue_types,%({f_k})s)")
416 # sh.multi_conditions(f"%({f_k})s {op} ANY (s.issue_types)", f.value, is_not=is_not,
417 # value_key=f_k))
418 ss_constraints.append(f"hasAny(ms.issue_types,%({f_k})s)")
419 # sh.multi_conditions(f"%({f_k})s {op} ANY (ms.issue_types)", f.value, is_not=is_not,
420 # value_key=f_k))
421 if is_not:
422 extra_constraints[-1] = f"not({extra_constraints[-1]})"
423 ss_constraints[-1] = f"not({ss_constraints[-1]})"
424 elif filter_type == schemas.FilterType.EVENTS_COUNT:
425 extra_constraints.append(
426 sh.multi_conditions(f"s.events_count {op} %({f_k})s", f.value, is_not=is_not,
427 value_key=f_k))
428 ss_constraints.append(
429 sh.multi_conditions(f"ms.events_count {op} %({f_k})s", f.value, is_not=is_not,
430 value_key=f_k))
431 else:
432 # global auto-captured property
433 cast = get_col_cast(data_type=f.data_type, value=f.value)
434 if is_any:
435 global_properties.append(f'isNotNull(e.`$properties`.`{f.name}`)')
436 else:
437 if is_not:
438 op = sh.reverse_sql_operator(op)
439 global_properties_negative.append(sh.multi_conditions(get_sub_condition(
440 col_name=f"accurateCastOrNull(e.`$properties`.`{f.name}`,'{cast}')",
441 val_name=f_k, operator=op), f.value, is_not=False, value_key=f_k))
442 else:
443 global_properties.append(sh.multi_conditions(get_sub_condition(
444 col_name=f"accurateCastOrNull(e.`$properties`.`{f.name}`,'{cast}')",
445 val_name=f_k, operator=f.operator), f.value, is_not=False, value_key=f_k))
447 continue
448 include_in_events = True
450 if include_in_events:
451 events_conditions_where.append(f"""main.session_id IN (SELECT s.session_id
452 FROM {MAIN_SESSIONS_TABLE} AS s
453 WHERE {" AND ".join(extra_constraints)})""")
455 if len(global_properties) > 0:
456 global_properties += ["e.project_id=%(project_id)s",
457 "e.created_at >= toDateTime(%(startDate)s/1000)",
458 "e.created_at <= toDateTime(%(endDate)s/1000)"]
459 # ---------------------------------------------------------------------------
460 events_extra_join = ""
461 if len(data.events) > 0:
462 valid_events_count = 0
463 for event in data.events:
464 is_any = sh.isAny_opreator(event.operator)
465 if not isinstance(event.value, list):
466 event.value = [event.value]
467 if __is_valid_event(is_any=is_any, event=event):
468 valid_events_count += 1
469 events_query_from = []
470 events_conditions = []
471 events_conditions_not = []
472 event_index = 0
473 or_events = data.events_order == schemas.SearchEventOrder.OR
474 for i, event in enumerate(data.events):
475 event_type = event.name
476 is_any = sh.isAny_opreator(event.operator)
477 if not isinstance(event.value, list):
478 event.value = [event.value]
479 if not __is_valid_event(is_any=is_any, event=event):
480 continue
481 op = sh.get_sql_operator(event.operator)
482 is_not = False
483 if sh.is_negation_operator(event.operator):
484 is_not = True
485 op = sh.reverse_sql_operator(op)
486 # if event_index == 0 or or_events:
487 # event_from = f"%s INNER JOIN {MAIN_SESSIONS_TABLE} AS ms USING (session_id)"
488 event_from = "%s"
489 event_where = ["main.project_id = %(projectId)s",
490 "main.created_at >= toDateTime(%(startDate)s/1000)",
491 "main.created_at <= toDateTime(%(endDate)s/1000)"]
493 e_k = f"e_value{i}"
494 s_k = e_k + "_source"
496 event.value = helper.values_for_operator(value=event.value, op=event.operator)
497 full_args |= sh.multi_values(event.value, value_key=e_k) \
498 | sh.multi_values(event.source, value_key=s_k) \
499 | {e_k: event.value[0] if len(event.value) > 0 else event.value}
501 if not event.auto_captured:
502 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
503 event_where.append(f"main.`$event_name`=%({e_k})s")
504 events_conditions.append({"type": event_where[-1]})
505 full_args[e_k] = event.name
506 # events_conditions[-1]["condition"] = event_where[-1]
507 elif event_type == schemas.EventType.CLICK:
508 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
509 if platform == "web":
510 _column = "label"
511 event_where.append(
512 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
513 events_conditions.append({"type": event_where[-1]})
514 if not is_any:
515 if schemas.ClickEventExtraOperator.has_value(event.operator):
516 # event_where.append(json_condition(
517 # "main",
518 # "$properties",
519 # "selector", op, event.value, e_k)
520 # )
521 event_where.append(
522 sh.multi_conditions(
523 get_sub_condition(col_name=f"main.`$properties`.selector",
524 val_name=e_k, operator=event.operator),
525 event.value, value_key=e_k)
526 )
527 events_conditions[-1]["condition"] = event_where[-1]
528 else:
529 if is_not:
530 # event_where.append(json_condition(
531 # "sub", "$properties", _column, op, event.value, e_k
532 # ))
533 event_where.append(
534 sh.multi_conditions(
535 get_sub_condition(col_name=f"sub.`$properties`.{_column}",
536 val_name=e_k, operator=event.operator),
537 event.value, value_key=e_k)
538 )
539 events_conditions_not.append(
540 {
541 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"
542 }
543 )
544 events_conditions_not[-1]["condition"] = event_where[-1]
545 else:
546 # event_where.append(
547 # json_condition("main", "$properties", _column, op, event.value, e_k)
548 # )
549 event_where.append(
550 sh.multi_conditions(
551 get_sub_condition(col_name=f"main.`$properties`.{_column}",
552 val_name=e_k, operator=event.operator),
553 event.value, value_key=e_k)
554 )
555 events_conditions[-1]["condition"] = event_where[-1]
556 else:
557 _column = "label"
558 event_where.append(
559 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
560 events_conditions.append({"type": event_where[-1]})
561 if not is_any:
562 if is_not:
563 event_where.append(
564 json_condition("sub", "$properties", _column, op, event.value, e_k)
565 )
566 events_conditions_not.append(
567 {
568 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
569 events_conditions_not[-1]["condition"] = event_where[-1]
570 else:
571 event_where.append(
572 json_condition("main", "$properties", _column, op, event.value, e_k)
573 )
574 events_conditions[-1]["condition"] = event_where[-1]
576 elif event_type == schemas.EventType.INPUT:
577 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
578 if platform == "web":
579 _column = "label"
580 event_where.append(
581 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
582 events_conditions.append({"type": event_where[-1]})
583 if not is_any:
584 if is_not:
585 event_where.append(
586 json_condition("sub", "$properties", _column, op, event.value, e_k)
587 )
588 events_conditions_not.append(
589 {
590 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
591 events_conditions_not[-1]["condition"] = event_where[-1]
592 else:
593 event_where.append(
594 json_condition("main", "$properties", _column, op, event.value, e_k)
595 )
596 events_conditions[-1]["condition"] = event_where[-1]
597 if event.source is not None and len(event.source) > 0:
598 event_where.append(
599 json_condition("main", "$properties", "value", "ILIKE", event.source, f"custom{i}")
600 )
602 full_args |= sh.multi_values(event.source, value_key=f"custom{i}")
603 else:
604 _column = "label"
605 event_where.append(
606 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
607 events_conditions.append({"type": event_where[-1]})
608 if not is_any:
609 if is_not:
610 event_where.append(
611 json_condition("sub", "$properties", _column, op, event.value, e_k)
612 )
613 events_conditions_not.append(
614 {
615 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
616 events_conditions_not[-1]["condition"] = event_where[-1]
617 else:
618 event_where.append(
619 json_condition("main", "$properties", _column, op, event.value, e_k)
620 )
622 events_conditions[-1]["condition"] = event_where[-1]
624 elif event_type == schemas.EventType.LOCATION:
625 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
626 if platform == "web":
627 _column = 'url_path'
628 event_where.append(
629 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
630 events_conditions.append({"type": event_where[-1]})
631 if not is_any:
632 if is_not:
633 event_where.append(
634 json_condition("sub", "$properties", _column, op, event.value, e_k)
635 )
636 events_conditions_not.append(
637 {
638 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
639 events_conditions_not[-1]["condition"] = event_where[-1]
640 else:
641 event_where.append(
642 json_condition("main", "$properties", _column, op, event.value, e_k)
643 )
644 events_conditions[-1]["condition"] = event_where[-1]
645 else:
646 _column = "name"
647 event_where.append(
648 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
649 events_conditions.append({"type": event_where[-1]})
650 if not is_any:
651 if is_not:
652 event_where.append(
653 json_condition("sub", "$properties", _column, op, event.value, e_k)
654 )
655 events_conditions_not.append(
656 {
657 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
658 events_conditions_not[-1]["condition"] = event_where[-1]
659 else:
660 event_where.append(sh.multi_conditions(f"main.{_column} {op} %({e_k})s",
661 event.value, value_key=e_k))
662 events_conditions[-1]["condition"] = event_where[-1]
663 elif event_type == schemas.EventType.CUSTOM:
664 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
665 _column = "name"
666 event_where.append(
667 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
668 events_conditions.append({"type": event_where[-1]})
669 if not is_any:
670 if is_not:
671 event_where.append(
672 json_condition("sub", "$properties", _column, op, event.value, e_k)
673 )
674 events_conditions_not.append(
675 {
676 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
677 events_conditions_not[-1]["condition"] = event_where[-1]
678 else:
679 event_where.append(json_condition(
680 "main", "$properties", _column, op, event.value, e_k
681 ))
682 events_conditions[-1]["condition"] = event_where[-1]
683 elif event_type == schemas.EventType.REQUEST:
684 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
685 _column = 'url_path'
686 event_where.append(
687 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
688 events_conditions.append({"type": event_where[-1]})
689 if not is_any:
690 if is_not:
691 event_where.append(json_condition(
692 "sub", "$properties", _column, op, event.value, e_k
693 ))
694 events_conditions_not.append(
695 {
696 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
697 events_conditions_not[-1]["condition"] = event_where[-1]
698 else:
699 event_where.append(json_condition(
700 "main", "$properties", _column, op, event.value, e_k
701 ))
702 events_conditions[-1]["condition"] = event_where[-1]
704 elif event_type == schemas.EventType.STATE_ACTION:
705 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
706 _column = "name"
707 event_where.append(
708 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
709 events_conditions.append({"type": event_where[-1]})
710 if not is_any:
711 if is_not:
712 event_where.append(json_condition(
713 "sub", "$properties", _column, op, event.value, e_k
714 ))
715 events_conditions_not.append(
716 {
717 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
718 events_conditions_not[-1]["condition"] = event_where[-1]
719 else:
720 event_where.append(json_condition(
721 "main", "$properties", _column, op, event.value, e_k
722 ))
723 events_conditions[-1]["condition"] = event_where[-1]
724 # TODO: isNot for ERROR
725 elif event_type == schemas.EventType.ERROR:
726 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main"
727 events_extra_join = f"SELECT * FROM {MAIN_EVENTS_TABLE} AS main1 WHERE main1.project_id=%(project_id)s"
728 event_where.append(
729 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
730 events_conditions.append({"type": event_where[-1]})
731 event.source = tuple(event.source)
732 events_conditions[-1]["condition"] = []
733 if not is_any and event.value not in [None, "*", ""]:
734 event_where.append(
735 sh.multi_conditions(
736 f"(toString(main1.`$properties`.message) {op} %({e_k})s OR toString(main1.`$properties`.name) {op} %({e_k})s)",
737 event.value, value_key=e_k))
738 events_conditions[-1]["condition"].append(event_where[-1])
739 events_extra_join += f" AND {event_where[-1]}"
740 if len(event.source) > 0 and event.source[0] not in [None, "*", ""]:
741 event_where.append(
742 sh.multi_conditions(f"toString(main1.`$properties`.source) = %({s_k})s", event.source,
743 value_key=s_k))
744 events_conditions[-1]["condition"].append(event_where[-1])
745 events_extra_join += f" AND {event_where[-1]}"
747 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
749 # ----- Mobile
750 elif event_type == schemas.EventType.CLICK_MOBILE:
751 _column = "label"
752 event_where.append(
753 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
754 events_conditions.append({"type": event_where[-1]})
755 if not is_any:
756 if is_not:
757 event_where.append(json_condition(
758 "sub", "$properties", _column, op, event.value, e_k
759 ))
760 events_conditions_not.append(
761 {
762 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
763 events_conditions_not[-1]["condition"] = event_where[-1]
764 else:
765 event_where.append(json_condition(
766 "main", "$properties", _column, op, event.value, e_k
767 ))
768 events_conditions[-1]["condition"] = event_where[-1]
769 elif event_type == schemas.EventType.INPUT_MOBILE:
770 _column = "label"
771 event_where.append(
772 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
773 events_conditions.append({"type": event_where[-1]})
774 if not is_any:
775 if is_not:
776 event_where.append(json_condition(
777 "sub", "$properties", _column, op, event.value, e_k
778 ))
779 events_conditions_not.append(
780 {
781 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
782 events_conditions_not[-1]["condition"] = event_where[-1]
783 else:
784 event_where.append(json_condition(
785 "main", "$properties", _column, op, event.value, e_k
786 ))
787 events_conditions[-1]["condition"] = event_where[-1]
788 elif event_type == schemas.EventType.VIEW_MOBILE:
789 _column = "name"
790 event_where.append(
791 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
792 events_conditions.append({"type": event_where[-1]})
793 if not is_any:
794 if is_not:
795 event_where.append(json_condition(
796 "sub", "$properties", _column, op, event.value, e_k
797 ))
798 events_conditions_not.append(
799 {
800 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
801 events_conditions_not[-1]["condition"] = event_where[-1]
802 else:
803 event_where.append(json_condition(
804 "main", "$properties", _column, op, event.value, e_k
805 ))
806 events_conditions[-1]["condition"] = event_where[-1]
807 elif event_type == schemas.EventType.CUSTOM_MOBILE:
808 _column = "name"
809 event_where.append(
810 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
811 events_conditions.append({"type": event_where[-1]})
812 if not is_any:
813 if is_not:
814 event_where.append(json_condition(
815 "sub", "$properties", _column, op, event.value, e_k
816 ))
817 events_conditions_not.append(
818 {
819 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
820 events_conditions_not[-1]["condition"] = event_where[-1]
821 else:
822 event_where.append(json_condition(
823 "main", "$properties", _column, op, event.value, e_k
824 ))
826 events_conditions[-1]["condition"] = event_where[-1]
827 elif event_type == schemas.EventType.REQUEST_MOBILE:
828 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
829 _column = 'url_path'
830 event_where.append(
831 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
832 events_conditions.append({"type": event_where[-1]})
833 if not is_any:
834 if is_not:
835 event_where.append(json_condition(
836 "sub", "$properties", _column, op, event.value, e_k
837 ))
838 events_conditions_not.append(
839 {
840 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
841 events_conditions_not[-1]["condition"] = event_where[-1]
842 else:
843 event_where.append(json_condition(
844 "main", "$properties", _column, op, event.value, e_k
845 ))
846 events_conditions[-1]["condition"] = event_where[-1]
847 elif event_type == schemas.EventType.ERROR_MOBILE:
848 _column = "name"
849 event_where.append(
850 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
851 events_conditions.append({"type": event_where[-1]})
852 if not is_any:
853 if is_not:
854 event_where.append(json_condition(
855 "sub", "$properties", _column, op, event.value, e_k
856 ))
858 events_conditions_not.append(
859 {
860 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
861 events_conditions_not[-1]["condition"] = event_where[-1]
862 else:
863 event_where.append(json_condition(
864 "main", "$properties", _column, op, event.value, e_k
865 ))
866 events_conditions[-1]["condition"] = event_where[-1]
867 elif event_type == schemas.EventType.SWIPE_MOBILE and platform != "web":
868 _column = "label"
869 event_where.append(
870 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
871 events_conditions.append({"type": event_where[-1]})
872 if not is_any:
873 if is_not:
874 event_where.append(json_condition(
875 "sub", "$properties", _column, op, event.value, e_k
876 ))
877 events_conditions_not.append(
878 {
879 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
880 events_conditions_not[-1]["condition"] = event_where[-1]
881 else:
882 event_where.append(json_condition(
883 "main", "$properties", _column, op, event.value, e_k
884 ))
885 events_conditions[-1]["condition"] = event_where[-1]
887 elif event_type == schemas.PerformanceEventType.FETCH_FAILED:
888 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
889 _column = 'url_path'
890 event_where.append(
891 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
892 events_conditions.append({"type": event_where[-1]})
893 events_conditions[-1]["condition"] = []
894 if not is_any:
895 if is_not:
896 event_where.append(json_condition(
897 "sub", "$properties", _column, op, event.value, e_k
898 ))
899 events_conditions_not.append(
900 {
901 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
902 events_conditions_not[-1]["condition"] = event_where[-1]
903 else:
904 event_where.append(json_condition(
905 "main", "$properties", _column, op, event.value, e_k
906 ))
907 events_conditions[-1]["condition"].append(event_where[-1])
908 col = performance_event.get_col(event_type)
909 colname = col["column"]
910 event_where.append(f"main.{colname} = 0")
911 events_conditions[-1]["condition"].append(event_where[-1])
912 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
914 elif event_type in [schemas.PerformanceEventType.LOCATION_DOM_COMPLETE,
915 schemas.PerformanceEventType.LOCATION_LARGEST_CONTENTFUL_PAINT_TIME,
916 schemas.PerformanceEventType.LOCATION_TTFB]:
917 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
918 event_where.append(
919 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
920 events_conditions.append({"type": event_where[-1]})
921 events_conditions[-1]["condition"] = []
922 col = performance_event.get_col(event_type)
923 colname = col["column"]
924 tname = "main"
925 if not is_any:
926 event_where.append(json_condition(
927 "main", "$properties", 'url_path', op, event.value, e_k
928 ))
929 events_conditions[-1]["condition"].append(event_where[-1])
930 e_k += "_custom"
931 full_args |= sh.multi_values(event.source, value_key=e_k)
933 event_where.append(json_condition(
934 tname, "$properties", colname, event.sourceOperator, event.source, e_k, True, True)
935 )
937 events_conditions[-1]["condition"].append(event_where[-1])
938 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
939 # TODO: isNot for PerformanceEvent
940 elif event_type in [schemas.PerformanceEventType.LOCATION_AVG_CPU_LOAD,
941 schemas.PerformanceEventType.LOCATION_AVG_MEMORY_USAGE]:
942 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
943 event_where.append(
944 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
945 events_conditions.append({"type": event_where[-1]})
946 events_conditions[-1]["condition"] = []
947 col = performance_event.get_col(event_type)
948 colname = col["column"]
949 tname = "main"
950 if not is_any:
951 event_where.append(json_condition(
952 "main", "$properties", 'url_path', op, event.value, e_k
953 ))
954 events_conditions[-1]["condition"].append(event_where[-1])
955 e_k += "_custom"
956 full_args |= sh.multi_values(event.source, value_key=e_k)
958 event_where.append(json_condition(
959 tname, "$properties", colname, event.sourceOperator, event.source, e_k, True, True)
960 )
962 events_conditions[-1]["condition"].append(event_where[-1])
963 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
965 elif event_type == schemas.EventType.REQUEST_DETAILS:
966 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
967 event_where.append(
968 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
969 events_conditions.append({"type": event_where[-1]})
970 apply = False
971 events_conditions[-1]["condition"] = []
972 for j, f in enumerate(event.filters):
973 is_any = sh.isAny_opreator(f.operator)
974 if is_any or len(f.value) == 0:
975 continue
976 is_negative_operator = sh.is_negation_operator(f.operator)
977 f.value = helper.values_for_operator(value=f.value, op=f.operator)
978 op = sh.get_sql_operator(f.operator)
979 r_op = ""
980 if is_negative_operator:
981 r_op = sh.reverse_sql_operator(op)
982 e_k_f = e_k + f"_fetch{j}"
983 full_args |= sh.multi_values(f.value, value_key=e_k_f)
984 if f.type == schemas.FetchFilterType.FETCH_URL:
985 event_where.append(json_condition(
986 "main", "$properties", 'url_path', op, f.value, e_k_f
987 ))
988 events_conditions[-1]["condition"].append(event_where[-1])
989 apply = True
990 if is_negative_operator:
991 events_conditions_not.append(
992 {
993 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
994 events_conditions_not[-1]["condition"] = sh.multi_conditions(
995 f"sub.`$properties`.url_path {r_op} %({e_k_f})s", f.value, value_key=e_k_f)
996 elif f.type == schemas.FetchFilterType.FETCH_STATUS_CODE:
997 event_where.append(json_condition(
998 "main", "$properties", 'status', op, f.value, e_k_f, True, True
999 ))
1000 events_conditions[-1]["condition"].append(event_where[-1])
1001 apply = True
1002 elif f.type == schemas.FetchFilterType.FETCH_METHOD:
1003 event_where.append(json_condition(
1004 "main", "$properties", 'method', op, f.value, e_k_f
1005 ))
1006 events_conditions[-1]["condition"].append(event_where[-1])
1007 apply = True
1008 if is_negative_operator:
1009 events_conditions_not.append(
1010 {
1011 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
1012 events_conditions_not[-1]["condition"] = sh.multi_conditions(
1013 f"sub.`$properties`.method {r_op} %({e_k_f})s", f.value,
1014 value_key=e_k_f)
1015 elif f.type == schemas.FetchFilterType.FETCH_DURATION:
1016 event_where.append(
1017 sh.multi_conditions(f"main.`$duration_s` {f.operator} %({e_k_f})s/1000", f.value,
1018 value_key=e_k_f))
1019 events_conditions[-1]["condition"].append(event_where[-1])
1020 apply = True
1021 elif f.type == schemas.FetchFilterType.FETCH_REQUEST_BODY:
1022 event_where.append(json_condition(
1023 "main", "$properties", 'request_body', op, f.value, e_k_f
1024 ))
1025 events_conditions[-1]["condition"].append(event_where[-1])
1026 apply = True
1027 if is_negative_operator:
1028 events_conditions_not.append(
1029 {
1030 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
1031 events_conditions_not[-1]["condition"] = sh.multi_conditions(
1032 f"sub.`$properties`.request_body {r_op} %({e_k_f})s", f.value,
1033 value_key=e_k_f)
1034 elif f.type == schemas.FetchFilterType.FETCH_RESPONSE_BODY:
1035 event_where.append(json_condition(
1036 "main", "$properties", 'response_body', op, f.value, e_k_f
1037 ))
1038 events_conditions[-1]["condition"].append(event_where[-1])
1039 apply = True
1040 if is_negative_operator:
1041 events_conditions_not.append(
1042 {
1043 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"})
1044 events_conditions_not[-1]["condition"] = sh.multi_conditions(
1045 f"sub.`$properties`.response_body {r_op} %({e_k_f})s", f.value,
1046 value_key=e_k_f)
1047 else:
1048 logging.warning(f"undefined FETCH filter: {f.type}")
1049 if not apply:
1050 continue
1051 else:
1052 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
1053 # TODO: no isNot for GraphQL
1054 elif event_type == schemas.EventType.GRAPHQL:
1055 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
1056 event_where.append(f"main.`$event_name`='GRAPHQL'")
1057 events_conditions.append({"type": event_where[-1]})
1058 events_conditions[-1]["condition"] = []
1059 for j, f in enumerate(event.filters):
1060 is_any = sh.isAny_opreator(f.operator)
1061 if is_any or len(f.value) == 0:
1062 continue
1063 f.value = helper.values_for_operator(value=f.value, op=f.operator)
1064 op = sh.get_sql_operator(f.operator)
1065 e_k_f = e_k + f"_graphql{j}"
1066 full_args |= sh.multi_values(f.value, value_key=e_k_f)
1067 if f.type == schemas.GraphqlFilterType.GRAPHQL_NAME:
1068 event_where.append(json_condition(
1069 "main", "$properties", "name", op, f.value, e_k_f
1070 ))
1071 events_conditions[-1]["condition"].append(event_where[-1])
1072 elif f.type == schemas.GraphqlFilterType.GRAPHQL_METHOD:
1073 event_where.append(json_condition(
1074 "main", "$properties", 'method', op, f.value, e_k_f
1075 ))
1076 events_conditions[-1]["condition"].append(event_where[-1])
1077 elif f.type == schemas.GraphqlFilterType.GRAPHQL_REQUEST_BODY:
1078 event_where.append(json_condition(
1079 "main", "$properties", 'request_body', op, f.value, e_k_f
1080 ))
1081 events_conditions[-1]["condition"].append(event_where[-1])
1082 elif f.type == schemas.GraphqlFilterType.GRAPHQL_RESPONSE_BODY:
1083 event_where.append(json_condition(
1084 "main", "$properties", 'response_body', op, f.value, e_k_f
1085 ))
1086 events_conditions[-1]["condition"].append(event_where[-1])
1087 else:
1088 logging.warning(f"undefined GRAPHQL filter: {f.type}")
1089 events_conditions[-1]["condition"] = " AND ".join(events_conditions[-1]["condition"])
1090 elif event_type == schemas.EventType.EVENT:
1091 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
1092 _column = "label"
1093 event_where.append(f"main.`$event_name`=%({e_k})s AND main.session_id>0")
1094 events_conditions.append({"type": event_where[-1], "condition": ""})
1095 elif event_type == schemas.EventType.INCIDENT:
1096 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
1097 _column = "label"
1098 event_where.append(
1099 f"main.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'")
1100 events_conditions.append({"type": event_where[-1]})
1102 if is_not:
1103 event_where.append(
1104 sh.multi_conditions(
1105 get_sub_condition(col_name=f"sub.`$properties`.{_column}",
1106 val_name=e_k, operator=event.operator),
1107 event.value, value_key=e_k)
1108 )
1109 events_conditions_not.append(
1110 {
1111 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(event_type, platform=platform)}'"
1112 }
1113 )
1114 events_conditions_not[-1]["condition"] = event_where[-1]
1115 else:
1117 event_where.append(
1118 sh.multi_conditions(
1119 get_sub_condition(col_name=f"main.`$properties`.{_column}",
1120 val_name=e_k, operator=event.operator),
1121 event.value, value_key=e_k)
1122 )
1123 events_conditions[-1]["condition"] = event_where[-1]
1124 elif event_type == schemas.EventType.CLICK_COORDINATES:
1125 event_from = event_from % f"{MAIN_EVENTS_TABLE} AS main "
1126 event_where.append(
1127 f"main.`$event_name`='{exp_ch_helper.get_event_type(schemas.EventType.CLICK, platform=platform)}'")
1128 events_conditions.append({"type": event_where[-1]})
1130 if is_not:
1131 event_where.append(
1132 sh.coordinate_conditions(
1133 condition_x=f"sub.`$properties`.normalized_x",
1134 condition_y=f"sub.`$properties`.normalized_y",
1135 values=event.value, value_key=e_k, is_not=True)
1136 )
1137 events_conditions_not.append(
1138 {
1139 "type": f"sub.`$event_name`='{exp_ch_helper.get_event_type(schemas.EventType.CLICK, platform=platform)}'"
1140 }
1141 )
1142 events_conditions_not[-1]["condition"] = event_where[-1]
1143 else:
1144 event_where.append(
1145 sh.coordinate_conditions(
1146 condition_x=f"main.`$properties`.normalized_x",
1147 condition_y=f"main.`$properties`.normalized_y",
1148 values=event.value, value_key=e_k, is_not=True)
1149 )
1150 events_conditions[-1]["condition"] = event_where[-1]
1152 else:
1153 continue
1154 if event.properties is not None and len(event.properties.filters) > 0:
1155 sub_conditions = []
1156 for l, property in enumerate(event.properties.filters):
1157 a_k = f"{e_k}_att_{l}"
1158 property.value = helper.values_for_operator(value=property.value, op=property.operator)
1159 full_args |= sh.multi_values(property.value, value_key=a_k, data_type=property.data_type)
1160 cast = get_col_cast(data_type=property.data_type, value=property.value)
1161 if property.auto_captured and property.is_predefined:
1162 condition = get_sub_condition(col_name=f"accurateCastOrNull(main.`{property.name}`,'{cast}')",
1163 val_name=a_k, operator=property.operator)
1164 elif property.auto_captured:
1165 condition = get_sub_condition(
1166 col_name=f"accurateCastOrNull(main.`$properties`.`{property.name}`,'{cast}')",
1167 val_name=a_k, operator=property.operator)
1168 else:
1169 condition = get_sub_condition(
1170 col_name=f"accurateCastOrNull(main.properties.`{property.name}`,'{cast}')",
1171 val_name=a_k, operator=property.operator)
1172 event_where.append(
1173 sh.multi_conditions(condition, property.value, value_key=a_k)
1174 )
1175 sub_conditions.append(event_where[-1])
1176 if len(sub_conditions) > 0:
1177 sub_conditions = (" " + event.properties.operator + " ").join(sub_conditions)
1178 if "condition" in events_conditions[-1]:
1179 events_conditions[-1]["condition"] += \
1180 " AND " if len(events_conditions[-1]["condition"]) > 0 else ""
1181 events_conditions[-1]["condition"] += "(" + sub_conditions + ")"
1182 else:
1183 events_conditions[-1]["condition"] = "(" + sub_conditions + ")"
1184 if event_index == 0 or or_events:
1185 event_where += ss_constraints
1186 if is_not:
1187 if event_index == 0 or or_events:
1188 events_query_from.append(f"""\
1189 (SELECT
1190 session_id,
1191 0 AS timestamp
1192 FROM sessions
1193 WHERE EXISTS(SELECT session_id
1194 FROM {event_from}
1195 WHERE {" AND ".join(event_where)}
1196 AND sessions.session_id=ms.session_id) IS FALSE
1197 AND project_id = %(projectId)s
1198 AND start_ts >= %(startDate)s
1199 AND start_ts <= %(endDate)s
1200 AND duration IS NOT NULL
1201 ) {"" if or_events else (f"AS event_{event_index}" + ("ON(TRUE)" if event_index > 0 else ""))}\
1202 """)
1203 else:
1204 events_query_from.append(f"""\
1205 (SELECT
1206 event_0.session_id,
1207 event_{event_index - 1}.timestamp AS timestamp
1208 WHERE EXISTS(SELECT session_id FROM {event_from} WHERE {" AND ".join(event_where)}) IS FALSE
1209 ) AS event_{event_index} {"ON(TRUE)" if event_index > 0 else ""}\
1210 """)
1211 else:
1212 if data.events_order == schemas.SearchEventOrder.THEN:
1213 pass
1214 else:
1215 events_query_from.append(f"""\
1216 (SELECT main.session_id, {"MIN" if event_index < (valid_events_count - 1) else "MAX"}(main.created_at) AS datetime
1217 FROM {event_from}
1218 WHERE {" AND ".join(event_where)}
1219 GROUP BY session_id
1220 ) {"" if or_events else (f"AS event_{event_index} " + ("ON(TRUE)" if event_index > 0 else ""))}\
1221 """)
1222 event_index += 1
1223 # limit THEN-events to 7 in CH because sequenceMatch cannot take more arguments
1224 if event_index == 7 and data.events_order == schemas.SearchEventOrder.THEN:
1225 break
1226 if event_index < 2:
1227 data.events_order = schemas.SearchEventOrder.OR
1228 if len(events_extra_join) > 0:
1229 if event_index < 2:
1230 events_extra_join = f"INNER JOIN ({events_extra_join}) AS main1 USING(error_id)"
1231 else:
1232 events_extra_join = f"LEFT JOIN ({events_extra_join}) AS main1 USING(error_id)"
1233 if favorite_only and user_id is not None:
1234 events_conditions_where.append(f"""main.session_id IN (SELECT session_id
1235 FROM {exp_ch_helper.get_user_favorite_sessions_table()} AS user_favorite_sessions
1236 WHERE user_id = %(userId)s)""")
1238 if data.events_order in [schemas.SearchEventOrder.THEN, schemas.SearchEventOrder.AND]:
1239 sequence_pattern = [f'(?{i + 1}){c.get("time", "")}' for i, c in enumerate(events_conditions)]
1240 sub_join = ""
1241 type_conditions = []
1242 value_conditions = []
1243 _value_conditions = []
1244 sequence_conditions = []
1245 for c in events_conditions:
1246 if c['type'] not in type_conditions:
1247 type_conditions.append(c['type'])
1249 if c.get('condition') \
1250 and c['condition'] not in value_conditions \
1251 and c['condition'] % full_args not in _value_conditions:
1252 value_conditions.append(c['condition'])
1253 _value_conditions.append(c['condition'] % full_args)
1255 sequence_conditions.append(c['type'])
1256 if c.get('condition'):
1257 sequence_conditions[-1] += " AND " + c["condition"]
1259 del _value_conditions
1260 if len(events_conditions) > 0:
1261 events_conditions_where.append(f"({' OR '.join([c for c in type_conditions])})")
1262 del type_conditions
1263 # if len(value_conditions) > 0:
1264 # events_conditions_where.append(f"({' OR '.join([c for c in value_conditions])})")
1265 del value_conditions
1266 if len(events_conditions_not) > 0:
1267 _value_conditions_not = []
1268 value_conditions_not = []
1269 for c in events_conditions_not:
1270 p = f"{c['type']} AND {c['condition']}"
1271 _p = p % full_args
1272 if _p not in _value_conditions_not:
1273 _value_conditions_not.append(_p)
1274 value_conditions_not.append(p)
1276 sub_join = f"""LEFT ANTI JOIN ( SELECT DISTINCT sub.session_id
1277 FROM {MAIN_EVENTS_TABLE} AS sub
1278 WHERE {' AND '.join(__events_where_basic)}
1279 AND ({' OR '.join([c for c in value_conditions_not])})) AS sub USING(session_id)"""
1280 del _value_conditions_not
1281 del value_conditions_not
1283 if data.events_order == schemas.SearchEventOrder.THEN:
1284 having = f"""HAVING sequenceMatch('{''.join(sequence_pattern)}')(toDateTime(main.created_at),{','.join(sequence_conditions)})"""
1285 else:
1286 having = f"""HAVING {" AND ".join([f"countIf({c})>0" for c in list(set(sequence_conditions))])}"""
1288 events_query_part = f"""SELECT main.session_id,
1289 MIN(main.created_at) AS first_event_ts,
1290 MAX(main.created_at) AS last_event_ts
1291 FROM {MAIN_EVENTS_TABLE} AS main {events_extra_join}
1292 {sub_join}
1293 WHERE {" AND ".join(events_conditions_where)}
1294 GROUP BY session_id
1295 {having}"""
1296 else:
1297 type_conditions = []
1298 sequence_conditions = []
1299 has_values = False
1300 for c in events_conditions:
1301 if c['type'] not in type_conditions:
1302 type_conditions.append(c['type'])
1303 if c.get('condition'):
1304 has_values = True
1305 sequence_conditions.append(c['type'] + " AND " + c["condition"])
1307 if len(events_conditions) > 0:
1308 events_conditions_where.append(f"({' OR '.join([c for c in type_conditions])})")
1310 if len(events_conditions_not) > 0:
1311 has_values = True
1312 _value_conditions_not = []
1313 value_conditions_not = []
1314 for c in events_conditions_not:
1315 p = f"{c['type']} AND {c['condition']}".replace("sub.", "main.")
1316 _p = p % full_args
1317 if _p not in _value_conditions_not:
1318 _value_conditions_not.append(_p)
1319 value_conditions_not.append(p)
1320 del _value_conditions_not
1321 # sequence_conditions += value_conditions_not
1322 events_extra_join += f"""LEFT ANTI JOIN ( SELECT DISTINCT session_id
1323 FROM {MAIN_EVENTS_TABLE} AS main
1324 WHERE {' AND '.join(__events_where_basic)}
1325 AND ({' OR '.join(value_conditions_not)})) AS sub USING(session_id)"""
1327 if has_values and len(sequence_conditions) > 0:
1328 events_conditions = [c for c in list(set(sequence_conditions))]
1329 events_conditions_where.append(f"({' OR '.join(events_conditions)})")
1331 events_query_part = f"""SELECT main.session_id,
1332 MIN(main.created_at) AS first_event_ts,
1333 MAX(main.created_at) AS last_event_ts
1334 FROM {MAIN_EVENTS_TABLE} AS main {events_extra_join}
1335 WHERE {" AND ".join(events_conditions_where)}
1336 GROUP BY session_id"""
1337 else:
1338 data.events = []
1339 # ---------------------------------------------------------------------------
1340 if data.startTimestamp is not None:
1341 extra_constraints.append("s.datetime >= toDateTime(%(startDate)s/1000)")
1342 if data.endTimestamp is not None:
1343 extra_constraints.append("s.datetime <= toDateTime(%(endDate)s/1000)")
1345 extra_join = ""
1346 if issue is not None:
1347 extra_join = """
1348 INNER JOIN (SELECT DISTINCT session_id
1349 FROM experimental.issues
1350 INNER JOIN experimental.events USING (issue_id)
1351 WHERE issues.type = %(issue_type)s
1352 AND issues.context_string = %(issue_contextString)s
1353 AND issues.project_id = %(projectId)s
1354 AND events.project_id = %(projectId)s
1355 AND events.issue_type = %(issue_type)s
1356 AND events.created_at >= toDateTime(%(startDate)s/1000)
1357 AND events.created_at <= toDateTime(%(endDate)s/1000)
1358 ) AS issues ON (f.session_id = issues.session_id)
1359 """
1360 full_args["issue_contextString"] = issue["contextString"]
1361 full_args["issue_type"] = issue["type"]
1362 elif len(issues) > 0:
1363 issues_conditions = []
1364 for i, f in enumerate(issues):
1365 f_k_v = f"f_issue_v{i}"
1366 f_k_s = f_k_v + "_source"
1367 full_args |= sh.multi_values(f.value, value_key=f_k_v) | {f_k_s: f.source}
1368 issues_conditions.append(sh.multi_conditions(f"issues.type=%({f_k_v})s", f.value,
1369 value_key=f_k_v))
1370 issues_conditions[-1] = f"({issues_conditions[-1]} AND issues.context_string=%({f_k_s})s)"
1371 extra_join = f"""INNER JOIN (SELECT DISTINCT events.session_id
1372 FROM experimental.issues
1373 INNER JOIN experimental.events USING (issue_id)
1374 WHERE issues.project_id = %(projectId)s
1375 AND events.project_id = %(projectId)s
1376 AND events.created_at >= toDateTime(%(startDate)s/1000)
1377 AND events.created_at <= toDateTime(%(endDate)s/1000)
1378 AND {" OR ".join(issues_conditions)}
1379 ) AS issues USING (session_id)"""
1381 if extra_event:
1382 extra_event = f"INNER JOIN ({extra_event}) AS extra_event USING(session_id)"
1383 if extra_conditions and len(extra_conditions) > 0:
1384 _extra_or_condition = []
1385 for i, c in enumerate(extra_conditions):
1386 if sh.isAny_opreator(c.operator) and c.name != schemas.EventType.REQUEST_DETAILS.value:
1387 continue
1388 e_k = f"ec_value{i}"
1389 op = sh.get_sql_operator(c.operator)
1390 c.value = helper.values_for_operator(value=c.value, op=c.operator)
1391 full_args |= sh.multi_values(c.value, value_key=e_k)
1392 if c.name in (schemas.EventType.LOCATION.value, schemas.EventType.REQUEST.value):
1393 _extra_or_condition.append(
1394 sh.multi_conditions(f"extra_event.url_path {op} %({e_k})s",
1395 c.value, value_key=e_k))
1396 elif c.name == schemas.EventType.REQUEST_DETAILS.value:
1397 for j, c_f in enumerate(c.filters):
1398 if sh.isAny_opreator(c_f.operator) or len(c_f.value) == 0:
1399 continue
1400 e_k += f"_{j}"
1401 op = sh.get_sql_operator(c_f.operator)
1402 c_f.value = helper.values_for_operator(value=c_f.value, op=c_f.operator)
1403 full_args |= sh.multi_values(c_f.value, value_key=e_k)
1404 if c_f.name == schemas.FetchFilterType.FETCH_URL.value:
1405 _extra_or_condition.append(
1406 sh.multi_conditions(f"extra_event.url_path {op} %({e_k})s",
1407 c_f.value, value_key=e_k))
1408 else:
1409 logging.warning(f"unsupported extra_event type:${c.name}")
1410 if len(_extra_or_condition) > 0:
1411 extra_constraints.append("(" + " OR ".join(_extra_or_condition) + ")")
1412 else:
1413 extra_event = ""
1414 if errors_only:
1415 query_part = f"""{f"({events_query_part}) AS f" if len(events_query_part) > 0 else ""}"""
1416 else:
1417 if len(events_query_part) > 0:
1418 if len(global_properties) > 0:
1419 extra_join += f""" INNER JOIN (SELECT DISTINCT session_id
1420 FROM {MAIN_EVENTS_TABLE} AS e
1421 WHERE {" AND ".join(global_properties)}) AS global_filters USING(session_id)"""
1422 if len(global_properties_negative) > 0:
1423 extra_join += f""" LEFT JOIN (SELECT DISTINCT session_id
1424 FROM {MAIN_EVENTS_TABLE} AS e
1425 WHERE project_id=%(project_id)s
1426 AND created_at >= toDateTime(%(startDate)s/1000)
1427 AND created_at <= toDateTime(%(endDate)s/1000)
1428 AND ({" OR ".join(global_properties_negative)})) AS negative_global_filters USING(session_id)"""
1429 extra_constraints.append("isNull(negative_global_filters.session_id)")
1430 extra_join += f"""INNER JOIN (SELECT DISTINCT ON (session_id) *
1431 FROM {MAIN_SESSIONS_TABLE} AS s {extra_event}
1432 WHERE {" AND ".join(extra_constraints)}
1433 ORDER BY _timestamp DESC) AS s ON(s.session_id=f.session_id)"""
1434 else:
1435 deduplication_keys = ["session_id"] + extra_deduplication
1436 if len(global_properties) > 0:
1437 extra_join += f""" INNER JOIN (SELECT DISTINCT session_id
1438 FROM {MAIN_EVENTS_TABLE} AS e
1439 WHERE {" AND ".join(global_properties)}) AS global_filters USING(session_id)"""
1440 if len(global_properties_negative) > 0:
1441 extra_join += f""" LEFT JOIN (SELECT DISTINCT session_id
1442 FROM {MAIN_EVENTS_TABLE} AS e
1443 WHERE project_id=%(project_id)s
1444 AND created_at >= toDateTime(%(startDate)s/1000)
1445 AND created_at <= toDateTime(%(endDate)s/1000)
1446 AND ({" OR ".join(global_properties_negative)})) AS negative_global_filters USING(session_id)"""
1447 extra_constraints.append("isNull(negative_global_filters.session_id)")
1448 extra_join = f"""(SELECT *
1449 FROM {MAIN_SESSIONS_TABLE} AS s {extra_join} {extra_event}
1450 WHERE {" AND ".join(extra_constraints)}
1451 ORDER BY _timestamp DESC
1452 LIMIT 1 BY {",".join(deduplication_keys)}) AS s"""
1453 query_part = f"""\
1454 FROM {f"({events_query_part}) AS f" if len(events_query_part) > 0 else ""}
1455 {extra_join}
1456 {extra_from}
1457 """
1458 return full_args, query_part