dockyard/postgres_ext-serializers — pull request #23 by mcm-ham (2014-12-12 08:41:27+00:00)
Title: Generate sql fixes

# Fixes issues #10, #19, #20, #22 and partially fixes #18

## Issue #20
Issue was fixed by generating CTE for root relation first (instead of later) and then using that to filter child ids table i.e.
```sql
WITH tags_attributes_filter AS
(
SELECT "tags"."id",
"tags"."name",
"tags"."note_id"
FROM "tags"
),
...
WITH notes_ids AS
(
SELECT "notes"."id"
FROM "notes"
WHERE "notes"."id" IN ( SELECT "tags_attributes_filter"."note_id" FROM "tags_attributes_filter")
),
```
Instead of
```sql
WITH notes_ids AS
(
SELECT "notes"."id"
FROM "notes"
WHERE "notes"."note_id" IN ( SELECT "tags_ids"."id" FROM "tags_ids")
),
...
WITH tags_attributes_filter AS
(
SELECT "tags"."id",
"tags"."name",
"tags"."note_id"
FROM "tags"
),
```
## Issue #18
Issue was partially fixed by adding foreign key name to CTE name to make it unique (really needs foreign table name added as well in case another table references the first with the same foreign key name) and then combining them together with `UNION` before passing to JSON function i.e.
```sql
offers_created_by_id_attributes_filter AS
(
SELECT "offers"."id"
FROM "offers"
WHERE "offers"."created_by_id" IN (SELECT "users_ids"."id" FROM "users_ids")
),
offers_reviewed_by_id_attributes_filter AS
(
SELECT "offers"."id"
FROM "offers"
WHERE "offers"."reviewed_by_id" IN (SELECT "users_ids"."id" FROM "users_ids")
),
offers_as_json_array AS
(
SELECT Array_to_json(Array_agg(Row_to_json(tbl))) AS offers, 1 AS match
FROM (
SELECT * FROM "offers_created_by_id_attributes_filter"
UNION
SELECT * FROM "offers_reviewed_by_id_attributes_filter"
) AS tbl
)
```
Instead of
```sql
offers_attributes_filter AS
(
SELECT "offers"."id"
FROM "offers"
WHERE "offers"."created_by_id" IN (SELECT "users_ids"."id" FROM "users_ids")
),
offers_attributes_filter AS
(
SELECT "offers"."id"
FROM "offers"
WHERE "offers"."reviewed_by_id" IN (SELECT "users_ids"."id" FROM "users_ids")
),
offers_as_json_array AS
(
SELECT Array_to_json(Array_agg(Row_to_json(offers_attributes_filter))) AS offers, 1 AS match
FROM "offers_attributes_filter"
)
```
## Other
* Not sure why relation was duplicated but removing call to duplicate fixes issue #19
* Added note to readme mentioning timestamp formats generated by Postgres 9.2 & 9.3 as it's not supported by many clients since I was getting invalid date errors in client side app with generated json, 9.4 fixes this http://www.postgresql.org/docs/9.4/static/release-9-4.html
* Added `COALESCE` to return [] instead of null to fix #22
* Added check to see if Postgres 9.3 or higher is in use and if so use json_agg instead to implement request #10 but is using a protected method from postgres adapter which isn't the best
* Removed unused `_relation_to_json_array_arel` function and inlined json functions calls which made code more readable and removed a few functions, not sure if there is a reason to wrap each part in an arel node, in checkin a860812
* Added tests for issue #21 but haven't spent time fixing as it was quicker to add workaround in our app for now

## Thanks
After getting this working for our app it has made a big difference to the performance of our app and are grateful for the idea and work put in.