Complex where queries: Integrity constraint violation: 1052 Column "XXX" in where clause is ambiguous

[Jofairden]

Describe the bug

Querying a certain field on a relation of the resulting type of the query and applying a complex where query to filter the relation causes the SQL error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ID' in where clause is ambiguous

Expected behavior/Solution

I feel like selections in the query need to be prefixed, or addSelect should be used (if it isn't already)

Steps to reproduce

1. Query some data and request a field on a relation of the data
2. Have a hasX complex where filter to filter the queried field, e.g. ID or name etc.

Output/Logs

Example error: (see example code below)

``` "debugMessage": "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ID' in where clause is ambiguous (SQL: select count(*) as aggregate from `teams` where exists (select * from `players` inner join `player_team` on `player_team`.`player_id` = `players`.`id` where `teams`.`id` = `player_team`.`team_id` and (`ID` = 120)))", "message": "Internal server error", "extensions": { "category": "internal" }, ```

**Lighthouse Version** `"nuwave/lighthouse": "^4.12",` # Example code: Query: (Requesting teams of players, request players of the teams (paginated) and filter the players relation for an ID) ``` query { teams( hasPlayers: { column: ID value:120 } ) { data { id players { data { id } } } } } ``` Example GraphQL query file: ``` extend type Query @guard(with: ["api"]) { teams( hasPlayers: _ @whereHasConditions(columnsEnum: PlayerColumn) ): [Team!] @paginate } ``` Team type: ``` type Team { "The ID of this team" id: ID! "Players of this team" players: [Player!] @hasMany(type: "paginator") } ``` Player type: ``` type Player { "The ID of this player" id: ID! } ``` Playercolumn example: ``` enum PlayerColumn { ID } ```

[spawnia]

Please add a PR with a failing test case in https://github.com/nuwave/lighthouse/blob/master/tests/Integration/WhereConditions/WhereHasConditionsDirectiveTest.php in order to investigate this further.

By the way (not invalidating your issue), but have you considered using the following query instead:

{
  player(id: 120) {
    teams {
      players { ... }
    }
  }
}

It is generally a good idea to take advantage of the fact that a GraphQL query does indeed form a graph of data and traverse it as such.

[Jofairden]

Please add a PR with a failing test case in https://github.com/nuwave/lighthouse/blob/master/tests/Integration/WhereConditions/WhereHasConditionsDirectiveTest.php in order to investigate this further.

By the way (not invalidating your issue), but have you considered using the following query instead:

{
  player(id: 120) {
    teams {
      players { ... }
    }
  }
}

It is generally a good idea to take advantage of the fact that a GraphQL query does indeed form a graph of data and traverse it as such.

I know you can reverse/change the queries, maybe my example was bad by just showing one ID, but this happens in many cases, for example filtering by a collection of IDs using the IN operator. I'll see if I can add a PR with a test.

[Jofairden]

Come to think of it, I think this issue is relevant: https://github.com/nuwave/lighthouse/issues/1356 If I change my SQL query so one of the IDs is named, e.g. a.id, the issue goes away (At least in my SQL editor)

[spawnia]

Can you check https://github.com/nuwave/lighthouse/pull/1253 for inspiration?

[daniel-de-wit]

I'm new to this project and not sure if I followed the rules correctly.. But I added a failing test ☝️

[spawnia]

Another related issue: https://github.com/nuwave/lighthouse/issues/1214

I feel like all of those could be solved upstream by Laravel itself. If not, we have to think long and hard about how we can produce a robust solution.

[daniel-de-wit]

@spawnia Would you mind taking a look at my updated PR? https://github.com/nuwave/lighthouse/pull/1530

It solves this particular issue without breaking the tests