Documented examples for common denormalizations

[simonw]

I'm principally interested in using this library for denormalizations - thinks like:

class BlogEntry(Model)
    # ...
    num_comments = IntegerField()

class Comment(Model):
    entry = ForeignKey(BlogEntry)
    # ...

Where that num_comments column stores the number of comments, and is updated by a trigger any time a comment is added or deleted.

It would be great if the documentation included some examples of these! Happy to contribute some if I end up figuring this out for my project.

[dracos]

I think something like this does what you're after:

@pgtrigger.register(
    pgtrigger.Trigger(
        name='keep_num_comments_in_sync',
        operation=pgtrigger.Update | pgtrigger.Insert | pgtrigger.Delete,
        when=pgtrigger.After,
        func='''
        IF TG_OP IN ('DELETE', 'UPDATE') THEN
            UPDATE blog_blogentry SET num_comments = num_comments - 1 WHERE id = OLD.entry_id;
        END IF;
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            UPDATE blog_blogentry SET num_comments = num_comments + 1 WHERE id = NEW.entry_id;
        END IF;
        RETURN NULL;
        ''',
    )
)

Deals with someone moving a comment from one blog entry to another as well. Could have 3 separate triggers without the IFs if that's preferable.

[wesleykendall]

@dracos very clever for the update case! Yes, you can do three as well and then have a condition on the update to only fire when the entry has been changed.

@simonw Did this address your issue? I can add it as a more advanced use case to the docs since this is also a problem I address with pgtrigger all the time (precomputing balances, etc).

I'm open to ideas of adding something like this as a core trigger if it can be generalized too.

[pgcd]

I have a similar need with a twist: we have several "container" models that need to do things like "this object's price is the sum of all its components' prices" etc, so I can't directly use the examples I've seen around the web, as they all seem to directly use the individual value of the row being updated.

My initial attempt is this (note that I will need to add Delete support as well, but first I should get it to work)

UpdateContainer = pgtrigger.Trigger(
    name="update_price_after_tax",
    when=pgtrigger.After,
    operation=pgtrigger.Insert | pgtrigger.UpdateOf("_after_tax"),
    condition=pgtrigger.Q(new__container__isnull=False),
    func="""UPDATE 
    app_container t set price_after_tax=sub_q.SUM_price_after_tax 
    FROM (
        SELECT SUM(price_after_tax) as SUM_price_after_tax, container_id from app_component 
        GROUP BY container_id
        ) sub_q
    WHERE sub_q.container_id=t.uuid;
    RETURN NULL;
    """,
)

There are several omissions (eg. it's not generalized at all, and I think I might have to add a filter to ensure only the relevant container is updated) but the main problem is that the above doesn't include the current component being saved - that is, if I save component1 with price_after_tax=100 and component2 with price_after_tax=50, the container ends up having price_after_tax=100 rather than 150. On the other hand, when I save component2 the second time, the sum is correct. From what I can understand, the SUM operates on already existing rows (possibly because the component save() is wrapped in a transaction) - and I have no idea whatsoever how to include the current row.

UPDATE: I also tried with a statement level trigger but, unfortunately, the result is the same, so I have no idea how to proceed.