CODEWITHSUNDEEP
mysqldjangodjango-modelsdjango-templates
isaapm
isaapm

Reputation: 157

Django -- Custom filter or SQL raw

I have an application with a model which has three tables implementing the toxi tagging model. One table for jobs, one table for skills and one table to map them, in the form of jobid -- skillid.

Due to the number of mappings if I use the following:

{% for job in jobs %}
  <p>{{ job.job_title }}</p>
                        {% for skill in skillmaps %}
                        {% if job.id == skill.job_id.id %}
                                #{{ skill.skill_id|title }}
                        {% endif %}
                        {% endfor %}
{% endwith %}

It just simply uses all the processor and takes a very long time, in the order of 30 minutes.

If I query the DB for all the skillids in the mapping table where jobid = "nnn", the query is really fast.

I was wondering what would be the best approach, as I would like something like this:

{% for job in jobs %}
  <p>{{ job.job_title }}</p>
  {{ print all skills for job.job_id}}
{% endwith %}

I've tried with custom filters, but I can't get the code above to work, as it seems I have to filter a value from a for skillmaps foor lop, which it's just the same problem. Is it possible to define a class with a raw query and pass a variable from the for loop in the template? Or what would be the best approach?

The model:

class Job(models.Model):
    job_title = models.CharField(max_length=200)
    job_identifier = models.CharField(max_length=140)
    job_site = models.URLField(max_length=200)
    job_url = models.URLField(max_length=400)
    job_salary = models.PositiveIntegerField(default=0, blank=True, null=True)
    def __unicode__(self):
        return self.job_title[:200]

class Skill(models.Model):
    skill_name = models.CharField(max_length=200)
    def __unicode__(self):
        return self.skill_name[:200]

class SkillMap(models.Model):
        job_id = models.ForeignKey(Job)
        skill_id = models.ForeignKey(Skill)
        def get_skills_mod(self, *args):
                return SkillMap.objects.filter(job_id = args)

Thanks, Isaac

Upvotes: 1

Views: 141

Answers (1)

yedpodtrzitko
yedpodtrzitko

Reputation: 9359

This is not very clean solution, but you can fetch all SkillMap at once in your view, create a dictionary with job_id as a key and then inject them into Job :p

from collections import defaultdict

def my_view(request):
    skills = SkillMap.objects.all()
    skills_dict = defaultdict(list)
    for x in skills:
        skills_dict[x.job_id].append(x)

    jobs = Job.objects.all()
    for job in jobs:
        job.injected_skills = skills_dict.get(job.id, [])

    template_context = {
        'jobs': jobs,
    }

template:

{% for job in jobs %}
    <p>{{ job.job_title }}</p>
    {% for skill in job.injected_skills %}
         {{ skill }}
    {% endfor %}
{% endwith %}

Upvotes: 1

Related Questions