pythonsqldjangodjango-viewsets

How to fetch rows from a table based on a field which is present in a different table having a foreign key relationship with it?


I have two tables as follows in Django:

Table1: 
    - id
    - name
    - address
    - state
    - short_code
    
Table2: 
    - id
    - table1_id
    - p1, property (searchable field) 

Relationship between Table1 and Table2: Table1(1) -> Table2(n) [ 1->n ]

Let's say I have a searchable property p1 in Table2. How to fetch all the rows from Table1 which is satisfying the following query parameters?

short_code(table1 field), state(table1 field), and property as p1(table2 field)

Remember, Table1 has 1:n relationship with Table2, so Table2 can have multiple rows satisfying foreign key relationship of field id from Table1.


Solution

  • You can do related lookups:

    class MyModel1(models.Model):
       model2 = models.ForeignKey('myapp.MyModel2', related_name='model1',...)
    
    class MyModel2(models.Model):
       fields...
    

    Now you can do:

    MyModel1.objects.filter(model2__pk=YOUR_MODEL2_PK)
    

    Or

    MyModel2.objects.filter(model1__pk=YOUR_MODEL_1_PK)
    

    You can limit the results returned with .only()

    Note:

    Have a look at the docs for many to one relationships

    Or the docs for querysets