StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Expanding on my comment to your answer, I tried to write a single queryset that would do what you want, but it doesn't seem possible based on your data model.</p> <p>Your data model doesn't actually describe the date range that a particular doctor treated a particular patient. The query you're writing is going to return the wrong results, regardless of how you write the query. What you're currently saying is the following:</p> <p>"If a patient dies, then any doctors that have treated this patient that have <code>is_main=True</code> are going to be marked as responsible". (Responsible might not be the right word here, but you should get the idea).</p> <p>Now, the above is fine if you are only ever assigning a single GP as <code>is_main</code> to a particular patient.. but your data model doesn't enforce this, and could be prone to error. Especially if the <code>is_main</code> changes after a patients death. I would structure the data model as one of the following:</p> <pre><code>class GP(models.Model): name = models.CharField(max_length=64) class Patient(models.Model): name = models.CharField(max_length=64) class Death(models.Model): current_gp = models.ForeignKey(GP) patient = models.ForeignKey(Patient) date = models.DateField() class Consultation(models.Model): gp = models.ForeignKey(GP) patient = models.ForeignKey(Patient) start_date = models.DateField() end_date = models.DateField(blank=True, null=True) </code></pre> <p>Or..</p> <pre><code>class GP(models.Model): name = models.CharField(max_length=64) class Patient(models.Model): name = models.CharField(max_length=64) class Death(models.Model): patient = models.ForeignKey(Patient) date = models.DateField() class Consultation(models.Model): gp = models.ForeignKey(GP) patient = models.ForeignKey(Patient) start_date = models.DateField() end_date = models.DateField(blank=True, null=True) </code></pre> <p>The first structure has the benefit of allowing really nice queries that will be extremely performant, at the cost of requiring extra information to be entered at the time of the patients death. However, the <code>Consultation</code> (formerly PatientGPLink) model will have all the information required to deduce this information. You could also make the Death.current_gp be a ManyToManyField to support multiple GPs being responsible for the patient.</p> <p>The second structure can glean the same information, but requires datetime filtering, which will join another table, and make the query slower and more complicated.</p> <p>All of this is slightly irrelevant if you're very conscious about maintaining that <code>is_main</code> field, in that the data will be correct. But let me show you how to query the information you want in a (probably) more efficient manner from your view:</p> <pre><code>def my_view(request): doctors = GP.objects.all() periods = get_time_periods() # however it is you do this... smallest_date = get_smallest_date(time_periods) largest_date = get_largest_date(time_periods) deaths = Death.objects.select_related(depth=1).filter(date__range=(smallest_date, largest_date)) # build the results table with initial count of 0 to account for all doctors # {period: {doctor: count}} results = dict((period,{doctor: 0}) for doctor in doctors for period in periods) for death in deaths: for period in time_periods: # I'm assuming this is a small range of values if death.date > period['start_date'] and death.date < period['end_date']: results[period][death.current_gp] += 1 # add a death to the count </code></pre> <p>Then, in your template, you have the <code>results</code> table with all the precomputed information:</p> <pre><code><table> {% for period, lookup in results.items %} {% for doctor, deaths in lookup.items %} <tr> <td>{{ period }}</td> <td>{{ doctor }}</td> <td>{{ deaths }}</td> </tr> {% endfor %} {% endfor %} </table> </code></pre> <p>For a total of 2 SQL queries. There is more manual processing this way, but computing the results should be rather quicker than querying the database num_doctors * num_timeperiods + 1 times which is what you're currently doing.</p> <p>Edit:</p> <p>To make it work with your current model structure (if you really can't change the models...), you incorporate your answer with mine, and end up with a view that's very similar to the original one I wrote. I'm eliding all the comments since they'll be just the same as above. I've put comments in where I've changed the original view.</p> <pre><code> def my_view(request): doctors = GP.objects.all() periods = get_time_periods() smallest_date = get_smallest_date(time_periods) largest_date = get_largest_date(time_periods) # we make depth=3 so it spans via the PatientGPLink over to GP deaths = Death.objects.select_related(depth=3).filter(date__range=(smallest_date, largest_date)).filter(patient__patientgplink__ismain=True) results = dict((period,{doctor: 0}) for doctor in doctors for period in periods) for death in deaths: for period in time_periods: if death.date > period['start_date'] and death.date < period['end_date']: # and we change how we access the GP results[period][death.patient.patientgplink.gp] += 1 </code></pre> <p>Still only two queries, though those queries are larger (spanning multiple tables).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload