How can I represent a single value in a query, based on testing several possible results from the aggregate of another relation in the join?
I have a database that is currently accessed via Django models:
class Lorem(models.Model): name = models.CharField() created = models.DateTimeField() def state(self): ipsum_states = [ipsum.state for ipsum in self.ipsum_set] if ipsum_states and all( ipsum_state == 'complete' for ipsum_state in ipsum_states): state = 'done' elif any( ipsum_state == 'error' for ipsum_state in ipsum_states): state = 'failure' elif any( ipsum_state == 'begin' for ipsum_state in ipsum_states): state = 'sending' else: state = 'not started' return state class Ipsum(models.Model): lorem = models.ForeignKey(Lorem) state = models.CharField()
That works correctly in Python. But it’s too slow to calculate for every instance.
So I want to have the
state value determined in a database view:
SELECT lorem.name AS name, CASE WHEN [… all the ipsum.states are 'complete' …] THEN 'done' WHEN [… any of the ipsum.states are 'error' …] THEN 'failure' WHEN [… any of the ipsum.states are 'begin' …] THEN 'sending' ELSE 'not started' END AS state FROM lorem LEFT INNER JOIN ipsum ON ipsum.lorem_id = lorem.id ;
How can I construct that CASE clause so that the aggregate functions used in the Python code will do the appropriate set operations in the SELECT?
Source: Stack Overflow