Hey, I did not find the solution for following problem and I hope not to duplicate questtion…
I have following models, and I need a unique_together / UniqueConstraint…
class Area(models.Model):
...
class Group(models.Model):
...
members = models.ManyToManyField('User', ...)
class Workshop(models.Model):
...
lecturer = models.ForeignKey('User', ...)
area = models.ForeignKey('Area', ...)
group_of_participants = models.ManyToManyField('Group', ...)
...
in forms.py in → class WorkshopForm in the clean() method I Try to check for object existence:
try:
obj = Workshop.models.get(
lecturer=self.cleaned_data['lecturer'],
area=self.cleaned_data['area'],
group_of_participants__id__in=self.cleaned_data['group_of_participants'],
)
except W...DoesNotExist: ...
obj = None
if obj:
raise ValidationError(....)
the problem is the above query raises MultipleObjectsReturned error:
…App.models.Workshop.MultipleObjectsReturned: get() returned more than one Workshop – it returned 4!
this gives me a queryset, what does not help me:
obj = Workshop.models.filter(
lecturer=self.cleaned_data['lecturer'],
area=self.cleaned_data['area'],
group_of_participants__id__in=self.cleaned_data['group_of_participants'],
)
How can I get() that single object of ‘Workshop’ by the exact queryset of ‘group_of_participants’ as criteria?
Thank You in advance!
If you are absolutely sure that there is only one instance of Workshop satisfying this requirement, you could use the first() function after the filter.
Or, if there is the possibility that there might validly be multiple Workshop instances satisfying this filter, you could use the distinct() function to eliminate duplicates and then check to verify that you’ve only got one result - and then retrieve that instance from the queryset.
The problem is first() & distinct() can not solv the problem because:
There are ‘Workshop’ objects with the same ‘lecturer’ & ‘area’ but different combinations of ‘group_of_participants’.
For e.g. :
- Workshop(lecturer=‘James’, area=‘team management’, group_of_participants=[A, B]
- Workshop(lecturer=‘James’, area=‘team management’, group_of_participants=[B, C, D]
- Workshop(lecturer=‘James’, area=‘team management’, group_of_participants=[C, D]
I can make a query group_of_participants in the form’s clean() method:
qs_group = Group.objects.filter(id__in=[C, D])
But
try:
obj = Workshop.objects.get(....) # in the orig. code above 'misspelled' :|
raises self.model.MultipleObjectsReturned() because Workshop 2. & 3. are returned…
so that’s why i need to query by the qs_group also and I do not know how to do that.
That’s why I was asking about that - it wasn’t clear from your original description.
If I can rephrase this to ensure I understand the situation, you want the Workshop instances where the lecturer and area fields match the corresponding fields, and that all of the group_of_participants related to Workshop are in the submitted group_of_participants.
Needing further clarification - does this list also need to be complete, such that only the group_of_participants from the form are in the group_of_participants field?
Thank you for the reply!
Yes, the group_of_participants must also be the exact match of the queryset! of the Group objects.
that is why i need Workshop.objects.get() method - returning single object - instead of Workshop.objects.filter() to satisfy the constraint by 3 criteria.
So I can tell the Frontend User, that :
Workshop(lecturer=‘James’, area=‘team management’, group_of_participants=[C, D]
is already in DB and no copies are allowed.
Well, there’s another way of looking at this. Do you actually need that Workshop object, or are you only trying to determine whether an instance exists matching this criteria? These are two slightly different situations, and the existence test is actually a lot easier.
The only solution I can come up with to retrieve the Workshop object involves dynamically creating the query to test the individual elements.
It’s going to look something like this:
qs = Workshop.objects.annotate(
group_count=Count('group_of_participants')
).filter(
group_count=len(self.cleaned_data['group_of_participants']),
lecturer=self.cleaned_data['lecturer'],
area=self.cleaned_data['area']
)
for participant in self.cleaned_data['group_of_participants']:
qs = qs.filter(group_of_participants=participant)
workshop = qs.get()
I just need to check for existence.
Your solution inspired me to use annotate, but without filtering the qs in a forloop - like this:
id_list = self.cleaned_data['group_of_participants']
obj = Workshop.objects.annotate(
total_group_count=Count('group_of_participants'),
corresp_group_count=Count(
'group_of_participants',
filter=Q(group_of_participants__id=id_list)
)
).filter(
lecturer=self.cleaned_data['lecturer'],
area=self.cleaned_data['area']
total_group_count=len(set(qs_groups)),
corresp_group_count=len(set(qs_groups))
).get()
in the near future - (I need a little more free time 
…) I will test both for timecomplexity, i don’t know yet, which is faster.
Dear Ken, THANK YOU VERY MUCH!
Given the clarifications of what you’re looking for, I’m thinking this solution is about as good as any. What I thought was going to be an easier solution for a pure existance test isn’t really any simpler.
Now, if you’re using PostgreSQL, you can leverage the PostgreSQL extensions:
from django.contrib.postgres.fields import ArraySubquery
group_list = self.cleaned_data['group_of_participants']
group_list.sort()
qs = Workshop.objects.filter(
lecturer=self.cleaned_data['lecturer'],
area=self.cleaned_data['area']
).annotate(
groups=ArraySubquery(
Workshop.objects.filter(id=OuterRef('id')).values_list('group_of_participants')
).filter(groups=group_list)
This will return a matching result (if any). Add a .exists() function on the end to make this a boolean expression returning True or False.
Note, I’m not actually filtering the qs in a loop. I’m building one qs with multiple .filter functions chained together.
Keep in mind that querysets are lazy, and will not be evaluated until a resolution is required (such as a .get())