CODEWITHSUNDEEP
pythondjangodjango-models
Roger Gonzalez
Roger Gonzalez

Reputation: 409

How can I filter a Django-Model with 2 foreign keys?

I have the following models: 

class Event(models.Model):
    event = models.ForeignKey(EventType)
    name = models.CharField(max_length=128, null=True, blank=True)

class Order(models.Model):
    requester = models.CharField(max_length=128)
    email = models. EmailField()
    phone = models.CharField(max_length=13)
    payment_date = models.DateTimeField()
    added = models.DateTimeField(auto_now_add=True)
    viewed = models.BooleanField(default=False)
    password = models.CharField(max_length=6,blank=True,null=True)

class Picture(models.Model):
    event = models.ForeignKey(Event)
    image = models.ImageField(upload_to='media')
    show_name = models.CharField(max_length=128, blank=True, null= True)
    file_name = models.CharField(max_length=128, blank=True, null=True)

class OrderItem(models.Model):
    order = models.ForeignKey(Order)
    picture = models.ForeignKey(Picture)
    amount = models.PositiveSmallIntegerField()

I'm trying to count all the Orders that belong to each event. Sometimes, there are orders that are in many events, that's why I can't use a FK between Order and Event.

I've tried to get all the "OrderItem", then go up to Picture, and then go up to Event, so if there is a picture in an order that belong to an event, the counter counts one. But this is where I get stuck.

This is a part of my view.py:

active_events = Event.objects.filter(is_active=True)
pictures_detail = OrderItem.objects.all()
picture = Picture.objects.all()
print_order = PrintOrder.objects.all()
context_dict['print_order'] = {}

for event in active_events:
    print_order_counter = 0
    for detail in pictures_detail:
        for pic in picture:
            for order in print_order:
                print_order_counter += 1
                for orders in pictures_detail:
                    context_dict['print_order'].update(
                        {event.id : print_order_counter})

Any help would be appreciated.

Thank you in advance.

Upvotes: 0

Views: 309

Answers (2)

T. Opletal
T. Opletal

Reputation: 2294

The previous answer is not correct. You will kill your database that way. If you would need more info than just count, then you should use prefetch_related, otherwise when looping it will hit database in every iteration.

If you need multiple orders for multiple events, then there is many-to-many relationship https://docs.djangoproject.com/en/dev/topics/db/examples/many_to_many/

Since you just need count Orders for Event, it's even easier. Let's name each foreign key in the way with unique related_name:

class Picture(models.Model):
    event = models.ForeignKey(Event, related_name='event_pictures')

class OrderItem(models.Model):
    order = models.ForeignKey(Order, related_name='ordered_items')
    picture = models.ForeignKey(Picture, related_name='picture_orders')

Now, It's a bit hard for me to navigate in the database on the spot, but you should be able to do something like:

Events.objects.annotate(Count('event_pictures__picture_orders__order'))

Maybe a modify will be needed or adding a distinct. However you should definitely use this approach and not any iteration in python. That would completely kill the app. However if you would absolutely need to, then definitely use prefetch_related('event_pictures__picture_orders__order'), as mentioned earlier.

Upvotes: 0

Ian Price
Ian Price

Reputation: 7616

Without spelling out a complete picture, it seems you're using a complicated scheme. Using the related_name keyword argument for a model's field definition allows you to find all reverse-related foreign key objects; this can be set per foreign key and called by a parent model.

class OrderItem(models.Model):
    order = models.ForeignKey(Order)
    picture = models.ForeignKey(Picture,related_name='order_items')
    amount = models.PositiveSmallIntegerField()

for pic in Picture.objects.all():
    print(pic.order_items.all())

>>> [queryset of all order_items related to first Picture instance in Picture.objects.all()]
>>> [queryset of all order_items related to second Picture instance... etc.]

Upvotes: 1

Related Questions