Skip to main content

Querying tables linked via foreign keys

A student was trying to display data from both her parent and child tables that were linked by foreign key. I played around with it and found 2 ways I can do this. 

The models(I took out most of the fields for this post because it was unnecessary for this explanation):
class Owner(models.Model):
owner_fname = models.CharField('First Name', max_length=50, blank=False, null=False)

Owner=models.Manager()


class PlayTime(models.Model):
dog_name = models.CharField(max_length=50, blank=False)
owner = models.ForeignKey(Owner, on_delete=models.CASCADE, blank=False, null=False)

PlayTime=models.Manager()

This first method involves multiple queries to the database. The second method involves a single query, and fewer lines of code. It is the equivalent of an inner join. I didn't include the context and the return statement, which are necessary, of course, if you plan to pass these variables on to the template.
def details(request, pk):
#============== METHOD 1: query database multiple times ===============#
get_posts = get_object_or_404(PlayTime, pk=pk)
print(get_posts.owner_id)
lookup_id = get_posts.owner_id #get foreign key
get_owner= get_object_or_404(Owner, pk= lookup_id) #pass foreign key to second db query
print(get_owner.owner_fname)

# ============== METHOD 2: inner join =================#
details = PlayTime.PlayTime.select_related('owner').get(id=pk)
print(details.owner.owner_fname)
# print(details.values())
 If you wanted to join all the data in both tables, you can also do so and get a queryset. In that case, you would just type: 
details = PlayTime.PlayTime.select_related('owner')

Disclaimer: I'm not sure if this is the best way to do this, but this is what I figured out from looking at documentation online. 

Comments

Popular posts from this blog

Using Modals to render forms

A student wanted to create several tables with foreign keys linking them. He was placing the forms all over the place and that just didn't seem to make sense to me. I suggested that he link all the forms to one main registration page, and while he worked on it, I came up with my own solution that I feel rather proud of. Though I'm well aware it is a very simple solution.  views.py def register (request): # form for team formteam = TeamForm(request.POST or None ) if formteam.is_valid(): formteam.save() return redirect( 'registerForTeam' ) else : print (formteam.errors) formteam = TeamForm() # form for coach formcoaches = CoachesForm(request.POST or None ) if formcoaches.is_valid(): formcoaches.save() return redirect( 'registerForTeam' ) else : print (formcoaches.errors) formcoaches = CoachesForm() # form for player formplayer = PlayersForm(request.POST or None ...

Shortcuts

 I saw one of my colleagues do something magical. He automatically fixed the alignment of student code on Visual Studio with just 1 little keyboard shortcut. Ctrl-k, followed by D. Holding down Ctrl the entire time Sometimes shortcuts really do work and make your life better!   

Finding your roots

I tried working on the Algorithms I course on Coursera a while back and I had no idea what was going on so I never continued with it. I decided to give it another try now that I've read up a little on algorithms. It's still using a lot of my brain cells but I am slowly making my way through it.  Learning about Quick-unions in Java(from the course) Java seems almost identical to C#. I've forgotten most of what I've learnt about C# but, there's enough in this brain for me to read this. I had such a tough time understanding the root method. I wrote it out in my notebook and worked through it to figure out how it functions and I am amazed! That line is so simple yet complex. And it reminded me of a binary tree LeetCode challenge I was trying to work on with my colleagues some time ago. I had no idea what binary trees even were at that point. The challenge involved finding roots and tre...