Web Dev Bootcamp TIL Day-51(Django ORM: Q() and _related)

• Django's built-in query object Q() allows us to query data in an equivalent manner to SQL WHERE clauses.

from django.db.models import Q

q = Q()

q.add(Q(no=12121), q.OR)
q.add(Q(name=lee)|Q(name=kim), q.AND)
q.add(Q(142411), q.OR)

Base.objects.filter(q)
# equivalent to:
# SELECT * FROM base WHERE (no=12121 AND (name='lee' OR name='kim')) OR no=142411

• Q() comes in handy when we want to build lengthy queries, perhaps from a search parameter list

skills = request.query_params.getlist('skills', '')
# skills = ['Python', 'MySQL', 'Java', 'PostgreSQL', 'Node.js', 'React Native'] 

query = Q()

for skill in skills:
    query.add(Q(skill_set__name=skill), Q.OR)

# Q(skillset__name='python') | Q(skillset__name='MySQL') | ...

job_skills = JobPostSkillSet.objects.filter(query)

---

• We can use select_related when the object that we are selecting is a single object such as:
  • OneToOneField
  • ForeignKey
• We use prefetch_related when we want to return a "set" of things:
  • ManyToManyFields
  • reverse ForeignKeys

class ModelA(models.Model):
    pass

class ModelB(models.Model):
    a = ForeignKey(ModelA)

ModelB.objects.select_related('a').all() # Forward ForeignKey relationship
ModelA.objects.prefetch_related('modelb_set').all() # Reverse ForeignKey relationship

• The main difference between the two are that select_related does an SQL join and therefore gets the results back as part of the table from the SQL server. 
• On the other hand, prefetch_related executes another query and therefore reduces the redundant columns in the original object (ModelA in the above example).
• Though we may use prefetch_related for anything that we can use select_related for, it is important to consider the cost efficiency of each query.
  • select_related uses a JOIN in the SQL whereas prefetch_related runs the query on the first model, collects all the IDs it needs to prefetch and then runs a query with an IN clause in the WHERE with all the IDs that it needs.
  • If you have say 3-5 models using the same foreign key, select_related will almost certainly be better.
  • If you have 100s or 1000s of models using the same foreign key, prefetch_related could actually be better.