CODEWITHSUNDEEP
pythonsqldjangopostgresql
neeraj lamsal
neeraj lamsal

Reputation: 57

django.core.exceptions.FieldDoesNotExist: Raw query must include the primary key

I am trying to do a raw query in django and it does not seems to be working.

It is working when I execute the same query in table plus (IDE for databases) .

This is my Django code:

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)

This is the Traceback that i got:

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

This is the query that i am executing in table 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;

Note:

h_te is home_transactionentries table

h_t is home_transaction table

u_c is user_customer table

home_transactionentries table

home_transaction table

user_customer table

Upvotes: 2

Views: 681

Answers (1)

AKX
AKX

Reputation: 168843

If you want to execute a raw query that doesn't map to returning model instances (which is what the exception is telling you), you'll need to use 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

Note the use of the %s placeholder to avoid SQL injection vulnerabilities.

As an aside, it looks like you could simplify the computation of the Total column to

sum(abs(h_te.to_pay_to_customer - h_te.pay_from_customer)) as Total

Upvotes: 1

Related Questions