CODEWITHSUNDEEP
djangodjango-modelsdjango-templatesdjango-views
Bob
Bob

Reputation: 329

Best way to show data from multiple tables

I have a recurring problem that I can't seem to solve adequately. My site is akin to a job site, where people can post jobs (and details within), and other people can bookmark tthem. Each job can obviously be bookmarked by more than one viewer.

So here's the model.py:

class Job(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    title = models.CharField(max_length=150)
    description = models.CharField(max_length=5000)


class Bookmark(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    job = models.ForeignKey(Job, on_delete=models.CASCADE)

My intention is to display all jobs in the homepage, that means multiple jobs from multiple users. Other users can click on a little bookmark icon to bookmark it, or to unbookmark it. Like so:

Here's the view.py:

def index(request):
    context = {}
    populateContext(request, context)

    jobs = Job.objects.all().order_by('-id')

    context = {'jobs': jobs}


    return render(request, 'templates/index.html', context)

Pretty simple I think, which is my intention. Here's how the template looks:

{% for job in jobs %}
    <div>
        {{ job.title }}<br>
        {{ job.description }}<br>
        by {{ job.user.username }}
    </div>
{% endfor %}

My question is, how do I display the bookmark state specific to each user? Here's my current solution:

{% for job in jobs %}
    <div>
        {{ job.title }}<br>
        {{ job.description }}<br>
        by {{ job.user.username }}<br>

        {% for watch in job.bookmark_set.all %}
            {% if watch.user_id = request.user.id %}
                You have bookmarked this! <a>Unbookmark!</a>
            {% endif %}
        {% endfor %}

        <a>Bookmark</a>
    </div>
{% endfor %}

The "Unbookmark!" link will be positioned over the "Bookmark" link by the way. The solution above works that way, because the bookmark table will contain zero or more bookmarks for a particular job, but under multiple users. I would handle this in the view.py, by filtering just jobs that the logged-in user has made. But I can't filter by which job specifically, because the index.html will be displaying them all. So for example, I could spit this out...:

bookmarked = Bookmark.objects.all().filter(user_id=request.user.id)

...and that would list out all bookmarks on different jobs that the logged-in user had made. I still need to filter that in the template, so that each project matches with each bookmark, and I understand this isn't possible.

Anyway, I think this is pretty inefficient. So I was wondering if there was an easier way to handle this? Preferably so that it works this way:

{% for job in jobs %}
    <div>
        {{ job.title }}<br>
        {{ job.description }}<br>
        by {{ job.user.username }}<br>

        {% if job.id = bookmark.job.id %}
            You have bookmarked this! <a>Unbookmark!</a>
        {% else %}
            <a>Bookmark</a>
        {% endif %}
    </div>
{% endfor %}

Thank you!

Upvotes: 1

Views: 887

Answers (1)

solarissmoke
solarissmoke

Reputation: 31404

You can use conditional expressions to annotate your jobs with this information. In the view:

from django.db.models import Case, IntegerField, Sum, When

jobs = Job.objects.annotate(
           is_bookmarked=Sum(Case(
               When(bookmark__user=request.user, then=1), 
               default=0, output_field=IntegerField()
           ))).order_by('-id')

Each job now has an is_bookmarked property which is either 1 (the user has bookmarked the job) or 0. In your template:

{% for job in jobs %}
    <div>
        {% if job.is_bookmarked %}
            You have bookmarked this! <a>Unbookmark!</a>
        {% else %}
            <a>Bookmark</a>
        {% endif %}
    </div>
{% endfor %}

Just for completeness, the other approach you had in mind would also work (although less efficient than the one above). In the view:

jobs = Job.objects.all().order_by('-id')
# Get a list of all Job IDs bookmarked by this user
user_bookmarks = Bookmark.objects.filter(user_id=request.user.id)\
                                 .values_list('job__id', flat=True)

In the template:

{% for job in jobs %}
    <div>  
        {% if job.id in user_bookmarks %}
            You have bookmarked this! <a>Unbookmark!</a>
        {% else %}
            <a>Bookmark</a>
        {% endif %}
    </div>
{% endfor %}

Both these approaches are doing pretty much the same logic - the difference being that the first one does this at database level which is generally more efficient.

Upvotes: 4

Related Questions