how to do inner join using 3 tables

[Angel Rojas]

Hello friends, I have a little problem with a relationship with 3 tables

tables

- User

- Transaction

- TransactionDetail

my query is

class TransactionInitiated(generics.ListAPIView):
    """
        List Transaction Initiated
    """
    serializer_class = TransactionSerializer

    def get_object(self, pk):
        user = User.objects.get(id=pk)
        transaction = Transaction.objects.filter(user_id=user.id, current_status=0)
        # Transactiondetail.objects.filter(transaction_id=transaction.id, status=0)
        return Transaction.objects.filter(user_id=user.id, current_status=0).order_by('-id')

    def get(self, request, pk, format=None):
        current_user = request.user
        list_trans_init = self.get_object(current_user.id)
        serializer = TransactionSerializer(list_trans_init, many=True)
        return JsonResponse({'data': serializer.data}, safe=False)

with this query I recovery all user with a respective transaction, but I don't how to use inner join in here, I try next

def get_object(self, pk):
    user = User.objects.get(id=pk)
    transaction = Transaction.objects.filter(user_id=user.id, current_status=0)
    return Transaction_Detail.objects.filter(transaction_id=transaction, status=0)

in here return me the next error

more than one row returned by a subquery used as an expression.


please some help me.

thanks for your attention.

[C. Kirby]

As written it looks like you want to get all the 0 status transactions for a given user, and then get all the transaction details for those transactions (.filter allows for any number of results) Since your var transaction is a list, and you really only care about the ids to pull the transaction details, you should have your code like this:

def get_object(self, pk):
    user = User.objects.get(id=pk)

    transactions = Transaction.objects.filter(user_id=user.id, current_status=0).values_list('id', flat=True)
    return Transaction_Detail.objects.filter(transaction_id__in=transactions, status=0)

[Angel Rojas]

thank you for your answer, but I need a next result

select * select User as u inner join transaction as tr on u.id=user_id.id inner join transactionDetail as trd on tr.id=trd.transaction_id

where tr.current_status=trd.status

please how can I do that

thanks for your attention.

[Simon Charette]

Hello Angel,

Did you try the following

transaction = Transaction.objects.filter(
    user=user,
    status=F('detail__status'),
).first()

Best,
Simon