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.