Hi everyone,
I just updated a project to 4.1, and a query stopped working. I tested with both SQLite and PostgreSQL, and i got the same result. I'm not 100% sure this is bug in Django 4.1, but it works fine on 4.0.7.
These are the models and the manager related to the query:
class PostManager(models.Manager):
def request_data(self, request_user):
liked_by_user = Value(False)
is_following = Value(False)
if request_user.is_authenticated:
# Check if the user has liked the post in each row of the query
liked_by_user = Exists(request_user.liked_posts.filter(id=OuterRef("id")))
is_following = Exists(request_user.following.filter(id=OuterRef("user__id")))
return is_owner, liked_by_user, is_following
def fetch_all_posts(self, request_user) -> QuerySet[Post]:
is_owner, liked_by_user, is_following = self.request_data(request_user)
return (
self.select_related()
.prefetch_related(
Prefetch(
"comments",
queryset=Comment.objects.select_related().filter(reply=False),
), # filter related "comments" inside the post QuerySet
)
.order_by("-publication_date")
.annotate(is_following=is_following)
.annotate(is_owner=is_owner)
.annotate(likes=Count("liked_by"))
.annotate(liked_by_user=liked_by_user)
)
def fetch_following_posts(self, request_user: User) -> QuerySet[Post]:
return self.fetch_all_posts(request_user).filter(user__in=request_user.following.all())
class User(AbstractUser):
id: int
posts: RelatedManager[Post]
liked_posts: RelatedManager[Post]
comments: RelatedManager[Comment]
about = models.CharField(blank=True, max_length=255)
photo = models.ImageField(
blank=True,
upload_to=upload_path,
validators=[file_validator],
)
following = models.ManyToManyField("self", related_name="followers", symmetrical=False)
objects: CustomUserManager = CustomUserManager()
class Post(models.Model):
id: int
user_id: int
comments: RelatedManager[Comment]
user = models.ForeignKey(
settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="posts"
)
text = models.CharField(max_length=200)
publication_date = models.DateTimeField(auto_now_add=True)
edited = models.BooleanField(default=False)
last_modified = models.DateTimeField(auto_now_add=True)
liked_by = models.ManyToManyField(
settings.AUTH_USER_MODEL, related_name="liked_posts", blank=True
)
objects: PostManager = PostManager()
The query raising the exception:
posts = Post.objects.fetch_all_posts(request_user=request.user)
Traceback:
File "/home/fernando/Workspaces/CS50 Web/project4/network/network/api_views.py", line 198, in get_all_posts
if posts is None or posts.exists() is False:
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/models/query.py", line 1225, in exists
return self.query.has_results(using=self.db)
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 592, in has_results
return compiler.has_results()
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1363, in has_results
return bool(self.execute_sql(SINGLE))
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1395, in execute_sql
cursor.execute(sql, params)
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute
return super().execute(sql, params)
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
with self.db.wrap_database_errors:
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
return self.cursor.execute(sql, params)
File "/home/fernando/Workspaces/CS50 Web/project4/network/.venv/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 357, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: sub-select returns 13 columns - expected 1