CASE clause for this relation, testing aggregate functions over another relation

By bignose

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

    

Share it with your friends!

    Fatal error: Uncaught Exception: 12: REST API is deprecated for versions v2.1 and higher (12) thrown in /home/content/19/9652219/html/wp-content/plugins/seo-facebook-comments/facebook/base_facebook.php on line 1273