Simple SQL query with join

Using Django Using the ORM
1

Hello.

I have this situation, it’s probably simple and the solution is somewhere on the surface, but I’m a newbie and definitely in the fog.
So I have two related database tables. One contains a list of people, the other records their actions. I want the table in the application to look like a list of employees with the number of actions from the second table.

The training request looks like this:

    select SPEC.id, SPEC.spec_fname, SPEC.spec_lname, Count(CAL.id) as 'Count'
    from app_specmodel as SPEC
    inner join app_calendarmodel as CAL on SPEC.id = CAL.cl_spec_id
    group by SPEC.id

My models look like this:

    class SpecModel(models.Model):
    id = models.BigAutoField(primary_key=True)
    firm = models.ForeignKey(Firm, on_delete=models.CASCADE)
    spec_fname = models.CharField(max_length=30, null=False)
    spec_lname = models.CharField(max_length=30, null=True, blank=True)
    spec_phone = models.CharField(max_length=12, null=True, blank=True)
    spec_color = models.CharField(max_length=10, null=False)
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    updated = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['spec_fname']

    def __str__(self):
        return self.spec_fname + " " + self.spec_lname

    

    class CalendarModel(models.Model):
    id = models.BigAutoField(primary_key=True)
    cl_date = models.DateField(null=False)
    cl_saloon = models.ForeignKey(SaloonModel, on_delete=models.CASCADE)
    cl_spec = models.ForeignKey(SpecModel, on_delete=models.SET_NULL, null=True)
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    updated = models.DateTimeField(auto_now=True)
    
    class Meta:
        ordering = ['id']
        
    def __str__(self) -> str:
        return self.cl_spec

views.py

    def app_spec(request):
    firm_id = get_company(request.user.id)
    if request.GET.get("q") != None:
        q = request.GET.get("q")
        data = SpecModel.objects.filter(
            Q(firm=firm_id) & (Q(spec_fname=q) | Q(spec_lname=q) | Q(spec_phone=q)) & ()
        )
    else:
        q = ""
        data = SpecModel.objects.filter(firm=firm_id)

    context = {"title": "Spec", "query": q, "spec_data": data}
    context.update(stateEngine("SPEC"))
    return render(request, template_name="app_spec.html", context=context)

my result now

Снимок экрана 2024-02-18 163657
Снимок экрана 2024-02-18 1636571673×590 24.4 KB

How to organize the formation of complex queries to different tables using Django. And how to “correctly” organize classes and functions for this. Thanks

2

I’m not sure what you’re trying to get here from your description.

You wrote:

Which I read as saying that you just want a count of the number of CalendarModel related to SpecModel.

On the other hand, the SQL you posted is going to return all of the instances of CalendarModel related to each SpecModel.

Either way is easy using the ORM.

If you want all the related CalendarModel for an instance of SpecModel, you get them using the related object manager. See Related objects reference | Django documentation | Django and Model field reference | Django documentation | Django for details.

In this case, if you have an instance of SpecModel named spec_model, then the set of related CalendarModel would be spec_model.calendarmodel_set.all().

If you just want the number of related CalendarModel, it would be spec_model.calendarmodel_set.count().

3

Yes, I want to see this column in my table.

But this is not obvious to me yet. The documentation does not give a complete picture of how it works in the complex.

4

So I can do it like this

class SpecModel(models.Model):
    id = models.BigAutoField(primary_key=True)
    ...
    
    @property
    def countCal(self):
        return CalendarModel.objects.filter(cl_spec=self.id).count()

and in views.py

...    
    for item in data:
        print(item.countCal)
...

but there is probably a more “correct” solution (here are 2 queries to the database)

5

Actually, done this way, it’s an N+1 query situation if you’re retrieving a set of SpecModel.

What you want to do in this case is annotate that count as a new field in the queryset.
See Aggregation | Django documentation | Django for details, particularly the sixth example in the Cheat Sheet

1 Like
6

Thank you Ken, very much

in views.py

from django.db.models import Count

spec = SpecModel.objects.annotate(Count('calendarmodel'))
print(spec.query)
    for i in spec:
        print(i.calendarmodel__count)

it’s so simple it’s embarrassing.