Hey guys, I’ve been trying to use UniqueConstraint on my project and although it works, I ran into an issue that I would like your expertise to figure out.
My project has a rule where each user can only have 1 favorite query, so while scanning the internet I discovered UniqueConstraint since I’m using a Postgres database I could use the condition setting, I also ran into this piece of code:
from django.db.models import BooleanField, ExpressionWrapper, Q
QueryModel.objects.filter(user=my_user).update(
is_favorite=ExpressionWrapper(Q(pk=my_pk), BooleanField())
)
Which is supposed to change is_favorite to False from any user and set it to True to the specified one.
My problem is that the above piece of code does not work, it can change the statuses of queries but it returns an error after a query goes from False → True → False → True, so instead, I’m having to run 2 queries, one to set everything to False and another to set it to True.
I had some discussions about this on stackoverflow but we couldn’t figure out why the code wouldn’t work, why we were getting errors. I’ll post some code snippets but I advise anyone interested in this to also read the StackOverflow discussion.
Model:
class QueryModel(models.Model):
id = models.AutoField(_("query id"), primary_key=True)
user = models.ForeignKey(UserModel, on_delete=models.CASCADE)
name = models.CharField(_("query name"), max_length=150)
status = models.CharField(
_("query status"),
choices=QueryStatus.choices,
default=QueryStatus.ACTIVE,
)
is_favorite = models.BooleanField(_("favorite"), default=False)
date_created = models.DateTimeField(_("date created"), auto_now_add=True)
class Meta(object):
app_label = "app"
db_table = "query"
constraints = [
models.UniqueConstraint(
fields=("user",),
condition=Q(is_favorite=True),
name="unique_favorite_per_user",
)
]
View:
class QueryView(viewsets.ModelViewSet):
"""
View to list all users in the system.
* Requires token authentication.
* Only admin users are able to access this view.
"""
authentication_classes = [TokenAuthentication]
permission_classes = [IsAuthenticated]
serializer_class = QuerySerializer
aws_client = AWSClient()
def get_queryset(self):
"""
This view should return a list of all the purchases
for the currently authenticated user.
"""
self._paginator = None
return QueryModel.objects.filter(user=self.request.user).order_by("id")
def create(self, request):
serializer = QuerySerializer(
data={
"user": request.user.id,
"name": request.data["name"],
"expiration_date": request.data["expiration_date"],
"json": request.data["json"],
}
)
serializer.is_valid(raise_exception=True)
serializer.save()
return Response(serializer.data, status=status.HTTP_201_CREATED)
def partial_update(self, request, pk=None):
serializer = QuerySerializer(data=request.data, partial=True)
serializer.is_valid(raise_exception=True)
if "is_favorite" in serializer.validated_data.keys():
self.get_queryset().update(
is_favorite=ExpressionWrapper(Q(pk=pk), BooleanField())
)
return super().partial_update(request, pk)
URLs:
router = DefaultRouter(trailing_slash=False)
router.register("auth", AuthView, basename="auth")
router.register("query", QueryView, basename="query")
version_info = json.load(open("./version.json"))
@api_view(["GET"])
def health_check(request):
"""Check if the endpoint is on. Used by container orchestration system."""
return Response({"status": "pass", **version_info})
urlpatterns = [
path("", health_check),
*router.urls,
]
Traceback:
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "unique_favorite_per_user"
DETAIL: Key (user_workspace_id)=(2) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/django/core/handlers/exception.py", line 55, in inner
response = get_response(request)
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/core/handlers/base.py", line 197, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/views/decorators/csrf.py", line 65, in _view_wrapper
return view_func(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/rest_framework/viewsets.py", line 124, in view
return self.dispatch(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/rest_framework/views.py", line 509, in dispatch
response = self.handle_exception(exc)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/rest_framework/views.py", line 469, in handle_exception
self.raise_uncaught_exception(exc)
File "/usr/local/lib/python3.11/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
raise exc
File "/usr/local/lib/python3.11/site-packages/rest_framework/views.py", line 506, in dispatch
response = handler(request, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/var/lib/trends_backend/trends/views/query.py", line 61, in partial_update
self.get_queryset().update(
File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1253, in update
rows = query.get_compiler(self.db).execute_sql(CURSOR)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1990, in execute_sql
cursor = super().execute_sql(result_type)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 122, in execute
return super().execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 79, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 100, in _execute
with self.db.wrap_database_errors:
File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.IntegrityError: duplicate key value violates unique constraint "unique_favorite_per_user"
DETAIL: Key (user_id)=(2) already exists.
Indexes created by Django Migrate:
CREATE UNIQUE INDEX unique_favorite_per_user ON trends.query USING btree (user_id) WHERE is_favorite
If you guys need anymore information let me know and I’ll update the post.