Django.core.exceptions.FieldDoesNotExist: Необработанный запрос должен включать первичный ключ

Я пытаюсь сделать необработанный запрос в django, но, похоже, он не работает.

Это работает, когда я выполняю тот же запрос в table plus (IDE для баз данных) .

Это мой код Django:

class AllCustomerOwnerTransactionView(APIView):
    permission_classes = [IsAuthenticated]
    authentication_classes = [JWTAuthentication]

    def get(self, request):
        query = f"""
SELECT  u_c.name,
       sum(CASE
         WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
          h_te.to_pay_to_customer - h_te.pay_from_customer
         WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
         h_te.pay_from_customer - h_te.to_pay_to_customer
         ELSE 0
       end) as Total,
       u_c.owner_id
FROM home_transactionentries h_te
       INNER JOIN home_transaction h_t
               ON h_te.transaction_id = h_t.id
       INNER JOIN users_customer u_c
               ON u_c.id = h_t.customer_id
WHERE u_c.owner_id = {request.user.id}
GROUP BY u_c.name,
         u_c.owner_id;
        """
        query_set = TransactionEntries.objects.raw(query)

Это трассировка, которую я получил:

Traceback (most recent call last):
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/views/generic/base.py", line 70, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "/home/__neeraj__/Documents/programming/merokarobarClone/AccountsBackend/home/views.py", line 73, in get
    for db_data in query_set:
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1484, in __iter__
    self._fetch_all()
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1471, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/__neeraj__/.local/lib/python3.9/site-packages/django/db/models/query.py", line 1499, in iterator
    raise exceptions.FieldDoesNotExist(
django.core.exceptions.FieldDoesNotExist: Raw query must include the primary ke

Этот запрос я выполняю в таблице plus:

SELECT u_c.name,
       sum(CASE
         WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
          h_te.to_pay_to_customer - h_te.pay_from_customer
         WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
         h_te.pay_from_customer - h_te.to_pay_to_customer
         ELSE 0
       end) as Total,
       u_c.owner_id
FROM   home_transactionentries h_te
       INNER JOIN home_transaction h_t
               ON h_te.transaction_id = h_t.id
       INNER JOIN users_customer u_c
               ON u_c.id = h_t.customer_id
WHERE  u_c.owner_id = 1
GROUP  BY u_c.name,
          u_c.owner_id;

Примечание:

h_te is home_transactionentries table

h_t is home_transaction table

u_c is user_customer table

таблица home_transactionentries

таблица home_transaction

таблица user_customer

Если вы хотите выполнить необработанный запрос, не связанный с возвратом экземпляров модели (о чем и говорит исключение), вам нужно использовать cursor.execute():

with connection.cursor() as cursor:
    cursor.execute(
        f"""
SELECT
  u_c.name,
  sum(
    CASE
      WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN h_te.to_pay_to_customer - h_te.pay_from_customer
      WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN h_te.pay_from_customer - h_te.to_pay_to_customer
      ELSE 0
    end
  ) as Total,
  u_c.owner_id
FROM
  home_transactionentries h_te
  INNER JOIN home_transaction h_t ON h_te.transaction_id = h_t.id
  INNER JOIN users_customer u_c ON u_c.id = h_t.customer_id
WHERE
  u_c.owner_id = %s
GROUP BY
  u_c.name,
  u_c.owner_id;
""",
        [request.user.id],
    )
    data = cursor.fetchall()  # list of 3-tuples according to your columns

Обратите внимание на использование заполнителя %s во избежание уязвимостей SQL-инъекций.

В качестве дополнения, похоже, что вы можете упростить вычисление столбца Total до 

sum(abs(h_te.to_pay_to_customer - h_te.pay_from_customer)) as Total
Вернуться на верх