Forums

django.db.utils.OperationalError: (1242, 'Subquery returns more than 1 row')

Project: https://binnybit.pythonanywhere.com/

A 500 internal server error is being raised when requesting a user profile page with a given numeric id. To request such a page, the following path is as follows: users/(?P<id>\d+)/?$

When checking the error log for the cause, the following error is raised: MySQLdb.OperationalError: (1242, 'Subquery returns more than 1 row'). I can't tell what is causing the error given the traceback doesn't pinpoint the cause whether it be in the Views, Manager methods, or Templates that I constructed. Rather it's all built-in Django in the traceback.

<br>The strange thing about this is that some user profile pages are returned without the error being raised while others raise the aforementioned error. All users are equal in this project in the sense that no groups/permissions are ever used.

<br>I've tried commenting out different sections of the code to eliminate possible causes but have no success in doing so.

<br> The given path and view is: urls.py

- users/(?P<id>\d+)/?$

views.py

class UserProfilePage(Page, SingleObjectMixin):

template_name = "authors/profile.html"
model = get_user_model()
pk_url_kwarg = "id"

def get_context_data(self, **kwargs):
    try:
        self.object = self.get_object()
    except ValueError:
        raise Http404()
    context = super().get_context_data(object=self.object)
    context |= {
        'page_options': [
            'Summary', 'Questions', 'Answers', 'Tags', 'Bookmarks'
        ]
    }
    return context

def get(self, request, id):
    context = self.get_context_data()
    query_page_filter = request.GET.get("tab", "summary").lower()
    post_queries = ['bookmarks', 'questions', 'answers', 'tags']
    profile_url_path = reverse('authors:profile', kwargs={'id': self.object.id})
    if query_page_filter not in post_queries:
            context |= context['object'].profile.collect_profile_data()
            context |= {
                'query_page_filter': "summary",
                'form': ProfileSearchQueryForm,
                'url': {
                    f"profile_{post}": f"{profile_url_path}?tab={post}&sort=newest"
                    for post in post_queries
                }
            }
    else:
        order_by = request.GET.get("sort")
        if query_page_filter == "tags":
            query_buttons = ['name', 'score']
            query = context['object'].profile.get_tag_posts
        elif query_page_filter == "bookmarks":
            query_buttons = ['newest', 'score', 'added']
            query = context['object'].profile.get_bookmarked_posts
        elif query_page_filter == "questions":
            query_buttons = ['newest', 'score', 'views']
            query = context['object'].profile.get_question_posts
        else:
            query_buttons = ['newest', 'score']
            query = context['object'].profile.get_answer_posts
        if not order_by or order_by not in query_buttons:
            order_by = query_buttons[0]
        paginator = Paginator(query(order_by)['records'], 10)
        page = paginator.get_page(request.GET.get('page', 1))
        query_string = QueryDict(
            f"tab={query_page_filter}&page={page.number}&sort={order_by}"
        )
        context |= {
            'page': page,
            'page_query_filter': query_page_filter,
            'requested_url': f"{request.path}?{query_string.urlencode()}",
            'form': ProfileSearchQueryForm,
            'query_buttons': query_buttons
        }
    return self.render_to_response(context)

models.py

class UserQueryManager(Manager):

def get_queryset(self):
    return super().get_queryset().annotate(
        post_count=Count("profile__question")
    )

def by_name(self, name):
    if not name:
        return self.get_queryset()
    return self.get_queryset().filter(
        username__icontains=name
    )

class User(AbstractUser):

username = CharField(
    unique=True, max_length=16,
    error_messages={
        "unique": "Username not available"
    }
)

objects = UserManager()
posted = UserQueryManager()


class Profile(Model):
user = OneToOneField(settings.AUTH_USER_MODEL, on_delete=CASCADE)

def get_tag_posts(self, order_by=None):
    tags = Tag.objects.filter(
        question__profile=self
    ).distinct()
    questions_with_tag = self.questions.filter(
        tags__name=OuterRef("name")).only('id')
    records = tags.annotate(
        times_posted=Count(Subquery(questions_with_tag)),
        avg_question_score=Avg("question__score", output_field=IntegerField())
    )
    if not order_by or order_by not in ['name', 'score']:
        order_by = "name"
    if order_by == "name":
        records = records.order_by(order_by)
    else:
        records = records.order_by("-avg_question_score")
    return {
        'records': records,
        'title': f"{tags.count()} Tags"
    }

def get_question_posts(self, order_by=None):
    if not order_by or order_by == "newest":
        order_by = "date"
    questions = self.questions.all().order_by(f"-{order_by}")
    return {
        'records': questions,
        'title': f"{questions.count()} Questions"
    }

def get_answer_posts(self, order_by=None):
    if not order_by or order_by == "newest":
        order_by = "-date"
    answers = self.answers.all().order_by(order_by).annotate(
        in_response_to=Concat(Value("Question:"), "question__title")
    )
    return {
        "records": answers,
        'title': f"{self.answers.count()} Answers"
    }

def get_posts_voted_on(self, sort=None):
    votes = Vote.objects.filter(profile=self)
    return {
        'like': votes.filter(type="like").count(),
        'dislike': votes.filter(type="dislike").count(),
        'questions': Question.objects.filter(vote__profile=self).count(),
        'answers': Answer.objects.filter(vote__profile=self).count()
    }

def get_bookmarked_posts(self, sort=None):
    bookmarks = Bookmark.objects.filter(profile=self)
    if not sort:
        pass
    else:
        if sort == "score":
            bookmarks = bookmarks.order_by("-question__score")
        elif sort == "added":
            bookmarks = bookmarks.order_by("saved")
        else:
            bookmarks = bookmarks.order_by("-question__date")
    return {
        'records': bookmarks,
        'title': f"{bookmarks.count()} bookmarks"
    }

def collect_profile_data(self):
    profile_posts = {
        "question": self.get_question_posts(),
        "answer": self.get_answer_posts(),
        "tag": self.get_tag_posts(),
        "bookmark": self.get_bookmarked_posts(),
    }
    for post_type, data in profile_posts.items():
        _queryset = data['records'][:5]
        profile_posts[post_type]['records'] = _queryset
    profile_posts.update({'votes': self.get_posts_voted_on()})
    return profile_posts

We can only generally help with PythonAnywhere-specific issues here on these forums; it's possible that other PythonAnywhere users might be able to help you, but you'll probably have more luck posting your question on a general programming help site like Stack Overflow.