def _annotate_steps( self, team_id: int, funnel_steps: QuerySet, date_query: Dict[str, datetime.date], properties: Dict[str, str], ) -> Dict[str, Subquery]: annotations = {} for index, step in enumerate(funnel_steps): filter_key = ("event" if step.get("type") == TREND_FILTER_TYPE_EVENTS else "action__pk") annotations["step_{}".format(index)] = Subquery( Event.objects.all().annotate(person_id=OuterRef("id")).filter( **{ filter_key: step["id"] }, team_id=team_id, distinct_id__in=Subquery( PersonDistinctId.objects.filter( team_id=team_id, person_id=OuterRef( "person_id")).values("distinct_id")), **({ "timestamp__gt": OuterRef("step_{}".format(index - 1)) } if index > 0 else {}), **date_query).filter(properties_to_Q(properties)).filter( properties_to_Q(step['properties']) if step. get('properties') is not None else Q()).order_by( "timestamp").values("timestamp")[:1]) return annotations
def _filter_events(self, request: request.Request) -> Q: filters = Q() date_from, date_to = self._get_dates_from_request(request=request) if date_from: filters &= Q(timestamp__gte=date_from) if date_to: interval = request.GET.get('interval') relativity = relativedelta(days=1) if interval == 'hour': relativity = relativedelta(hours=1) elif interval == 'minute': relativity = relativedelta(minutes=1) elif interval == 'week': relativity = relativedelta(weeks=1) elif interval == 'month': relativity = relativedelta( months=1 ) - relativity # go to last day of month instead of first of next filters &= Q(timestamp__lte=date_to + relativity) if not request.GET.get('properties'): return filters properties = json.loads(request.GET['properties']) filters &= properties_to_Q(properties) return filters
def person_ids(self): person_ids = [] for group in self.groups: if group.get('action_id'): action = Action.objects.get(pk=group['action_id'], team_id=self.team_id) people = Person.objects.filter( team_id=self.team_id, ).annotate(has_action=Subquery( Event.objects.filter_by_action(action).filter( person_id=OuterRef('id'), **({ 'timestamp__gt': timezone.now() - relativedelta(days=group['days']) } if group.get('days') else {} )).values('id')[:1])).filter( has_action__isnull=False) person_ids.extend([person.id for person in people]) elif group.get('properties'): properties = properties_to_Q(group['properties']) person_ids.extend([ person_id for person_id in Person.objects.filter( properties, team_id=self.team_id).order_by( '-id').values_list('pk', flat=True) ]) return person_ids
def test_contains(self): Event.objects.create(team=self.team, event='$pageview') event2 = Event.objects.create( team=self.team, event='$pageview', properties={'$current_url': 'https://whatever.com'}) properties = {'$current_url__icontains': 'whatever'} events = Event.objects.filter(properties_to_Q(properties)) self.assertEqual(events.get(), event2)
def test_simple(self): Event.objects.create(team=self.team, event='$pageview') Event.objects.create( team=self.team, event='$pageview', properties={'$current_url': 'https://whatever.com'}) properties = {'$current_url': 'https://whatever.com'} events = Event.objects.filter(properties_to_Q(properties)) self.assertEqual(events.count(), 1)
def _filter_events(self, request: request.Request) -> Q: filters = Q() date_from, date_to = self._get_dates_from_request(request=request) if date_from: filters &= Q(timestamp__gte=date_from) if date_to: filters &= Q(timestamp__lte=date_to + relativedelta(days=1)) if not request.GET.get('properties'): return filters properties = json.loads(request.GET['properties']) filters &= properties_to_Q(properties) return filters
def test_does_not_contain(self): event1 = Event.objects.create(team=self.team, event='$pageview') event2 = Event.objects.create( team=self.team, event='$pageview', properties={'$current_url': 'https://something.com'}) Event.objects.create( team=self.team, event='$pageview', properties={'$current_url': 'https://whatever.com'}) properties = {'$current_url__not_icontains': 'whatever.com'} events = Event.objects.filter(properties_to_Q(properties)) self.assertEqual(events[0], event1) self.assertEqual(events[1], event2) self.assertEqual(len(events), 2)
def _filter_request(self, request: request.Request, queryset: QuerySet) -> QuerySet: for key, value in request.GET.items(): if key in ('event', 'ip'): pass elif key == 'after': queryset = queryset.filter(timestamp__gt=request.GET['after']) elif key == 'before': queryset = queryset.filter(timestamp__lt=request.GET['before']) elif key == 'person_id': person = Person.objects.get(pk=request.GET['person_id']) queryset = queryset.filter(distinct_id__in=person.distinct_ids) elif key == 'distinct_id': queryset = queryset.filter(distinct_id=request.GET['distinct_id']) elif key == 'action_id': queryset = queryset.filter_by_action(Action.objects.get(pk=value)) # type: ignore elif key == 'properties': queryset = queryset.filter(properties_to_Q(json.loads(value))) return queryset
def people_filter(self): filters = Q() for group in self.groups: if group.get("action_id"): action = Action.objects.get(pk=group["action_id"], team_id=self.team_id) events = (Event.objects.filter_by_action(action).filter( team_id=self.team_id, **({ "timestamp__gt": timezone.now() - relativedelta(days=group["days"]) } if group.get("days") else {})).order_by("distinct_id"). distinct("distinct_id").values("distinct_id")) filters |= Q(persondistinctid__distinct_id__in=events) elif group.get("properties"): properties = properties_to_Q(group["properties"]) filters |= Q(properties) return filters
def test_multiple(self): event2 = Event.objects.create(team=self.team, event='$pageview', properties={ '$current_url': 'https://something.com', 'another_key': 'value' }) Event.objects.create( team=self.team, event='$pageview', properties={'$current_url': 'https://something.com'}) properties = { '$current_url__icontains': 'something.com', 'another_key': 'value' } events = Event.objects.filter(properties_to_Q(properties)) self.assertEqual(events[0], event2) self.assertEqual(len(events), 1)
def people_filter(self): filters = Q() for group in self.groups: if group.get('action_id'): action = Action.objects.get(pk=group['action_id'], team_id=self.team_id) events = Event.objects.filter_by_action(action).filter( team_id=self.team_id, **({ 'timestamp__gt': timezone.now() - relativedelta(days=group['days']) } if group.get('days') else {})).order_by('distinct_id').distinct( 'distinct_id').values('distinct_id') filters |= Q(persondistinctid__distinct_id__in=events) elif group.get('properties'): properties = properties_to_Q(group['properties']) filters |= Q(properties) return filters
def _filter_events(self, filter: Filter) -> Q: filters = Q() if filter.date_from: filters &= Q(timestamp__gte=filter.date_from) if filter.date_to: relativity = relativedelta(days=1) if filter.interval == 'hour': relativity = relativedelta(hours=1) elif filter.interval == 'minute': relativity = relativedelta(minutes=1) elif filter.interval == 'week': relativity = relativedelta(weeks=1) elif filter.interval == 'month': relativity = relativedelta( months=1 ) - relativity # go to last day of month instead of first of next filters &= Q(timestamp__lte=filter.date_to + relativity) if filter.properties: filters &= properties_to_Q(filter.properties) return filters