25. Joins, filters and chaining commands

We’ve now built a page that shows the data present in the gene table. But we haven’t looked about data that arrives via foreign key.

These are setup as part of each query record and accessed via the appropriate variable as a linked query set, but we can then also iterate over.

Let’s jump back to our code and visit our gene.html template. We need to iterate over the foreign keys to extract the information we require.

        {% with seq=gene.sequencing %}
            <tr><td>Sequencing Factory:</td>
            <td>{{seq.sequencing_factory}}</td></tr>
            <tr><td>Factory Location: </td>
            <td>{{seq.factory_location}}</td></tr>
        {% endwith %}

        {% with ec=gene.ec %}
            <tr><td>EC Name: </td><td>{{ec.ec_name}}</td></tr>
        {% endwith %}

        {% with links=gene.geneattributelink_set.all %}
            {% for link in links %}
                <tr><td>{{link.attribute.key}}</td><td>{{link.attribute.value}}</td></tr>
            {% endfor %}
        {% endwith %}

We can also filter our data. Let’s say we only want to view records which only have the chromosome entity in the gene table.

Let’s go back to the index.html page and add a new section:

Select gene location

Chromosome OR Plasmid

As we have created a link to a URL with /list/ on it, we now need to add a new route to the urls.py file. So navigate to that file and update urlpatterns:

urlpatterns = [
    path('', views.index, name='index'),
    path('gene/', views.gene, name='gene'),
    path('list/', views.list, name='list'),
]

This time list is capturing a string (with ).

We will also need to add a list function to views.py:

def list(request, type):
    genes = Gene.objects.filter(entity__exact=type)
    return render(request, 'genedata/list.html', {'genes': genes, 'type': type})

__exact is an inbuilt function which looks for an exact match to a field, in this case entity.

So we’re sending genes and type to a new template called list.html, which we need to create.

<html>
    <head>
    </head>
    <body>
        <h1>Filtered List: {{type}}</h1>
        <table>
        <tr><th>Gene ID</th></tr>

        {% for gene in genes %}
            <tr><td><a href="/gene/{{gene.pk}}">{{gene}}</a></td></tr>
        {% endfor %}

        </table>
    </body>
</html>

Gene.objects.filter(entity__exact=type) is a filter (like a WHERE in SQL) and we can chain them together to make more sophisticated database queries.

Let’s say we want a list where the gene is chromosome and sense is positive.

Add a new link to index.html:

<h2>Show Positive Chromosome</h2> <a href="/poslist/">Show This List</a>

Add a new path in urlpatterns in urls.py:

path('poslist/', views.poslist, name='poslist'),

Add a new function in views.py:

def poslist(request):
    genes = Gene.objects.filter(entity__exact='Chromosome').filter(sense__startswith='+')
    return render(request, 'genedata/list.html', {'genes': genes, 'type':'PosList'})

You can see we have used .filter twice in the statement assigning the query set to genes here.

Further reading on how to use the Query Set API: https://docs.djangoproject.com/en/3.0/ref/models/querysets/

Wednesday 3 November 2021, 611 views

Next post: 26. Deleting and updating records
Previous post: 24. Using the ORM in views.py