Feature Request: Introduce many_to_many argument in Func.render

[jacek-rybak]

Description:
The Func class allows for rendering SQL functions with access to the model's meta, fields, and columns variables. However, there is currently no direct way to access ManyToMany fields by their name. The only available approach requires using positional indexing, which can lead to fragile code when models are altered.

For example, to access a ManyToMany field, users currently have to use:

{{meta.many_to_many[0].remote_field.through._meta.db_table}}

Proposed Solution:
To improve this, we can modify the render method in the Func class to include a many_to_many argument, allowing access to ManyToMany fields by name:

def render(self, model: models.Model) -> str:
    """
    Render the SQL of the function.

    Args:
        model: The model.

    Returns:
        The rendered SQL.
    """
    fields = utils.AttrDict({field.name: field for field in model._meta.fields})
    columns = utils.AttrDict({field.name: field.column for field in model._meta.fields})
    many_to_many = utils.AttrDict({field.name: field for field in model._meta.many_to_many})

    return self.func.format(meta=model._meta, fields=fields, columns=columns, many_to_many=many_to_many)

Then, in Func, it would be possible to use:

# Assuming we have models A and B and A has a M2M to B called b_items,
# and A is an abstract model that can be inherited.
pgtrigger.Func(f"""
...
                    SELECT array_agg(model_a_to_b.b_id)
                    INTO b_ids
                    FROM {{many_to_many['b_items'].remote_field.through._meta.db_table}} model_a_to_b
                    WHERE model_a_to_b.{{meta.model_name}}_id = NEW.id;
...
""")

Benefits:

• Better Maintainability: Accessing fields by name reduces the risk of breaking code when the field order changes.
• Ease of Use: Simplifies the syntax for accessing ManyToMany fields.
• Dynamic Models: Particularly useful for models that inherit from abstract base classes where related_name attributes are set dynamically.

Version Info:

• Django: 5.0.7
• django-pgtrigger: 4.12.2

PS: Just wanted to say a big thank you to the authors for creating such an amazing library! It’s been a huge help in our projects, and we really appreciate all the hard work you put into it. Keep up the great work! 🙌

[wesleykendall]

Thanks for the suggestion! Writing triggers without hardcoding names has been a long-standing request in pgtrigger. There is a discussion here about using Django's templating system and template tags for things like this.

I'm surprised to hear there is no way with Django's meta to reference a M2M field by name. Just tried it myself. The _meta object seems to be all over the place in what it offers.

What I'm concerned about is if we implement this request, someone else will make a feature request to access the many_to_many of any model rather than the model over which the trigger is defined.

Similar to the other discussion, what if one could write a template like this:

{% load meta from pgtrigger %}

{% meta "app.MyModel" as my_model_meta %}

SELECT * from {{ my_model_meta.fields.m2m_field_name... }}

Here my_model_meta is an extended Meta object. We would either create a m2m_fields map or just included many-to-many fields in the fields map.

Then in the trigger definition itself, you'd be able to reference the template like a django template:

class MyModel:
    class Meta:
        triggers = [pgtrigger.Trigger(func=pgtrigger.Template("path/to/template.sql"))]

Thoughts on if this would help?

[jwpeddle]

I'm surprised to hear there is no way with Django's meta to reference a M2M field by name. Just tried it myself. The _meta object seems to be all over the place in what it offers.

_meta.get_fields() provides a more complete list of fields than _meta.fields, including m2m fields and all reverse relations.

[jacek-rybak]

@wesleykendall Utilizing a templating system sounds like an excellent idea. It should address most of the issues related to complex triggers and will certainly make maintenance easier with separate files.

@jwpeddle

_meta.get_fields() provides a more complete list of fields than _meta.fields, including m2m fields and all reverse relations.

Good point! A shorter solution to the problem could be:

    def render(self, model: models.Model) -> str:
        ...
        fields = utils.AttrDict({field.name: field for field in model._meta.get_fields()})
        ...

[wesleykendall]

BTW moving this to a discussion instead of issue for now. Yes, I would love if we could make it possible to use templates and solve some of the pain points with the more complex triggers.

I'm open to just about any solution that:

a) Can leverage Django's templating system
b) Has template tags or enough context variables to help remove a lot of the boilerplate for complex triggers.

If you or others want to follow up on #165, that might be the best place for this discussion going forward since it seems like the template approach would solve this.

In August, I responded to the most recent thread of #165 about getting this into pgtrigger. I unfortunately don't know how much time I will be able to dedicate to this particular feature. If someone starts with a pull request, I'm happy to get it into the library fast or help get the final pieces across the finish line and into the library