[Beginner] How can I filter my MySQL query

pierrot10 · July 12, 2022, 9:43pm

Hi,
I am discovery Django and after following the tuto from the (Django web site → Documentation → First Steps → Turorials) , I am trying to build my app.

I have a database with about 1 million of measures but my goal is not to show all yet.
My database has about 10 tables and I am using a lot of JOIN. I developped a PHP/MySQL script which works fine but I would like to migrate my work with Django/Python.

My project has some Fields. Ia field, there are actived and inactived stations and each station has sensors.

I crated a view here
(Sadly, as I am a new user, I can only add 2 links)
and the template fields.html print all the field.

duvernay (46.183534,6.004783) (ocalhost:8080/console/5/stations)

Perrières (46.217744,6.013987) (localhost:8080/console/4/stations)

boigies (46.184610,6.008253) (localhost:8080/console/3/stations)

hutins (46.186090,5.997505) (localhost:8080/console/2/stations)

printaniere (46.173291,6.003578) (localhost:8080/console/1/stations)

Here is the model

When I click on the first field, the view stations, show me ALL stations

class StationsView(generic.ListView):
    #model = Stations
    context_object_name = 'stations_list'
    template_name = 'console/stations.html'

    def get_queryset(self):
        """Return the last five published station."""
        return Stations.objects.order_by('-station_created')[:30]

Here is the stations model

class Stations(models.Model):
    id_station = models.AutoField(primary_key=True)
    fields_id_field = models.ForeignKey(Fields, models.DO_NOTHING, db_column='fields_id_field')
    stations_types_id_stations_type = models.IntegerField()
    station_name = models.CharField(max_length=20)
    station_longname = models.CharField(max_length=45, blank=True, null=True)
    station_active = models.IntegerField()
    station_archive = models.IntegerField()
    station_lat = models.FloatField(blank=True, null=True)
    station_lng = models.FloatField(blank=True, null=True)
    station_alt = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    installed = models.DateTimeField()
    station_description = models.TextField(blank=True, null=True)
    ttn_app_id = models.CharField(max_length=20)
    ttn_dev_id = models.CharField(max_length=20)
    ttn_hardware_serial = models.CharField(max_length=20)
    station_created = models.DateTimeField()
    station_order = models.IntegerField()
    map = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'stations'

I would like to know, how can I filter the station for the selected field, and how can I hidden the inactivate station.

Other question will come, but it would be nice, I understand how can I first filter my queries

Many thanks

KenWhitesell · July 12, 2022, 10:17pm

If you are working through the tutorial, then you would have performed the exercises in the section Playing with the API. In those examples, you have seen how to use the filter method to limit what is retrieved by your query.

Also see the docs for filter, queryset methods, and field lookups.

pierrot10 · July 13, 2022, 9:09pm

Hello,

It’s not really clear, at least I cna not do it. I remove the generic view and I change my urls.py

First I changed this

path('<int:fields_id_field>/stations', views.stations, name = 'stations'),

as my link is as the following:
localhost:8080/console/5/stations
5 is the id of the field: fields_id_field

The I try to create my view a s the following

But I have an error here

Is there a way to debug my script as with console.log() with javascript?
That would help to see what there in stations_list

stations_list = get_object_or_404(Stations, pk=fields_id_field)

or in fields_id_field. How can make sure that fields_id_field has the value of 5

Thanks a lot

KenWhitesell · July 13, 2022, 9:34pm

What error are you getting? What do the messages say in the console where you run runserver?

pierrot10 · July 13, 2022, 9:51pm

Hello
I have this error

TypeError: ‘Stations’ object is not iterable

then I simply tried to change
stations_list = get_object_or_404(Stations, pk=fields_id_field)
to
stations_list = get_object_or_404(stations, pk=fields_id_field)

Now I have a new error

ValueError: First argument to get_object_or_404() must be a Model, Manager, or QuerySet, not ‘function’.

Then I look at models.py
and I found this


class Stations(models.Model):
    id_station = models.AutoField(primary_key=True)
    fields_id_field = models.ForeignKey(Fields, models.DO_NOTHING, db_column='fields_id_field')
    stations_types_id_stations_type = models.IntegerField()
    station_name = models.CharField(max_length=20)
    station_longname = models.CharField(max_length=45, blank=True, null=True)
    station_active = models.IntegerField()
    station_archive = models.IntegerField()
    station_lat = models.FloatField(blank=True, null=True)
    station_lng = models.FloatField(blank=True, null=True)
    station_alt = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    installed = models.DateTimeField()
    station_description = models.TextField(blank=True, null=True)
    ttn_app_id = models.CharField(max_length=20)
    ttn_dev_id = models.CharField(max_length=20)
    ttn_hardware_serial = models.CharField(max_length=20)
    station_created = models.DateTimeField()
    station_order = models.IntegerField()
    map = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'stations'

But now I wonder if (station) should remind with a S capital

stations_list = get_object_or_404(Stations, pk=fields_id_field)

KenWhitesell · July 13, 2022, 10:00pm

You have:

First, review the documentation of what get_object_or_404 does.

It calls get, which returns a single object, in this case an instance of type Station.

It does not return a list!

The rest of your code is expecting that stations_list is a list.

As a result, you get this error message:

(Note: The complete traceback would have provided more information. You should get used to reading them. They’re frequently quite valuable.)

pierrot10 · July 13, 2022, 10:15pm

Meanwhile, I removed get_object_or_404 with the goal to make it simpler and to get a result

def stations(request, fields_id_field):
    #template = loader.get_template('console/stations.html')
    #try:
    #stations_list = get_object_or_404(Stations, pk=field_id_field)
    stations_list = Stations.objects.get(fields_id_field=fields_id_field)

    #except(KeyError, Stations.DoesNotExist):
    #    return render(request, 'console/stations.html', {'stations_list': '1'})
    #response = "You're looking at the results of question %s."
    #return HttpResponse(response % fields_id_field)

    #try:
    return render(request, 'console/stations.html', {'stations_list':stations_list})
    #except(KeyError, Stations.DoesNotExist):
    #    return render(request, 'console/stations.html', {'stations_list':'1'})

this is interesting because I got this error

raise self.model.MultipleObjectsReturned(
console.models.Stations.MultipleObjectsReturned: get() returned more than one Stations – it returned 2!

It’s interresting because I should have serveral rows of stations. In my thought, I am trying to compare the value of fields_id_filed (5) in the colum ‘fields_id_field’ of my table ‘Stations’

pierrot10 · July 13, 2022, 10:23pm

Ouh, I think I found

stations_list = Stations.objects.filter(fields_id_field=fields_id_field)

Now I have the appropriate stations for a field
But I will come with a closer question

pierrot10 · July 13, 2022, 10:29pm

As a result I have this

st-2 (ST Bourgeon 2; ID Field:Fields object (1))

st-4 (ST 4; ID Field:Fields object (1))

st-6 (ST 6; ID Field:Fields object (1))

st-8 (ST 8; ID Field:Fields object (1))

st-14 (Zone M2; ID Field:Fields object (1))

st-15 (Station 15; ID Field:Fields object (1))

st-17 (Station 17; ID Field:Fields object (1))

st-18 (ST 18; ID Field:Fields object (1))

st-22 (Météo 22; ID Field:Fields object (1))

I happy with this (for now) but for today, I would like to solve this part ‘Fields object (1)’

Here is the part of y template

<h1>Station for fieldid</h1>
  {% if stations_list %}
      <ul>
      {% for station in stations_list %}
          <li>{{ station.station_name}} ({{ station.station_longname }}; ID Field:{{ station.fields_id_field }})</li>
          <!--<li><a href="{% url 'console:station' station.id_station %}">{{ station.station_name}} ({{ station.station_longname }}; ID Field:{{ station.fields_id_field }})</a></li>-->
      {% endfor %}
      </ul>
  {% else %}
      <p>No stations are available.</p>
  {% endif %}

Instead of ‘Fields object (1)’ I would like to have the field name, which is stored in the Fields table. Then I suppose, I should do a join, isn’t, but how?

Is there a way to print the content of the Fields object (1)? as I can do in JavaScript (console.log() )?

Thanks a lot

KenWhitesell · July 14, 2022, 12:03am

First, since you’ve asked twice about the JavaScript console, I’ll address that first.
It does nothing for you here. All this work is occurring on the server, none of it is done in the browser.
The view is generating the HTML and sending it to the browser. The information in that HTML is all that is available to the browser.

The join is done implicitly by Django, you don’t need to do anything to have that information available to you.

Take a step back for a moment and think about this in Python terms.

If you have a reference to an object, you access an attribute of that object using the “dot” notation.

So if you have a variable named station, which is a reference to an object of type Stations, and you want to access the station_active attribute, you would access this as station.station_active.

Now, if you have a variable named field, which is a reference to an object of type Fields, and you want to access that Fields attribute named name, you would reference it as field.name.

Take the next step and combine these two. If station is a reference to a Stations, then stations.fields_id_field is a reference to the Fields object.

How do you think you might then reference the name field of that Fields object?

(Side note: standard database design identifies that table names should use singular names, not plural. However, for a variety of reasons, having a model named Field would not be a good idea.)

pierrot10 · July 14, 2022, 8:50pm

Ok I understand, but how can I debug?
In my template this ‘ID Field:{{ station.fields_id_field }})’ print ID Field:Fields object (5)) . How can I debug ‘Fields object(5)’? How can I print the object to know how I can target a value.

I tried


          <ul>
            {% for field in station.fields_id_field %}
              <li>field.field_name</li>
            {% endfor %}
            </ul>
<ul>

TypeError: ‘Fields’ object is not iterable

<ul>
{% for field in station.fields %}
              <li>field.field_name</li>
            {% endfor %}
            </ul>

Take the next step and combine these two. If station is a reference to a Stations, then stations.fields_id_field is a reference to the Fields object.

then I tried

<ul>
            {% for field in Fields %}
              <li>field.field_name</li>
            {% endfor %}
            </ul>

Without success, sadly.

If ‘ID Field:{{ station.fields_id_field }})’ prints ID Field:Fields object (5)), I should be able to loop Fields, but I do not understand how, sorry.

Now, if you have a variable named field, which is a reference to an object of type Fields, and you want to access that Fields attribute named name, you would reference it as field.name.

It’s what I did above with field.field_name. My fields table has a colomns: field_name, id_field, etc.

So if you have a variable named station, which is a reference to an object of type Stations, and you want to access the station_active attribute, you would access this as station.station_active.
It’s very clear for my from the template. Additionally, in my view, I added this

stations_list = Stations.objects.filter(fields_id_field=fields_id_field, station_active=1)

and the inactive station are now not displayed, nice!

But I am still confused about Fields…

Thanks

KenWhitesell · July 14, 2022, 8:56pm

You debug on the server side. The two most common methods are PDB (the Python debugger) and print statements. I tend to favor the latter as a first option as it tends to be quick and easy.

Going back to your starting point, you had in your template:

pierrot10:

      {% for station in stations_list %}
          <li>{{ station.station_name}} ({{ station.station_longname }}; ID Field:{{ station.fields_id_field }})</li>
          <!--<li><a href="{% url 'console:station' station.id_station %}">{{ station.station_name}} ({{ station.station_longname }}; ID Field:{{ station.fields_id_field }})</a></li>-->
      {% endfor %}

Looking only at this section:
ID Field:{{ station.fields_id_field }}
What you are printing is the default representation of that object.

At this specific point, station.fields_id_field is a reference to a Fields object.

Accessing an attribute of that Fields object is done with the dot notation.

So, if you want the field named field_name for that Fields object:
ID Field:{{ station.fields_id_field.field_name }}

pierrot10 · July 14, 2022, 8:58pm

Same here
How can I debug ‘stations_list’ to see the result of the MySQL request.

Thanks KenWhitesell, I am going to read the post you sent

pierrot10 · July 14, 2022, 9:04pm

OK, THNAKS, I understand. Thanks a lot, It’s work station.fields_id_field.field_name. But sorry to insite, is there no way to loop ‘station.fields_id_field’?

The two most common methods are PDB (the Python debugger) and print statements.

I am going to investigate,
thanks you

KenWhitesell · July 14, 2022, 9:46pm

What are you trying to do? station.fields_id_field is a single object. What are you looking to iterate over?

Topic		Replies	Views
[Beginner] How can I get the data from mySQL Using the ORM	19	1499	July 25, 2022
Simple(ish) SQL to ORM query Using the ORM	2	432	March 23, 2023
ORM filtering with nested joins Using the ORM	4	1962	August 14, 2022
How to extract usable SQL from a QuerySet? ORM	2	4649	August 26, 2020
Filtering models in view Forms & APIs	2	421	August 12, 2022

[Beginner] How can I filter my MySQL query

Related topics