how to do inner join using 3 tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

how to do inner join using 3 tables

Angel Omar Rojas Pacheco
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.






















--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2ea9259d-19de-4c10-be8d-34df2b0aa66c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: how to do inner join using 3 tables

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)



On Thursday, March 8, 2018 at 9:52:44 AM UTC-5, Angel Rojas wrote:
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=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">user.id, current_status=0)
# Transactiondetail.objects.filter(transaction_id=<a href="http://transaction.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;">transaction.id, status=0)
return Transaction.objects.filter(user_id=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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(<a href="http://current_user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;">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=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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.






















--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7d8b7d7b-5dfc-4ba6-816a-294264f2020b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: how to do inner join using 3 tables

Angel Omar Rojas Pacheco
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.

 

On Thursday, March 8, 2018 at 1:25:00 PM UTC-2, C. Kirby wrote:
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)



On Thursday, March 8, 2018 at 9:52:44 AM UTC-5, Angel Rojas wrote:
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=<a href="http://user.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">user.id, current_status=0)
# Transactiondetail.objects.filter(transaction_id=<a href="http://transaction.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;">transaction.id, status=0)
return Transaction.objects.filter(user_id=<a href="http://user.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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(<a href="http://current_user.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;">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=<a href="http://user.id" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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.






















--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e8b0c942-178d-4dd0-95eb-a9a797663851%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: how to do inner join using 3 tables

charettes
In reply to this post by Angel Omar Rojas Pacheco
Hello Angel,

Did you try the following

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

Best,
Simon

Le jeudi 8 mars 2018 09:52:44 UTC-5, Angel Rojas a écrit :
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=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">user.id, current_status=0)
# Transactiondetail.objects.filter(transaction_id=<a href="http://transaction.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Ftransaction.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFxptBGPGgdAHM91R56GRvbSx13Pw&#39;;return true;">transaction.id, status=0)
return Transaction.objects.filter(user_id=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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(<a href="http://current_user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fcurrent_user.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHln86yVm6ftJ3tJurAoqyAu6JNQA&#39;;return true;">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=<a href="http://user.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fuser.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGa7JWuFksxAdNITxOmvA9YZ0FCUQ&#39;;return true;">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.






















--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5b5505c0-5e15-4c35-b141-3a566cda6726%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.