CODEWITHSUNDEEP
djangodjango-modelsdjango-rest-frameworkdjango-filter
shahab
shahab

Reputation: 323

how to filter a model objects by related table's foreign key in django REST

I want to make an API for a restaurant, and my models.py is like this:

class Table(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=30, null=False, blank=False)
    is_free = models.BooleanField(default=True)

    def __str__(self):
        return '%s %s %s' % (self.id, self.name, self.is_free)


class Order(models.Model):
    order_id = models.AutoField(primary_key=True)
    table_id = models.ForeignKey(Table, on_delete=models.CASCADE)
    total_price = models.IntegerField()
    factor_id = models.IntegerField()

    def __str__(self):
        return '%s %s %s' % (self.order_id, self.table_id.id, self.total_price)


class OrderDetail(models.Model):
    order_detail_id = models.IntegerField(primary_key=True)
    order_id = models.ForeignKey(Order, on_delete=models.CASCADE)
    food_id = models.ForeignKey(Food, on_delete=models.CASCADE)
    amount = models.IntegerField()
    price = models.IntegerField()

    def __str__(self):
        return '%s %s %s' % (self.order_detail_id, self.order_id, self.food_id.name)

When an Order with a Table created, some OrderDetails should add to it.

I want to filter all OrderDetail objects that are for a specific Table and are for the latest Order(I mean all OrderDetail objects that are active now). The client request URL with filter contains table_id and not order_id. How can I filter OrderDetail objects by the table that have it?

Each table can only have one order in a moment, When an Order creates with a specific Table, the is_free will set to False. So the table_id field of Order class should be models.ForeignKey not models.OneToOneField, I think so! :) If it's wrong please tell me.

Thanks in advance.

Upvotes: 0

Views: 1861

Answers (2)

JPG
JPG

Reputation: 88429

Try this,

order_detail_queryset = OrderDetail.objects.filter(
    order_id__table_id=table_id,
    order_id=Order.objects.filter(
        table_id=table_id).latest(
        'table_id'))


I didn't tested the answer, but it might work as expected

Upvotes: 1

mythr
mythr

Reputation: 102

What I understood from your model is that Table never gets new entries and you generate new Order based on the Table that has is_free set to TRUE.

So, in order to get the Order from table_id you require some field by which you can get the latest order of that Table. Right now you have order_id but I would not suggest you to do it. The best way could be to add some Date/Time field in class Order.

Your query could be something like:

SELECT * FROM OrderDetail WHERE order_id = (SELECT order_id FROM Order WHERE table_id = <table_id> ORDER BY date_time DESC LIMIT 1)

Hope that helps.

Upvotes: 0

Related Questions