SQLQL

curl -X GET \
     -H 'Authorization: Token 6f916ae6-8472-463a-9808-6af19e459541' \
     -d "haute_couture[query]=SELECT * FROM users" \
     localhost:3000/haute_couture | jq

[
  {
    "id": 10,
    "name": "yancya",
    "created_at": "2017-09-18T04:59:38.507022",
    "updated_at": "2017-09-18T04:59:38.507022"
  },
  {
    "id": 11,
    "name": "testuser",
    "created_at": "2017-09-18T05:09:13.531304",
    "updated_at": "2017-09-18T05:09:13.531304"
  }
]

curl -X GET \
     -H 'Authorization: Token 4f3326a4-b900-4624-af58-87e8f363dee6' \
     -d "haute_couture[query]=SELECT * FROM users"  \
     localhost:3000/haute_couture | jq

[
  {
    "id": 10,
    "name": "yancya",
    "created_at": "2017-09-18T04:59:38.507022",
    "updated_at": "2017-09-18T04:59:38.507022"
  },
  {
    "id": 11,
    "name": "testuser",
    "created_at": "2017-09-18T05:09:13.531304",
    "updated_at": "2017-09-18T05:09:13.531304"
  },
  {
    "id": 14,
    "name": "secretman",
    "created_at": "2017-09-19T02:26:57.195464",
    "updated_at": "2017-09-19T02:26:57.195464"
  }
]

curl -X GET \
     -H 'Authorization: Token 6f916ae6-8472-463a-9808-6af19e459541' \
     -d "haute_couture[query]=WITH comments AS (SELECT comments.id, content, users.name AS user_name, comments.created_at FROM comments JOIN users ON comments.user_id = users.id), likes AS (SELECT COALESCE(users.name, '匿名') AS user_name, likes.created_at, comment_id FROM likes LEFT OUTER JOIN users ON likes.user_id = users.id) SELECT comments.id, content, comments.user_name, JSON_AGG(likes ORDER BY likes.created_at DESC) AS liked_by FROM comments JOIN likes ON likes.comment_id = comments.id GROUP BY 1, 2, 3" \
     localhost:3000/haute_couture | jq

[
  {
    "id": 8,
    "content": "hoge",
    "user_name": "yancya",
    "liked_by": [
      {
        "user_name": "匿名",
        "created_at": "2017-09-20T05:10:20.676045",
        "comment_id": 8
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:34.871388",
        "comment_id": 8
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:29.185687",
        "comment_id": 8
      }
    ]
  },
  {
    "id": 9,
    "content": "fuga",
    "user_name": "yancya",
    "liked_by": [
      {
        "user_name": "匿名",
        "created_at": "2017-09-20T05:10:23.789931",
        "comment_id": 9
      },
      {
        "user_name": "testuser",
        "created_at": "2017-09-18T05:23:01.764913",
        "comment_id": 9
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:32.062099",
        "comment_id": 9
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:26.740116",
        "comment_id": 9
      }
    ]
  },
  {
    "id": 10,
    "content": "piyo",
    "user_name": "yancya",
    "liked_by": [
      {
        "user_name": "testuser",
        "created_at": "2017-09-18T05:23:07.392301",
        "comment_id": 10
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:36.449477",
        "comment_id": 10
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:33.455075",
        "comment_id": 10
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:23.85745",
        "comment_id": 10
      }
    ]
  },
  {
    "id": 11,
    "content": "it is secret",
    "user_name": "yancya",
    "liked_by": [
      {
        "user_name": "testuser",
        "created_at": "2017-09-18T05:23:10.6083",
        "comment_id": 11
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:04:19.505836",
        "comment_id": 11
      }
    ]
  },
  {
    "id": 13,
    "content": "it is not secret",
    "user_name": "testuser",
    "liked_by": [
      {
        "user_name": "testuser",
        "created_at": "2017-09-18T05:23:15.535289",
        "comment_id": 13
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:15:50.857694",
        "comment_id": 13
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:13:34.048887",
        "comment_id": 13
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:11:50.945295",
        "comment_id": 13
      },
      {
        "user_name": "yancya",
        "created_at": "2017-09-18T05:11:48.028928",
        "comment_id": 13
      }
    ]
  }
]

module HauteCouture
  def self.find_by_sql(query:, user:)
    ActiveRecord::Base.connection.execute(<<~SQL).first['result'] || '[]'
        WITH users AS (#{user.for_haute_couture_sql})
           , comments AS (#{Comment.for_haute_couture(user).to_sql})
           , t AS (#{query})
      SELECT JSON_AGG(t) AS result FROM t
    SQL
  end
end

def for_haute_couture_sql
  base = User.select(:id, :name, :created_at, :updated_at)
  base.where(id: id).or(base.where(privacy: false)).to_sql
end

scope :for_haute_couture, -> (user) {
  base = Comment.select(:id, :content, :user_id, :created_at, :updated_at)
  base.where(privacy: false).or(base.where(user: user))
}

     WITH RECURSIVE r AS (
   SELECT 1 AS n
UNION ALL
   SELECT n + 1 AS n
     FROM r)
   seleCT * FROM r;

psql(34605,0x7fff9c0ef3c0) malloc: *** mach_vm_map(size=18446744056529682432) failed (error code=3)
*** error: can't allocate region
*** set a breakpoint in malloc_error_break to debug

pg_aggregate                     pg_locks                         pg_stat_user_functions
pg_am                            pg_matviews                      pg_stat_user_indexes
pg_amop                          pg_namespace                     pg_stat_user_tables
pg_amproc                        pg_opclass                       pg_stat_wal_receiver
pg_attrdef                       pg_operator                      pg_stat_xact_all_tables
pg_attribute                     pg_opfamily                      pg_stat_xact_sys_tables
pg_auth_members                  pg_pltemplate                    pg_stat_xact_user_functions
pg_authid                        pg_policies                      pg_stat_xact_user_tables
pg_available_extension_versions  pg_policy                        pg_statio_all_indexes
pg_available_extensions          pg_prepared_statements           pg_statio_all_sequences
pg_cast                          pg_prepared_xacts                pg_statio_all_tables
pg_catalog.                      pg_proc                          pg_statio_sys_indexes
pg_class                         pg_range                         pg_statio_sys_sequences
pg_collation                     pg_replication_origin            pg_statio_sys_tables
pg_config                        pg_replication_origin_status     pg_statio_user_indexes
pg_constraint                    pg_replication_slots             pg_statio_user_sequences
pg_conversion                    pg_rewrite                       pg_statio_user_tables
pg_cursors                       pg_roles                         pg_statistic
pg_database                      pg_rules                         pg_stats
pg_db_role_setting               pg_seclabel                      pg_tables
pg_default_acl                   pg_seclabels                     pg_tablespace
pg_depend                        pg_settings                      pg_temp_1.
pg_description                   pg_shadow                        pg_timezone_abbrevs
pg_enum                          pg_shdepend                      pg_timezone_names
pg_event_trigger                 pg_shdescription                 pg_toast.
pg_extension                     pg_shseclabel                    pg_toast_temp_1.
pg_file_settings                 pg_stat_activity                 pg_transform
pg_foreign_data_wrapper          pg_stat_all_indexes              pg_trigger
pg_foreign_server                pg_stat_all_tables               pg_ts_config
pg_foreign_table                 pg_stat_archiver                 pg_ts_config_map
pg_group                         pg_stat_bgwriter                 pg_ts_dict
pg_index                         pg_stat_database                 pg_ts_parser
pg_indexes                       pg_stat_database_conflicts       pg_ts_template
pg_inherits                      pg_stat_progress_vacuum          pg_type
pg_init_privs                    pg_stat_replication              pg_user
pg_language                      pg_stat_ssl                      pg_user_mapping
pg_largeobject                   pg_stat_sys_indexes              pg_user_mappings
pg_largeobject_metadata          pg_stat_sys_tables               pg_views