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

So long and thanks for all the fish! Part 1 of 2

I have been with the Tech Academy both as a software developer bootcamp student, as well as an employee. After my bootcamp, I was hired first as the live project instructor, and then as Live Project Director. This, I believe, gives me a unique point of view. I have absolutely no regrets and would join the bootcamp again. But there are a number of things I would do differently. What I have learnt as a former student 1. DO NOT WORK PART TIME.   I worked part-time(20-30hrs) during my bootcamp. I was up at 2.30-3.00am every day to work for several hours. I took a short nap, and then I took a 1hr bus ride down to campus. Studied for 7- 9 hours. Took a 1hr bus ride back home. Lather, rinse, repeat. I also had some family obligations. My weekends and half the summer were taken up caring for my young stepdaughter. I was completely exhausted by the end of the bootcamp and I didn't know if I could do more. Learning to program is HARD. You need to be fully focused. I am fortunate because I di...

Inserting data from CSV into Postgres table with Python

I've been looking into how to insert data from a CSV file into a Postgres Table with code. Turns out it's pretty simple and straightforward. I personally prefer doing it in code than with a command, but I'm not sure what is more common in the industry.  So now I should be able to load data from a CSV file into a Postgres table to create a REST API. The next step is trying to figure out how to convert that data into JSON format. 🤔

Figuring out Postgres Part 1(Setting it up)

 I've been meaning how to use Postgres for a while now and I've finally decided to dive into it. First step, installing Postgres from their website . I kept all the default settings which meant it installed PostgreSQL Server, pgAdmin4, Stack Builder, and Command Line Tools. It later prompted me to set up Stack Builder, but I took a look at this tutorial  and determined that I don't really need to do that right now. It also helped me figure out how to verify the installation using SQL Shell(psql). Everything looks good so far. I followed another tutorial on Linkedin learning to create a database. Next on the tutorial, create a virtual environment and install Psycopg2-binary in it. Apparently it's a Postgres database adapter.  And because I'm an idiot, I forgot where I saved the database. I opened up Postgres shell and used the command SHOW data_directory; But it turns out I didn't need it anyway 😁 I created a new Python file and added the following lines of cod...