CODEWITHSUNDEEP
djangodjango-queryset
Jshee
Jshee

Reputation: 2686

Issue formulating django query between two tables

I've read at length about django queries.

But, despite my efforts to trying django forward queries and backwards queries I just cannot get what I want.

i wrote a forward query:

ATUFS =  TSFH.objects.filter(FKToUser=request.user).values('sB','sE')

this returns:

{'sB': datetime.datetime(2019, 5, 21, 18, 14, 2, 691185, tzinfo=<UTC>), 'sE': datetime.datetime(2019, 5, 21, 18, 16, 2, 532731, tzinfo=<UTC>)}

But for this query I need also make a query that ensures TSF.FKToT a FK to another table T matches the local variable in my script ed = request.GET.get('d', ''). So essentially with this clause Im trying to achieve T.dNm = ed . Im just confused how to achieve this in my django forward query.

i wrote a backward query: ATFUS = TSF.objects.filter(FKToTld__dNm='123.123.13.1').values('sB','sE ')

This returns an error, that sB, and sE are not available to return the values for, because they're not in the TSF table.

In regular SQL Im simply trying to achieve 

SELECT sB, sE 
FROM TSF, TSFH,T
where TSFH.id=TSF.FKToTSFH_id and T.id=tsf.FKToT_id;

How can I achieve what I am trying to do via the Django orm?

Thanks

UPDATED VIEW/TEMPLATE

Completed=TSF.objects.filter(FKToT__FKToUser=request.user,FKToTSFH__sE__isnull=False).values('FKToTSFH__sB','FKToTSFH__sE')
return render(request, 'view.html', {
'C':Completed

 })

{% if C %}
{% for res in C %}
<li><span>{{ res.FKToTSFH.sB }}</span></li>
{% endfor %}

This gives an issue of blank output.

Any thoughts on how to resolve.

Upvotes: 0

Views: 23

Answers (1)

ruddra
ruddra

Reputation: 51988

You can try like this(as sB abd sE are in TSFH model, so you need to put FKToTSFH__ before sB and sE like following):

TSF.objects.filter(FKToTld__dNm='123.123.13.1').values('FKToTSFH__sB','FKToTSFH__sE')

# with user

TSF.objects.filter(FKToTld__FKToUser=request.user).values('FKToTSFH__sB','FKToTSFH__sE')  # make sure user is authenticated

Bit more complicated way of acheiving this result is to annotate the value of sE and sB to the queryset, like this:

from django.db.models import F

TSF.objects.filter(FKToTld__dNm='123.123.13.1').annotate(sB=F('TSFH__sB'), sE=F('FKToTSFH__sE')).values('sB','sE')

Usage

for item in TSF.objects.filter(FKToTld__dNm='123.123.13.1'):
     print(item.FKToTSFH.sB)
     print(item.FKToTSFH.sE)

for item in TSF.objects.filter(FKToTld__dNm='123.123.13.1').values('FKToTSFH__sB', 'FKToTSFH__sE'):
  print(item.get('FKToTSFH__sB'))
  print(item.get('FKToTSFH__sE'))

for (sB, sE) in TSF.objects.filter(FKToTld__dNm='123.123.13.1').values_list('FKToTSFH__sB', 'FKToTSFH__sE'):
  print(sB)
  print(sE)

Update 2:

You can use isnull for checking null value exists or not:

# view
queryset = TSF.objects.filter(FKToTld__dNm='123.123.13.1', FKToTSFH__sB__isnull=False)
return ('template.html', context={'tsf': queryset})

# template

{% for i in tsf %}
    {{ i.FKToTSFH.sB }}
{% endfor %}

Upvotes: 1

Related Questions