ambiguous column reference \"id\" when using $joinRelation and $select together

[Aerlin]

I'm using $select to include a custom sql function in my comments query.

// Default $select in before hook.
context.params.query["$select"] = context.params.query["$select"] || [
	'comments.*', 
	raw('comment_hotness(comments.score, comments.created_at) as hotness')
]

Which works well.

A problem I'm having is when I try to filter based on a relationship value, I get the ambiguous column name error.

// Default $joinRelation in before hook.
context.params.query["$joinRelation"] = context.params.query["$joinRelation"] || "[tags]";

A test query.

http://xxx/comments?tags.tag=abc

Error that's returned.

Error in 'comments' service method 'find' Forbidden: select distinct "comments".*, comment_hotness(comments.score, comments.created_at) as hotness, "id", "comments".* from "comments" inner join "comment_tags" as "tags" on "tags"."comment_id" = "comments"."id" where "tags"."tag" = $1 limit $2 - column reference "id" is ambiguous

In feathers-object index.js, adding the table's name to line 240 seems to fix it.

    if (filters.$select) {
      q = q.select(...filters.$select.concat(`${this.Model.tableName}.${this.id}`));
    } // $eager for Objection eager queries

The new sql query.

select distinct "comments".*, comment_hotness(comments.score, comments.created_at) as hotness, "comments"."id", "comments".* from "comments" inner join "comment_tags" as "tags" on "tags"."comment_id" = "comments"."id"

The query then executes as expected.

[dekelev]

You should use $pick instead of $select when using $eager. it behaves different, but ends up with the same results.

[Aerlin]

$pick returns a list of empty objects and doesn't call the custom SQL function in the query. Again, I'm using $select to call a custom function (comment_hotness) in my db.

context.params.query["$pick"] = [
		'comments.*', 
		raw('comment_hotness(comments.score, comments.created_at) as hotness')
	];

SQL query

select distinct "comments".* from "comments" inner join "comment_tags" as "tags" on "tags"."comment_id" = "comments"."id"

Results

{
  "total": 11,
  "limit": 20,
  "skip": 0,
  "data": [
    {},
    {},
    {},
    {},
    {},
    {},
    {},
    {},
    {},
    {},
    {}
  ]
}

[dekelev]

Thanks for sharing this. I'll try to debug this on the weekend.

[dekelev]

Thanks @Aerlin, your suggested bug fix is published in v1.3.1

[Aerlin]

Thank you!