M20 filtered area aggregation and groupBy ambiguous

[ahmtaras]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

I am trying to aggregate and group by filtering from M2O field. But I am getting the below mentioned error.

To Reproduce

const { data } = await this.$sdk.items("records").readMany({
  fields: ["id", "type", "price", "current.name"],
  filter: {
    current: {
      name: {
        _contains: "Ahmet",
      },
    },
  },
  groupBy: ["type"],
  aggregate: {
    sum: "price",
  },
});

Errors Shown

{
  "errors": [{
    "message": "select \"records\".\"type\", sum(\"records\".\"price\") as \"sum->price\" from \"records\" left join \"currents\" as \"czbsb\" on \"records\".\"current\" = \"czbsb\".\"id\" where \"czbsb\".\"name\" like $1 group by \"type\" order by \"records\".\"type\" asc limit $2 - column reference \"type\" is ambiguous",
    "extensions": {
      "code": "INTERNAL_SERVER_ERROR"
    }
  }]
}

What version of Directus are you using?

9.1.2

What version of Node.js are you using?

16.13.0

What database are you using?

PostgreSQL 14.0, compiled by Visual C++ build 1914, 64-bit

What browser are you using?

Chrome

What operating system are you using?

Windows_NT 10.0.22000

How are you deploying Directus?

locally

[azrikahar]

@ahmtaras just to confirm, both records and currents collections have a column named type in them? This should be detailed in the To Reproduce section, as that is the key element of reproducing this 👍

---

Note for possible fix:

Judging from the generated SQL query in the error message, seems like groupBy doesn't have a table prefix, but orderBy has it. Likely due to order by uses getColumn() function to get the prefix:

directus/api/src/utils/apply-query.ts

Line 40 in 76a306e

column: getColumn(knex, collection, column, false) as any,

whereas group by just returns the column (if it's not a function):

directus/api/src/utils/apply-function-to-column-name.ts

Line 20 in 76a306e

return column;

[ahmtaras]

@azrikahar yes, both collections have a column named type. I think the problem is name conflict. But there must be another way. I tried alias but same problem with it.

Parent Collection Currents

id
name
type -> supply || customer || special
records -> O2M

Child Collection Records

id
price
type -> money_in || money_out || sales_invoice || purchase_invoice
current -> M2O