missing FROM-clause entry for table "collection__translations"

[floze]

Describe the bug
Querying the translations field on collections yields "missing FROM-clause" in postgres.

To Reproduce
Steps to reproduce the behavior:

1. Query

query collections($options: CollectionListOptions) {
  collections(options: $options) {
    items {
      id
      translations {
        id
      }
    }
  }
}

2. Error: missing FROM-clause entry for table "collection__translations"

Expected behavior
v3.0.0 does return as expected; to us this is a major roadblock for upgrading above it.

Environment (please complete the following information):

• @vendure/core version: v3.0.4
• Nodejs version v20.11.0
• Database (mysql/postgres etc): postgres 15.3

[michaelbromley]

Thanks for the report. I put this in the next patch milestone 👍

[Draykee]

Same problem reported by me for: "facetvalue__translations".
Discord Post: https://discord.com/channels/1100672177260478564/1290704316495171664

The problem is the double underline character.

[michaelbromley]

@floze I cannot reproduce this on a clean install of v3.0.4.

Here's what I did:

1. Created a brand new project using @vendure/create
2. Selected postgres as the DB (running postgres v16 in docker)
3. populated test data
4. Ran the query given above

query collections($options: CollectionListOptions) {
  collections(options: $options) {
    items {
      id
      name
      translations {
        id
      }
    }
  }
}

Result:

{
  "data": {
    "collections": {
      "items": [
        {
          "id": "2",
          "name": "Electronics",
          "translations": [
            {
              "id": "1"
            }
          ]
        },
        {
          "id": "3",
          "name": "Computers",
          "translations": [
            {
              "id": "3"
            }
          ]
        },
  // etc

Please take a look to see what information/environment/config is required to reproduce this from a clean install, and then I'll be able to dig in and fix whatever issue is there.

[floze]

The crux seems to be the options' translated filters. When querying one of the translated fields I run into the FROM-clause error, so perhaps try the following param on the query: {"options":{"filter":{"name":{"eq":"Electronics"}}}}

[michaelbromley]

ah thanks, that's the info the original report was missing. I'll try it with that 👍

[michaelbromley]

Confirmed 👍 I've got a failing e2e test now - all that's left is to fix it :)

[michaelbromley]

Noting down Kevin's example from Discord that gives the same class of error:

private getFacetValues(term: string, facetId: string) {
        return this.dataService
            .query(
                gql`
                    query GetFacetValueList($options: FacetValueListOptions) {
                        facetValues(options: $options) {
                            items {
                                id
                                facetId
                                name
                                code
                                customFields {
                                    childFacetValue {
                                        id
                                    }
                                }
                            }
                            totalItems
                        }
                    }
                `,
                {
                    options: {
                        filter: {
                            facetId: facetId,
                            name: { contains: term },
                        },
                        filterOperator: LogicalOperator.AND,
                    },
                },
            )
            .mapSingle((data: any) => data.facetValues.items);
    }

Note that in this example, there is a custom field defined on FacetValue which is a relation to another FacetValue.

Therefore I strongly suspect this bug is related to the special path we have for "tree entities" - i.e. entities which have relations to the same entity type (see this commit: #2744)

This is why it happens with Collection (which is innately a tree, having a parent and children). But once you make a self-referential relation custom field, you've now also set up that entity to be treated like a tree.