def filter_for_api(self, academic_year):
from partnership.models import PartnershipYear, PartnershipAgreement
return self.annotate(
current_academic_year=models.Value(
academic_year.id, output_field=models.AutoField()),
).annotate(
has_years_in=models.Exists(
PartnershipYear.objects.filter(
partnership=OuterRef('partnership_id'),
academic_year=academic_year,
)),
has_valid_agreement_in_current_year=models.Exists(
PartnershipAgreement.objects.filter(
partnership=OuterRef('partnership_id'),
status=AgreementStatus.VALIDATED.name,
start_academic_year__year__lte=academic_year.year,
end_academic_year__year__gte=academic_year.year,
)),
).filter(
# If mobility, should have agreement for current year
# and have a partnership year for current year
Q(
partnership__partnership_type=PartnershipType.MOBILITY.name,
has_valid_agreement_in_current_year=True,
has_years_in=True,
)
# Else all other types do not need agreement
| (~Q(partnership__partnership_type=PartnershipType.MOBILITY.name)
& Q(partnership__end_date__gte=Now())),
# And must be public
partnership__is_public=True,
)
def get_deleted(self, model, model_db=None):
model_db = model_db or router.db_for_write(model)
connection = connections[self.db]
if self.db == model_db and connection.vendor in ("sqlite", "postgresql", "oracle"):
pk_field_name = model._meta.pk.name
object_id_cast_target = model._meta.get_field(pk_field_name)
if django.VERSION >= (2, 1):
# django 2.0 contains a critical bug that doesn't allow the code below to work,
# fallback to casting primary keys then
# see https://code.djangoproject.com/ticket/29142
if django.VERSION < (2, 2):
# properly cast autofields for django before 2.2 as it was fixed in django itself later
# see https://github.com/django/django/commit/ac25dd1f8d48accc765c05aebb47c427e51f3255
object_id_cast_target = {
"AutoField": models.IntegerField(),
"BigAutoField": models.BigIntegerField(),
}.get(object_id_cast_target.__class__.__name__, object_id_cast_target)
casted_object_id = Cast(models.OuterRef("object_id"), object_id_cast_target)
model_qs = (
model._default_manager
.using(model_db)
.filter(**{pk_field_name: casted_object_id})
)
else:
model_qs = (
model._default_manager
.using(model_db)
.annotate(_pk_to_object_id=Cast("pk", Version._meta.get_field("object_id")))
.filter(_pk_to_object_id=models.OuterRef("object_id"))
)
# conditional expressions are being supported since django 3.0
# DISTINCT ON works only for Postgres DB
if connection.vendor == "postgresql" and django.VERSION >= (3, 0):
subquery = (
self.get_for_model(model, model_db=model_db)
.filter(~models.Exists(model_qs))
.order_by("object_id", "-pk")
.distinct("object_id")
.values("pk")
)
else:
subquery = (
self.get_for_model(model, model_db=model_db)
.annotate(pk_not_exists=~models.Exists(model_qs))
.filter(pk_not_exists=True)
.values("object_id")
.annotate(latest_pk=models.Max("pk"))
.values("latest_pk")
)
else:
# We have to use a slow subquery.
subquery = self.get_for_model(model, model_db=model_db).exclude(
object_id__in=list(
model._default_manager.using(model_db).values_list("pk", flat=True).order_by().iterator()
),
).values_list("object_id").annotate(
latest_pk=models.Max("pk")
).order_by().values_list("latest_pk", flat=True)
# Perform the subquery.
return self.filter(pk__in=subquery)
def with_event_status(self):
return self.annotate(
calculated_has_opened=models.Exists(
Event.objects.filter(mail=models.OuterRef("id"), name=EVENT_TYPES.OPENED)),
calculated_has_delivered=models.Exists(
Event.objects.filter(mail=models.OuterRef("id"), name=EVENT_TYPES.DELIVERED)),
calculated_has_clicked=models.Exists(
Event.objects.filter(mail=models.OuterRef("id"), name=EVENT_TYPES.CLICKED)),
)
def links_by_user(username, user=None):
qs_reactions = Reaction.objects.filter(post=models.OuterRef('pk'),
owner=user)
qs_added = Post.objects.filter(origin=models.OuterRef('pk'),
owner=user)
return Post.objects.filter(owner__user__username=username).annotate(
is_reacted=models.Exists(queryset=qs_reactions)).annotate(
is_added=models.Exists(
queryset=qs_added)).order_by('-created_at')
def to_copy(self, connector_name: str) -> models.QuerySet:
"""Get a queryset of locations that must be copied.
:returns: a queryset of FileStorage objects for which a new copy of
StorageLocation object belonging to the given connector must be
created.
"""
location_settings = copy.deepcopy(
STORAGE_CONNECTORS.get(connector_name, {}))
rules: dict = location_settings.get("config", {}).get("copy", {})
if not rules:
return FileStorage.objects.none()
process_overrides = rules.pop("process_type", {})
data_slug_overrides = rules.pop("data_slug", {})
whens = [
models.When(
data__slug=data_slug,
then=timedelta(
days=self._preprocess_delay(override_rule.get("delay"))),
) for data_slug, override_rule in data_slug_overrides.items()
]
whens += [
models.When(
data__process__type__startswith=process_type,
then=timedelta(
days=self._preprocess_delay(override_rule.get("delay"))),
) for process_type, override_rule in process_overrides.items()
]
default_delay = self._preprocess_delay(rules.get("delay"))
return (FileStorage.objects.filter(
data__status=Data.STATUS_DONE).filter(
created__lt=now() # type: ignore
- models.Case(
*whens,
default=timedelta(days=default_delay),
output_field=models.DurationField(),
)).annotate(
any_location_exists=models.Exists(
StorageLocation.objects.filter(
file_storage=models.OuterRef("id"))),
to_location_exists=models.Exists(
StorageLocation.objects.filter(
file_storage=models.OuterRef("id"),
connector_name=connector_name,
)),
).filter(any_location_exists=True,
to_location_exists=False).distinct())
def annotate_assessments(self, assessment_set):
assessment_set = super().annotate_assessments(assessment_set)
most_recent_state = (self.get_queryset().filter(
assessment_id=models.OuterRef('id'),
attempt_number=self.max_attempt_number()))
return assessment_set.annotate(
is_complete=models.Exists(
most_recent_state.filter(state=CompletionState.COMPLETE)),
is_partially_complete=models.Exists(
most_recent_state.filter(
state=CompletionState.PARTIALLY_COMPLETE)),
completion_state=models.Subquery(
most_recent_state.values('state')))
def feeds(username):
follower_ids = Relationship.objects.filter(
owner__user__username=username).values_list('follow_id',
flat=True).distinct()
qs_reacted = Reaction.objects.filter(post=models.OuterRef('pk'),
owner__user__username=username)
qs_added = Post.objects.filter(origin=models.OuterRef('pk'),
owner__user__username=username)
return Post.objects.filter(
models.Q(owner_id__in=follower_ids)
| models.Q(owner__user__username=username)).annotate(
is_reacted=models.Exists(queryset=qs_reacted)).annotate(
is_added=models.Exists(
queryset=qs_added)).order_by('-created_at')
def get_deleted(self, model, model_db=None):
model_db = model_db or router.db_for_write(model)
if self.db == model_db:
model_qs = (model._default_manager.using(model_db).annotate(
_pk_to_object_id=Cast('pk', Version._meta.get_field(
'object_id'))).filter(
_pk_to_object_id=models.OuterRef('object_id')))
subquery = (self.get_for_model(model, model_db=model_db).annotate(
pk_not_exists=~models.Exists(model_qs)).filter(
pk_not_exists=True).values('object_id').annotate(
latest_pk=models.Max('pk')).values('latest_pk'))
else:
# We have to use a slow subquery.
existing_pks = model._default_manager.using(model_db).values_list(
'pk', flat=True).order_by().iterator()
subquery = self.get_for_model(model, model_db=model_db).exclude(
object_id__in=list(existing_pks),
).values_list('object_id').annotate(
latest_pk=models.Max('pk')).order_by().values_list('latest_pk',
flat=True)
# Perform the subquery.
return self.filter(pk__in=subquery)
def get_monthly_rows(self):
"""
Gets all credit count and amounts grouped by month and categorised by security check status.
"""
# subquery used to determine if a credit's security check triggered any rules
rules_triggered = Check.objects.filter(credit=models.OuterRef('pk'),
rules__len__gte=1).values('pk')
queryset = (
# given all credits
Credit.objects_all
# take only credits since FIU checks went live
# take only credits with a check (i.e. debit card payment was completed)
.filter(created__date__gte='2020-01-02',
security_check__status__isnull=False)
# remove ordering
.order_by()
# truncate creation date and determine if associated check triggered rules
# (subquery is not ideal, but django can't seem to group by array field length being >= 1)
.annotate(date=TruncMonth('created'),
triggered_rules=models.Exists(rules_triggered)).values(
'date', 'security_check__status', 'triggered_rules')
# count credits and sum amounts
.annotate(count=models.Count('pk'), amount=models.Sum('amount'))
# group and order for display
.values('date', 'security_check__status', 'triggered_rules',
'count', 'amount').order_by('date',
'security_check__status',
'triggered_rules'))
# collect counts and amounts into monthly rows
grouped_rows = []
for date, group in itertools.groupby(queryset, lambda r: r['date']):
row = collections.defaultdict(int)
row['date'] = date
for item in group:
count = item['count']
amount = item['amount']
status = item['security_check__status']
triggered_rules = item['triggered_rules']
if status == 'accepted':
if triggered_rules:
row['accepted_count'] += count
row['accepted_amount'] += amount
else:
row['auto_accepted_count'] += count
row['auto_accepted_amount'] += amount
elif status == 'rejected' and triggered_rules:
row['rejected_count'] += count
row['rejected_amount'] += amount
elif status == 'pending' and triggered_rules:
row['pending_count'] += count
row['pending_amount'] += amount
else:
logger.warning(
f'Unexpected grouped check status: status={status} triggered_rules={triggered_rules}'
)
grouped_rows.append(row)
return grouped_rows
def get_generic_related_exists(
model,
related,
related_object_id_field="object_id",
related_content_type_field="content_type",
):
"""Used with QuerySet.annotate() to add an EXISTS clause
to a QuerySet where you want to select based on a ContentType
relation.
For an example see LikesAnnotationQuerySetMixin in
localhub.likes/models.py.
Args:
model (Model or Queryset): model class or QuerySet instance
related (Model): ContentType-related model
related_object_id_field (str, optional): field in content type model used as
foreign key (default: "object_id")
related_content_type_field (str, optional): field in content type model used
as FK to ContentType (default: "content_type")
Returns:
Exists
"""
return models.Exists(
_get_generic_related_by_id_and_content_type(
models.OuterRef("pk"),
model,
related,
related_object_id_field,
related_content_type_field,
).only("pk"))
def with_is_parent_owner_member(self, community):
return self.annotate(is_parent_owner_member=models.Exists(
Membership.objects.filter(
member=models.OuterRef("parent__owner__pk"),
community=community,
active=True,
)))
def get_max_deviations(
self,
submitter: UserProfile,
exercises: Iterable[Union[BaseExercise, int]],
) -> Iterable[TModel]:
"""
Returns the maximum deviations for the given submitter in the given
exercises (one deviation per exercise is returned). The deviation may
be granted to the submitter directly, or to some other submitter in
their group.
"""
deviations = (
self.filter(
models.Q(exercise__in=exercises)
& (
# Check that the owner of the deviation is the user, or
# some other user who has submitted the deviation's
# exercise with the user.
models.Q(submitter=submitter)
| models.Exists(
# Note the two 'submitters' filters.
Submission.objects.filter(
exercise=models.OuterRef('exercise'),
submitters=models.OuterRef('submitter'),
).filter(submitters=submitter, )))).select_related(
'exercise').order_by('exercise',
self.max_order_by))
previous_exercise_id = None
for deviation in deviations:
if deviation.exercise.id == previous_exercise_id:
continue
previous_exercise_id = deviation.exercise.id
yield deviation
def test_pickle_exists_queryset_still_usable(self):
group = Group.objects.create(name='group')
Event.objects.create(title='event', group=group)
groups = Group.objects.annotate(has_event=models.Exists(
Event.objects.filter(group_id=models.OuterRef('id')), ), )
groups2 = pickle.loads(pickle.dumps(groups))
self.assertSequenceEqual(groups2.filter(has_event=True), [group])
def get(self, request, *args, **kwargs):
program_id = kwargs.get('program')
programs = request.user.tola_user.available_programs.annotate(
indicators_count=models.Count('indicator'),
targets_exist=models.Exists(
PeriodicTarget.objects.filter(
indicator__program=models.OuterRef('pk')
)
),
tva_indicators_count=models.Subquery(
Indicator.rf_aware_objects.filter(
program=models.OuterRef('pk'),
target_frequency__in=Indicator.REGULAR_TARGET_FREQUENCIES + (Indicator.LOP, Indicator.MID_END)
).order_by().values('program').annotate(tva_count=models.Count('pk')).values('tva_count')[:1],
output_field=models.IntegerField()
)
).filter(
funding_status="Funded",
targets_exist=True,
reporting_period_start__isnull=False,
reporting_period_end__isnull=False,
indicators_count__gt=0
).order_by('name').values_list('pk', 'name', 'tva_indicators_count')
program_data = IPTTProgramSerializer.get_for_pk(program_id).data
react_data = {
'programs_list': list(programs),
'program_data': program_data,
}
return self.render_to_response({'react_context': react_data})
def with_has_subscribed(self, user):
if user.is_anonymous:
return self.annotate(has_subscribed=models.Value(
False, output_field=models.BooleanField()))
return self.annotate(has_subscribed=models.Exists(
Subscription.objects.filter(user=user,
podcast=models.OuterRef("pk"))))
def with_has_bookmarked(self, user):
if user.is_anonymous:
return self.annotate(has_bookmarked=models.Value(
False, output_field=models.BooleanField()))
return self.annotate(has_bookmarked=models.Exists(
Bookmark.objects.filter(user=user,
episode=models.OuterRef("episode"))))
def get_deleted(self, model, model_db=None):
model_db = model_db or router.db_for_write(model)
connection = connections[self.db]
if self.db == model_db and connection.vendor in ("sqlite",
"postgresql",
"oracle"):
model_qs = (model._default_manager.using(model_db).annotate(
_pk_to_object_id=Cast("pk", Version._meta.get_field(
"object_id"))).filter(
_pk_to_object_id=models.OuterRef("object_id")))
subquery = (self.get_for_model(model, model_db=model_db).annotate(
pk_not_exists=~models.Exists(model_qs)).filter(
pk_not_exists=True).values("object_id").annotate(
latest_pk=models.Max("pk")).values("latest_pk"))
else:
# We have to use a slow subquery.
subquery = self.get_for_model(model, model_db=model_db).exclude(
object_id__in=list(
model._default_manager.using(model_db).values_list(
"pk", flat=True).order_by().iterator()),
).values_list("object_id").annotate(
latest_pk=models.Max("pk")).order_by().values_list("latest_pk",
flat=True)
# Perform the subquery.
return self.filter(pk__in=subquery)
def annotate_subscription_data(qs, user):
subscribed_user_feed_mappings = SubscribedFeedUserMapping.objects.filter(
user=user, feed_id=models.OuterRef('uuid'))
return qs.annotate(
custom_title=models.Subquery(
subscribed_user_feed_mappings.values('custom_feed_title')),
is_subscribed=models.Exists(subscribed_user_feed_mappings),
)
def test_conditional_filter(self, application_model, version_model):
version_model.objects.filter(major=2).first().delete()
subquery = version_model.objects.filter(
application_id=models.OuterRef('pk'), version='2.0.0')
applications = application_model.objects.filter(
models.Exists(subquery))
assert len(applications) == 1
assert applications[0].versions.filter(version='2.0.0').exists()
def annotate_assessments(self, assessments):
has_attempts = (self.get_queryset().filter(
assessment_id=models.OuterRef('id')).order_by(
'-attempt_number'))
return assessments.annotate(
is_attempted=models.Exists(has_attempts),
attempted_at=models.Subquery(
has_attempts.values('created_at')[:1]))
def liked(self, user=None):
return self.annotate(likes_count=models.Count('likes', distinct=True),
comments_count=models.Count('comments',
distinct=True),
is_liked=models.Exists(
Like.objects.filter(
post_id=models.OuterRef('pk'),
created_by_id=user.pk)))
def with_subscribed(self, user: AnyUser) -> models.QuerySet:
"""Marks which recommendations are subscribed by this user."""
if user.is_anonymous:
return self.annotate(is_subscribed=models.Value(
False, output_field=models.BooleanField()))
return self.annotate(is_subscribed=models.Exists(
Subscription.objects.filter(
user=user, podcast=models.OuterRef("recommended"))))
def test_pickle_exists_kwargs_queryset_not_evaluated(self):
group = Group.objects.create(name='group')
Event.objects.create(title='event', group=group)
groups = Group.objects.annotate(has_event=models.Exists(
queryset=Event.objects.filter(group_id=models.OuterRef('id')), ), )
list(groups) # evaluate QuerySet.
with self.assertNumQueries(0):
self.assert_pickles(groups)
def get_deleted(self, model, model_db=None):
model_db = model_db or router.db_for_write(model)
connection = connections[self.db]
# If safedelete is being used then this function helps
# determine if our model is a SafeDeleteModel. If not installed
# then never is.
try:
from safedelete.models import is_safedelete_cls
except ImportError:
is_safedelete_cls = lambda: False
if self.db == model_db and connection.vendor in ("sqlite",
"postgresql",
"oracle"):
if is_safedelete_cls(model):
# Hack: added this hack to account for safe-delete
# We need to check not that the instance doesn't exist at all, but that it doesn't exist
# with a `deleted=null` field (it will still exist but deleted field will be set to datetime of
# safedelete)
model_qs = (model._default_manager.using(model_db).annotate(
_pk_to_object_id=Cast(
"pk", Version._meta.get_field("object_id"))).filter(
_pk_to_object_id=models.OuterRef("object_id"),
deleted__isnull=True))
else:
model_qs = (model._default_manager.using(model_db).annotate(
_pk_to_object_id=Cast(
"pk", Version._meta.get_field("object_id"))).filter(
_pk_to_object_id=models.OuterRef("object_id")))
subquery = (self.get_for_model(model, model_db=model_db).annotate(
pk_not_exists=~models.Exists(model_qs)).filter(
pk_not_exists=True).values("object_id").annotate(
latest_pk=models.Max("pk")).values("latest_pk"))
else:
if is_safedelete_cls(model):
# Hack: added this hack to account for safe-delete
subquery = self.get_for_model(
model, model_db=model_db).exclude(
object_id__in=list(
model._default_manager.using(model_db).filter(
deleted__isnull=True).values_list(
"pk", flat=True).order_by().iterator()),
).values_list("object_id").annotate(
latest_pk=models.Max("pk")).order_by().values_list(
"latest_pk", flat=True)
else:
# We have to use a slow subquery.
subquery = self.get_for_model(
model, model_db=model_db).exclude(
object_id__in=list(
model._default_manager.using(model_db).values_list(
"pk", flat=True).order_by().iterator()),
).values_list("object_id").annotate(
latest_pk=models.Max("pk")).order_by().values_list(
"latest_pk", flat=True)
# Perform the subquery.
return self.filter(pk__in=subquery)
def test_pickle_boolean_expression_in_Q__queryset(self):
group = Group.objects.create(name="group")
Event.objects.create(title="event", group=group)
groups = Group.objects.filter(
models.Q(
models.Exists(
Event.objects.filter(group_id=models.OuterRef("id")), )), )
groups2 = pickle.loads(pickle.dumps(groups))
self.assertSequenceEqual(groups2, [group])
def annotate_company_claps(qs, profile_id=-1):
"""
Annotates Companies with `user_did_clap` indicating if provided
profile has clapped
"""
return qs.annotate(user_did_clap=m.Exists(
Company.clappers.through.objects.filter(company_id=m.OuterRef("pk"),
profile_id=profile_id)))
def backpopulate_incomplete_profiles(apps, schema):
"""Backpopulate users who don't have a profile record"""
User = apps.get_model("users", "User")
Profile = apps.get_model("users", "Profile")
for user in User.objects.annotate(
has_profile=models.Exists(Profile.objects.filter(user=models.OuterRef("pk")))
).filter(has_profile=False):
Profile.objects.get_or_create(user=user)
def jobs_in_process(self):
from .models import DataSourceJob
subquery = DataSourceJob.objects.filter(
datasource=models.OuterRef("pk"),
job_state__in=[ProcessingState.PENDING, ProcessingState.PROCESSING],
)
return self.annotate(jobs_in_process=models.Exists(subquery))
def feedback_filter(self, queryset, name, value):
feedbacks = EventFeedback.objects.filter(
event__summit__id=models.OuterRef('summit__id'),
event__category__id=models.OuterRef('id'))
queryTmp = queryset.annotate(
has_feedback=models.Exists(feedbacks)).filter(has_feedback=True)
queryTmp = queryTmp.annotate(rate=SubQueryAvg(feedbacks, field="rate"))
return queryTmp
def get_feed(self):
from apps.blog.models import BlogPost
return BlogPost.objects.filter(blog__in=self.subscriptions.all()).annotate(
seen=models.Exists(
UserFeedSeen.objects.filter(user=self, post=models.OuterRef('pk')).only(
'pk'
)
)
)
