database: always include table name in SELECT
When JOINs are performed on the auto-generated SELECT query,
multiple tables may have the same field names. For instance, in
lists.sr.ht the "list" and "subscription" tables both have an "id"
column. When performing a "mailingLists" GraphQL query this results
in this error:
pq: column reference "id" is ambiguous
The old query looks like this:
SELECT "description", "name", "id", "owner_id", "updated", COALESCE(
access.permissions,
CASE WHEN list.owner_id = $1
THEN $2
ELSE CASE WHEN sub.id IS NOT NULL
THEN list.subscriber_permissions
ELSE null END
END,
list.nonsubscriber_permissions | list.account_permissions), access.id AS access_id, sub.id AS subscription_id FROM list LEFT JOIN access ON
access.list_id = list.id AND
access.user_id = $3 LEFT JOIN subscription sub ON
sub.list_id = list.id AND
sub.user_id = $4 WHERE list.owner_id = $5 ORDER BY "updated" DESC LIMIT 26
The new query looks like this:
SELECT "description", "name", "list"."id", "list"."owner_id", "list"."updated", COALESCE(
access.permissions,
CASE WHEN list.owner_id = $1
THEN $2
ELSE CASE WHEN sub.id IS NOT NULL
THEN list.subscriber_permissions
ELSE null END
END,
list.nonsubscriber_permissions | list.account_permissions), access.id AS access_id, sub.id AS subscription_id FROM list LEFT JOIN access ON
access.list_id = list.id AND
access.user_id = $3 LEFT JOIN subscription sub ON
sub.list_id = list.id AND
sub.user_id = $4 WHERE list.owner_id = $5 ORDER BY "updated" DESC LIMIT 26