I wanted to get your advice.

I have the below, which returns the data I want.

However, so that I can use ajax to update the page without refresh, I need to be able to return the results as JSON. I keep getting erros that the query result is not serializable.

Does anyone know how to do it?

Thanks a lot!

open_tasks = skills.objects.filter(creator=request.user).raw('''
            SELECT *,
            round((closed_points/(open_points   closed_points)*100),2) as points_pct,
            round((closed_count/(open_count   closed_count)*100),2) as closed_pct
            from (
            SELECT id, coalesce(sum(open_points),0) as open_points, coalesce(sum(closed_points),0) as closed_points,
            coalesce(sum(open_count),0) as open_count, coalesce(sum(closed_count),0) as closed_count
            from (
            SELECT id,
            case when status = 'open' then sum(points) end as open_points,
            case when status <> 'open' then sum(points) end as closed_points,
            case when status = 'open' then sum(count) end as open_count,
            case when status <> 'open' then sum(count) end as closed_count
            from (
            SELECT category as id, status, sum(cast(points as int)) as points, count(*) as count
            FROM voxi_skills
            WHERE creator = %s
            group by category, status)s
            group by id, status)p
            group by id)j
            ''', [request.user.username])

CodePudding user response：

One way to do this would be to create a serialize() method on the object that you wish to serialize to a JSON string. You can then use Django's built-in JsonResponse object to return the data as a JSON string.

For example, if I wanted to return a Book object as JSON, I could write the code for the Book class as follows:

from django.db import models
import uuid

class Book(models.Model):
    id = models.UUIDField(
        primary_key=True, 
        default=uuid.uuid4, 
        editable=False
    )

    title = models.CharField(
        max_length=250,
        null=False,
        blank=False
    )

    author = models.CharField(
        max_length=250,
        null=False,
        blank=False
    )

    def serialize(self):
        return {
            "id": self.id,
            "title": self.title,
            "author": self.author
        }

Then, within my view, I could return the Book object as JSON using the following code:

from django.http import JsonResponse

def books(request):
    if request.method == "GET": 
        books = Book.objects.all()
        return JsonResponse([book.serialize() for book in books], safe=False)

The above view should return my Book objects as JSON data, similar to the below:

[
    {
        "id": <some_guid>,
        "title": <some_title>,
        "author": <some_author>
    },
    {
        "id": <some_guid>,
        "title": <some_title>,
        "author": <some_author>
    }
]

More info:

• Django JsonResponse object documentation: https://docs.djangoproject.com/en/3.2/ref/request-response/#jsonresponse-objects