Question: ASUM in Many-to-Many relationship

[FelipeBarrosCruz]

Hello folks, I have a question that I can't solve by myself.
I have a many-to-many relatioship as below:

• products

  Column	Type
  id	UUID
  name	text
  price	decimal

• orders

  Column	Type
  id	UUID
  user	FK to directus_users table

• orders_products

  Column	Type
  id	UUID
  order_id	FK to orders table
  product_id	FK to products table

I have a doubt how I can write a syntax to compute the field like this sql query:

SELECT
        SUM(p.price)
FROM
    orders AS o
INNER JOIN orders_products_1 AS op
    ON (o.id = op.orders_id)
INNER JOIN products AS p
    ON (op.products_id = p.id)

My goal is, when I add the product should calculate the total of order.

[duydvu]

Hi @FelipeBarrosCruz

I'm afraid it's not possible for now because ASUM can only handle second-level relational fields while your case is third-level. Fortunately, it's not hard to solve so if I have time, I will look into it.

[FelipeBarrosCruz]

Hi @FelipeBarrosCruz

I'm afraid it's not possible for now because ASUM can only handle second-level relational fields while your case is third-level. Fortunately, it's not hard to solve so if I have time, I will look into it.

Hi @duydvu , thanks for replying.
Do you have some advice for me?
I want to help and contribute to your lib.

If you can explain what I need to do I'll code it.

Thank you again.

[duydvu]

Contributions are appreciated.

You will need to specify which fields need to be queried in utils.ts.

Currently, no fields are specified in the query so Directus will return only top-level values (see here). If you know which fields are used in the template (require parsing it), then you can place them into the query and Directus will automatically return their values.

About how to parse the template, I'm using a simple parsing algorithm - find a pair of ( and ) recursively - in src/operations.ts. It has some limitations but can still identify which fields are used: if exp is not an expression (opMatch == false) nor a number literal, then it must be a field name.

Good luck!