djangodjango-modelsdjango-viewsdjango-querysetdjango-related-manager

How to 'order_by' model objects in Django using a field several levels of relationship apart


I have multiple models for an app that will contain option to generate forms for various companies. I am using mySQL. Also, I am a Noob :(

FSForm is the master form. Whenever it is generated, it gets its 'fskey' from the 'Company' object (some logic determines which of the two keys to select).

The are lots of 'FSRows' that a form can have. Django determines which rows to select and populate for the 'FSForm' as follows.

The 'FSKeyRow' objects pertaining to the 'fskey' of the form are retrieved. Then the corresponding 'FSRows' in those objects are populated as objects of the 'FSDataPoint' model for that form.

Following are my models:

class Company(models.Model):
    """Represents all companies"""
    symbol = models.CharField(max_length=5)
    name = models.CharField(max_length=255)
    annual_fskey = models.ForeignKey("FSKey", on_delete=models.CASCADE, related_name="company_annual_key")
    qtr_fskey = models.ForeignKey("FSKey", on_delete=models.CASCADE, related_name="company_qtr_key")

class FSForm(models.Model):
    """Represents all forms"""
    company = models.ForeignKey("Company", on_delete=models.CASCADE, related_name="company_fsform")
    functional_currency = models.ForeignKey("Currency", on_delete=models.CASCADE, related_name="fsform_currency")
    fskey = models.ForeignKey("FSKey", on_delete=models.CASCADE, related_name="fsform_key")

class FSKey(models.Model):
    """Represents keys which determine the rows to populate in the FSForm"""
    name = models.CharField(max_length=100)
    comments = models.TextField(max_length=500, blank=True)
    create_date = models.DateTimeField(auto_now_add=True)
    update_date = models.DateTimeField(auto_now=True)

class FSRow(models.Model):
    """Represents all possible rows"""
    name = models.CharField(max_length=255)
    comments = models.TextField(max_length=500, blank=True)

class FSKeyRow(models.Model):
    """Represents all rows that pertain to a key. This tells Django which rows to populate for an FSKey"""
    key = models.ForeignKey("FSKey", on_delete=models.CASCADE, related_name="fskey_key")
    row = models.ForeignKey("FSRow", on_delete=models.CASCADE, related_name="fskey_row")
    ordering = models.IntegerField(default=0)

class FSDataPoint(models.Model):
    """Represents DataPoints of the form. These are generated when a form is created based on FSKey applied to that form."""
    form = models.ForeignKey("FSForm", on_delete=models.CASCADE, related_name="fsform_datapoint")
    row = models.ForeignKey("FSRow", on_delete=models.CASCADE, related_name="fsrow_datapoint")
    float_data = models.FloatField(null=True, blank=True)
    str_data = models.CharField(max_length=255, blank=True)

There are a lot more fields in these models. I have just included those I thought would be necessary.

I need to retrieve the 'FSDataPoints' for a form IN THE ORDER OF THE 'ORDERING' FIELD in the FSKeyRow model. I cannot keep the 'ordering' field in FSDataPoints model or FSRows model as these values might change for each FSKey at any time which must effect all respective the FSDataPoint objects.

How might I access the FSDataPoints in that order from views.py? The shorter/simpler the better.


Solution

  • I guess this should do the trick:

    FSDataPoint.objects.order_by('row__fskey_row__ordering').all()