Clarification on depends using related_name vs. related_name_query and empty dependency lists

[hayder-mustafa-aswar]

@jerch I encountered two issues/questions related to the depends configuration. Below is the relevant snippet from my model:

amount_residual = ComputedField(
    MonetaryField(
        currency_field="company_currency",
        verbose_name="Residual Amount",
        editable=False,
        help_text="The residual amount on a journal item expressed in the company currency.",
    ),
    depends=[
        (
            "self",
            [
                "debit",
                "credit",
                "amount_currency",
                "account",
                "currency",
                "company",
            ],
        ),
        (
            "matched_debit",
            [],
        ),
        (
            "matched_credit",
            [],
        ),
    ],
    select_related=["currency", "company"],
    prefetch_related=["matched_debits", "matched_credits"],
    compute=lambda self: self._compute_residual_amounts_and_reconciled(
        "amount_residual"
    ),
)

1. Difference between "matched_debit" and "matched_debits" in depends

• What happened:

  Prior to the latest upgrade, my depends block used "matched_debit" (the related_name_query on the reverse FK). After updating, I changed it to "matched_debits" (the usual related_name for the reverse relationship).

  As soon as I switched to "matched_debits", profiling revealed a large number of unexpected JOINs/queries and a significant slowdown when saving or updating AccountMoveLine records.

  Reverting back to "matched_debit" (i.e., the related_name_query) immediately fixed the performance issue.

• Questions:

  What is the semantic difference between using a related_name_query (e.g. "matched_debit") versus a direct related_name (e.g. "matched_debits") in the depends list?

  Why does switching to "matched_debits" (the “normal” related name) cause additional queries or JOINs, whereas "matched_debit" (the query-optimized reverse name) does not?

2. Using an empty list for related‐field dependencies
   In my depends block, I wrote:

(
    "matched_credit",
    [],
),

I wanted this to mean “whenever any related credit move line is added/removed/updated, recompute amount_residual, but I don’t actually depend on any specific field of matched_credit.”

• Question:

  Is it correct to specify a related field with an empty list ([]) when I only want to trigger the computation on any change to that related object, without reading a particular attribute?

  If not, what is the recommended way to express “depend on any change to the matched_credit reverse relationship, without listing individual fields”?

[jerch]

Well regarding related_name_query - you are the first one using it different from related_name and why the latest change happened. My first guess would be, that the resolver still does something wrong during the related_name --> related_name_query transition, as it is not battle-tested yet. There are 2 possibilities here:

• either related_name does the right thing and related_name_query misses some crucial updates (would be my first guess)
• or related_name_query is correct and related_name duplicates stuff

Can you show me the intermodel graphs for both scenarios? Either missed out or duplicated updates should be clearly visible there.

Regarding your question:
Nope, an empty list on the right side is wrong. There should always go the real concrete fields data is pulled from, e.g. if you concat a string field 'name' from a set in your compute function, 'name' should be listed there.

Only edge case, where you might struggle to see, which fields are really needed, are aggregation functions like count(), that operate without field notions. For that you should place at least the fk fieldname there. Why? Because the resolver walks the dependencies backwards at update time, thus for a compute function like (untested):

class A(ComputedFieldsModel):
    @computed(..., depends=[('a_set', ['a'])])
    def computed_field(self):
        return self.a_set.all().count()

class B(models.Model):
    a = models.models.ForeignKey(A, ...)

it resolves the instances of A to be updated via B.a matches (thus the fk field).

[hayder-mustafa-aswar]

@jerch So after more testing it seems the two points i mentioned are related, if add id to the right side the same issue appears

  (
      "matched_debit",
      ["id"],
  ),

it behave same as

  (
      "matched_debits",
      [],
  ),

so i think the issue it's about leaving the right side empty not about using related_name or related_name_query

but the problem here is it's generating weird queries with a lot of joins, i will be glad to provide the intermodal graphs but it's so complicated, i will show you an example :

but i will show you example of the weird queries happening

(
  SELECT
    am.created,
    am.modified,
    am.id,
    …             -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  INNER JOIN accounting_account_partial_reconcile AS pr
    ON aml.id = pr.debit_move_line_id
  INNER JOIN accounting_account_move_line AS credit_aml
    ON pr.credit_move_line_id = credit_aml.id
  INNER JOIN accounting_account_move AS am_credit
    ON credit_aml.move_id = am_credit.id
  WHERE am_credit.payment_id IN (
    SELECT p.id
    FROM accounting_payment AS p
    WHERE p.id IN (f281f17a-19ef-4dd3-8ba5-ea2a83447bf9)
  )
  ORDER BY am.date DESC, am.name DESC, am.invoice_date DESC, am.created DESC
)
UNION
(
  SELECT
    am.created,
    am.modified,
    am.id,
    …             -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  WHERE aml.payment_id IN (
    SELECT p.id
    FROM accounting_payment AS p
    WHERE p.id IN (f281f17a-19ef-4dd3-8ba5-ea2a83447bf9)
  )
  ORDER BY am.date DESC, am.name DESC, am.invoice_date DESC, am.created DESC
)
UNION
(
  SELECT
    am.created,
    am.modified,
    am.id,
    …             -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  INNER JOIN accounting_account_partial_reconcile AS pr
    ON aml.id = pr.credit_move_line_id
  INNER JOIN accounting_account_move_line AS debit_aml
    ON pr.debit_move_line_id = debit_aml.id
  INNER JOIN accounting_account_move AS am_debit
    ON debit_aml.move_id = am_debit.id
  WHERE am_debit.payment_id IN (
    SELECT p.id
    FROM accounting_payment AS p
    WHERE p.id IN (f281f17a-19ef-4dd3-8ba5-ea2a83447bf9)
  )
  ORDER BY am.date DESC, am.name DESC, am.invoice_date DESC, am.created DESC
);

(
  SELECT
    am.created,
    am.modified,
    am.id,
    …  -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  INNER JOIN accounting_account_partial_reconcile AS pr_debit
    ON aml.id = pr_debit.debit_move_line_id
  INNER JOIN accounting_account_move_line AS T4_credit
    ON pr_debit.credit_move_line_id = T4_credit.id
  INNER JOIN accounting_account_move AS T5_credit_move
    ON T4_credit.move_id = T5_credit_move.id
  INNER JOIN accounting_account_move_line AS T6_line
    ON T5_credit_move.id = T6_line.move_id
  WHERE T6_line.id IN (
    SELECT U0.id
    FROM accounting_account_move_line AS U0
    WHERE U0.id IN (
      0d6782d3-a926-44aa-a220-ec1d1633ee7b,
      59d93470-d5b0-47c3-962f-95476f3508d1
    )
  )
  ORDER BY
    am.date DESC,
    am.name DESC,
    am.invoice_date DESC,
    am.created DESC
)
UNION
(
  SELECT
    am.created,
    am.modified,
    am.id,
    …  -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  INNER JOIN accounting_account_partial_reconcile AS pr_credit
    ON aml.id = pr_credit.credit_move_line_id
  WHERE pr_credit.debit_move_line_id IN (
    SELECT U0.id
    FROM accounting_account_move_line AS U0
    WHERE U0.id IN (
      0d6782d3-a926-44aa-a220-ec1d1633ee7b,
      59d93470-d5b0-47c3-962f-95476f3508d1
    )
  )
  ORDER BY
    am.date DESC,
    am.name DESC,
    am.invoice_date DESC,
    am.created DESC
)
UNION
(
  SELECT
    am.created,
    am.modified,
    am.id,
    …  -- (other accounting_account_move columns omitted)
  FROM accounting_account_move AS am
  INNER JOIN accounting_account_move_line AS aml
    ON am.id = aml.move_id
  INNER JOIN accounting_account_partial_reconcile AS pr_credit
    ON aml.id = pr_credit.credit_move_line_id
  INNER JOIN accounting_account_move_line AS T4_debit
    ON pr_credit.debit_move_line_id = T4_debit.id
  INNER JOIN accounting_account_move AS T5_debit_move
    ON T4_debit.move_id = T5_debit_move.id
  INNER JOIN accounting_account_move_line AS T6_line
    ON T5_debit_move.id = T6_line.move_id
  WHERE T6_line.id IN (
    SELECT U0.id
    FROM accounting_account_move_line AS U0
    WHERE U0.id IN (
      0d6782d3-a926-44aa-a220-ec1d1633ee7b,
      59d93470-d5b0-47c3-962f-95476f3508d1
    )
  )
  ORDER BY
    am.date DESC,
    am.name DESC,
    am.invoice_date DESC,
    am.created DESC
);

and the performance is reduced a lot because of these queries

[jerch]

Oh good find with the empty vs. non empty field list, so there is indeed an issue with the resolver and the new related_name_query handling.

About your listed queries these things stick out:

• many JOINs in base query - are there inherited models involved?
• repeating enitities in UNIONs
• --> high cost of base query with duplicated entities in UNION results is bad runtime

Could it be, that you have multiple CFs on the same model depending on the same related source entities (depends has a lot of intersections)? That is the a case, where the union would be filled with the same cf entity multiple time and the DB actually would have to filter it down. In an older version (pre 0.2.0) there was a transitive reduction in the resolver for intermodel dependencies, but I removed it with 0.2.0 as it had no to negative impact, was only able to aggregate duplicates on the same tree level and was quite complicated code to maintain.

[hayder-mustafa-aswar]

@jerch yes that's right, i have multiple CFs on the same model depending on the same related source entities, is there a fix for that ?

[jerch]

No there is no easy fix for that. Note that your UNIONed queries slightly differ at various points, esp. at the joints. This needs a bit more debugging first, could you post the output of:

from computedfields.resolver import active_resolver
active_resolver._map[model_with_intersecting_CFs]

It could be, that the static nature of the deps resolving causes this, but I am not sure atm.

If you are adventurous you could try the following and check, if it helps to reduce the "union pressure":

• identify all related source fields, that occur multiple times in CFs on one model
• for each, create a new CF on that model just depending on that source field holding its value
• reshape CFs to now depend on that model local CF instead of the related one

[hayder-mustafa-aswar]

@jerch

    "line": {
        "<class 'accounting.models.account_move.AccountMove'>": (
            {
                "amount_untaxed",
                "amount_tax_signed",
                "amount_residual",
                "amount_untaxed_signed",
                "amount_total_in_currency_signed",
                "amount_total",
                "amount_tax",
                "amount_residual_signed",
                "amount_total_signed",
            },
            {
                "line__payment__move",
                "line__matched_credit__credit_move_line__move",
                "line__matched_debit__debit_move_line__move",
            },
        ),
        "<class 'accounting.models.payment.Payment'>": (
            {"is_reconciled", "is_matched"},
            {"move"},
        ),
    },
    "state": {
        "<class 'purchase.models.purchase_order_line.PurchaseOrderLine'>": (
            {"qty_billed", "qty_to_bill"},
            {"bill_line__move"},
        ),
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"parent_state"},
            {"move"},
        ),
        "<class 'accounting.models.account_move.AccountMove'>": (
            {
                "amount_untaxed",
                "amount_tax_signed",
                "amount_residual",
                "amount_untaxed_signed",
                "amount_total_in_currency_signed",
                "amount_tax",
                "amount_total",
                "amount_residual_signed",
                "amount_total_signed",
            },
            {
                "line__payment__move",
                "line__matched_credit__credit_move_line__move",
                "line__matched_debit__debit_move_line__move",
            },
        ),
        "<class 'sales.models.sale_order_line.SaleOrderLine'>": (
            {"qty_invoiced"},
            {"invoice_line__move"},
        ),
        "<class 'accounting.models.asset.Asset'>": (
            {"value_residual"},
            {"depreciation_move"},
        ),
    },
    "amount_total_signed": {
        "<class 'accounting.models.payment.Payment'>": (
            {"amount_company_currency_signed"},
            {"move"},
        )
    },
    "name": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"move_name"},
            {"move"},
        )
    },
    "is_storno": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"debit", "credit"},
            {"move"},
        )
    },
    "accountmovereversal": {
        "<class 'accounting.models.account_move_reversal.AccountMoveReversal'>": (
            {"journal_id"},
            {"moves"},
        )
    },
    "asset": {
        "<class 'accounting.models.asset.Asset'>": (
            {"disposal_date"},
            {"depreciation_move"},
        )
    },
    "ref": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"ref"},
            {"move"},
        )
    },
    "company_currency": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"company_currency_id"},
            {"move"},
        )
    },
    "currency": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"currency_id"},
            {"move"},
        )
    },
    "invoice_date": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"invoice_date"},
            {"move"},
        )
    },
    "company": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"company_id"},
            {"move"},
        )
    },
    "reversal_move": {
        "<class 'accounting.models.asset.Asset'>": (
            {"value_residual"},
            {"depreciation_move"},
        )
    },
    "date": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"currency_rate", "date"},
            {"move"},
        ),
        "<class 'accounting.models.asset.Asset'>": (
            {"disposal_date"},
            {"depreciation_move"},
        ),
    },
    "journal": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"journal_id"},
            {"move"},
        )
    },
    "payment": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"payment_id"},
            {"move"},
        )
    },
    "depreciation_value": {
        "<class 'accounting.models.asset.Asset'>": (
            {"value_residual"},
            {"depreciation_move"},
        )
    },
    "contact": {
        "<class 'accounting.models.account_move_line.AccountMoveLine'>": (
            {"contact_id"},
            {"move"},
        )
    },
}

[hayder-mustafa-aswar]

If you are adventurous you could try the following and check, if it helps to reduce the "union pressure":

• identify all related source fields, that occur multiple times in CFs on one model
• for each, create a new CF on that model just depending on that source field holding its value
• reshape CFs to now depend on that model local CF instead of the related one

I will try this tmw

[jerch]

Oh I see the problem here - AccountMoveLine gets joined in like 12 times, but with slightly different fields. All of these relations, that point to the same fk field (either in forward or backward direction), could be merged into 1 joint instead. WARNING: It is important here to only merge on the same fk field. Or in other words - if you have a second relation to/from AccountMoveLine, it must not be merged with the first relation, as it can point to different table rows.

Yes here it makes sense to extend depends to contain all needed fields at once and use that temporary cache pattern from your other issue.

EDIT: I am not quite sure, if the ORM would detect adding the same OR'ed or UNION'ed clause again and again and reduce it to just one occurrence. If not than the resolver might need special code to handle that edge case. (Edit: No the ORM does not fuse UNIONs at all...)

EDIT2: Having so many CFs on one model pointing effectively to the same data sources feels like poor choice in model schemes to me. Sure this can happen in data warehousing for the purpose of decoupling and audit/report needs, so I wont comment further on this. Just wanted to mention it here once...