jackc / surus

PostgreSQL extensions for ActiveRecord
MIT License
395 stars 35 forks source link

Include table name in queries to avoid ambiguous column errors. #34

Closed schneidmaster closed 7 years ago

schneidmaster commented 7 years ago

Problem: surus-generated queries can contain ambiguous column errors if more than one table has the same column name.

For example, consider this schema:

User
has_many :books
has_many :favorites

Book
belongs_to :user # book author
has_many :favorites

Favorite
belongs_to :book
belongs_to :user

And the query:

some_user.notebooks.all_json(
  include: {
    favorites: {
      columns: [:id, :user_id]
    },
    user: {
      columns: [:id, :name]
    }
  }
)

Surus will generate a query like:

select array_to_json(coalesce(array_agg(row_to_json(t)), '{}')) from (SELECT books.id, books.name, books.user_id, (select array_to_json(coalesce(array_agg(row_to_json(t)), '{}')) from (SELECT favorites.id, favorites.user_id FROM "favorites" WHERE ("books"."id"="book_id")) t) "favorites", (select row_to_json(t) from (SELECT users.id, users.name FROM "users" WHERE ("id"="user_id")) t) "user" FROM "favorites" WHERE "favorites"."user_id" = 12345) t;

of which the important part is

(SELECT users.id, users.name FROM "users" WHERE ("id"="user_id"))

The user_id column is not scoped by table and it generates an ambiguous column error because there are two potentially matching columns (the user_id on Book and the user_id on Favorite).

This PR simply adds the table name where it is currently omitted to prevent any ambiguity.

jackc commented 7 years ago

Thanks!