Перейти к содержанию

Select

В этой главе мы подробно разберём чтение данных. Мы уже знаем, что форму результата selectSch задаёт тип: какие поля читать, какие связанные таблицы подтягивать и вложены ли они списком или одной строкой. Здесь мы посмотрим, какие формы результата бывают, а потом перейдём к описанию запроса: условиям, переходам по связям, сортировке, ограничению числа строк, агрегатам и, наконец, к запасному выходу на случай, когда возможностей pg-schema не хватает.

Данные для примеров

Примеры этой главы лежат в tutorial/app/app/Tut/Select.hs. Перед ними программа пересоздаёт схему и заполняет её небольшим набором данных: четыре пользователя, у троих из которых есть начальник, четыре проекта в разных статусах и шесть задач. Как устроено заполнение, можно посмотреть здесь:

Заполнение базы для примеров главы 
type UserIn = "id" := Int64 :. "name" := Text :. "email" := Maybe Text
  :. "managerId" := Maybe Int64

type ProjectIn = "id" := Int64 :. "ownerId" := Int64 :. "title" := Text
  :. "status" := ProjectStatus :. "tags" := PgArr Text

type TaskIn = "projectId" := Int64 :. "seq" := Int32 :. "title" := Text
  :. "priority" := Int32 :. "payload" := Maybe Payload :. "watchers" := PgArr Int64

_ <- insertSch_ (MyAnn "users") conn $ rowsOf @UserIn
  [ 1 :. "Alice" :. Just "alice@example.com" :. Nothing
  , 2 :. "Bob"   :. Just "bob@example.com"   :. Just 1
  , 3 :. "Carol" :. Nothing                  :. Just 1
  , 4 :. "Dave"  :. Just "dave@example.com"  :. Just 2 ]
_ <- insertSch_ (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. 1 :. "Website" :. Project_status_active   :. pgArr' ["web", "frontend"]
  , 2 :. 2 :. "API"     :. Project_status_active   :. pgArr' ["backend"]
  , 3 :. 3 :. "Docs"    :. Project_status_draft    :. pgArr' []
  , 4 :. 4 :. "Legacy"  :. Project_status_archived :. pgArr' ["backend"] ]
_ <- insertSch_ (MyAnn "tasks") conn $ rowsOf @TaskIn
  [ 1 :. 1 :. "Design"    :. 10  :. Just (Payload 3) :. pgArr' [2, 3]
  , 1 :. 2 :. "Frontend"  :. 30  :. Just (Payload 8) :. pgArr' [3]
  , 1 :. 3 :. "Polish"    :. 100 :. Nothing          :. pgArr' []
  , 2 :. 1 :. "Schema"    :. 20  :. Just (Payload 5) :. pgArr' [1]
  , 2 :. 2 :. "Endpoints" :. 40  :. Nothing          :. pgArr' [1, 4]
  , 3 :. 1 :. "Outline"   :. 50  :. Nothing          :. pgArr' [] ]

Все примеры выполняются одной и той же вспомогательной функцией run. Она печатает строки результата по одной, затем параметры запроса и, отдельно, отформатированный SQL:

-- | Run a query, print the rows one per line and the query parameters,
-- then the formatted SQL.
run :: Show r => String -> IO ([r], (Text, [SomeToField])) -> IO ()
run name act = do
  sql <- section name $ do
    (rows, (sql, params)) <- act
    mapM_ print rows
    putStrLn $ "params: " <> show params
    pure sql
  sqlSection name [sql]

Форма результата

Начнём с запроса без всяких условий. Прочитаем проекты вместе с их владельцами и задачами:

type Owner = "name" := Text

data Task = Task { seq :: Int32, title :: Text, priority :: Int32 }
  deriving (Show, Generic)

data Project = Project
  { title              :: Text
  , status             :: ProjectStatus
  , projectOwner       :: Owner
  , tasksProjectIdFkey :: [Task]
  } deriving (Show, Generic)

selectSch (MyAnn "projects") @Project conn qpEmpty

Project {title = "Website", status = Project_status_active, projectOwner = "name" =: "Alice", tasksProjectIdFkey = [Task {seq = 1, title = "Design", priority = 10},Task {seq = 2, title = "Frontend", priority = 30},Task {seq = 3, title = "Polish", priority = 100}]}
Project {title = "API", status = Project_status_active, projectOwner = "name" =: "Bob", tasksProjectIdFkey = [Task {seq = 1, title = "Schema", priority = 20},Task {seq = 2, title = "Endpoints", priority = 40}]}
Project {title = "Docs", status = Project_status_draft, projectOwner = "name" =: "Carol", tasksProjectIdFkey = [Task {seq = 1, title = "Outline", priority = 50}]}
Project {title = "Legacy", status = Project_status_archived, projectOwner = "name" =: "Dave", tasksProjectIdFkey = []}
params: []
SQL, отформатированный для чтения 
select t0.title "title",
       t0.status "status",
       jsonb_build_object('name',t1.name) "projectOwner",
       array_to_json(array(
           select jsonb_build_object('seq',t2.seq,'title',t2.title,'priority',t2.priority)
           from tut.tasks t2
           where t2.project_id=t0.id)) "tasksProjectIdFkey"
from tut.projects t0
join tut.users t1 on t0.owner_id=t1.id;

В типе Project два поля описывают связи. tasksProjectIdFkey — это, как и в главе «Быстрый старт», внешний ключ из tasks в projects: для проекта он ведёт к дочерним строкам, поэтому поле — список. projectOwner ведёт в обратную сторону: ключ projects_owner_id_fkey смотрит из projects в users, так что у проекта ровно один владелец, и поле — одна строка типа Owner. Имя projectOwner мы придумали сами. Ренеймер MyRenamer из главы «Рабочая модель pg-schema» переводит его в настоящее имя внешнего ключа:

data MyRenamer :: Renamer

type family MyRenamerImpl (s :: Symbol) :: Symbol where
  MyRenamerImpl "projectOwner" = "projects_owner_id_fkey"
  MyRenamerImpl "manager" = "users_manager_id_fkey"
  MyRenamerImpl "subordinates" = "users_manager_id_fkey"
  MyRenamerImpl s = CamelToSnake s

type instance ApplyRenamer MyRenamer s = MyRenamerImpl s

Обратите внимание, что Owner — не record, а PgTag: type Owner = "name" := Text. Две нотации можно свободно смешивать. Поле Generic-рекорда может быть строкой PgTag, её списком или Maybe, а рекорд, наоборот, может быть частью цепочки :., как в главе «Рабочая модель pg-schema». Для маленькой строки из одного-двух полей отдельный record заводить не обязательно.

По SQL видно, как pg-schema строит такой запрос. Родительская строка присоединяется через join, а дочерние строки собираются подзапросом в JSON-массив, так что весь результат читается одним запросом.

Если внешний ключ допускает NULL, родителя может и не быть. Тогда поле должно иметь тип Maybe, а вместо join используется left outer join. Хороший пример — колонка users.manager_id: она ссылается на ту же таблицу users. По одному и тому же внешнему ключу можно пройти в обе стороны: к начальнику (одна строка или Nothing) и к подчинённым (список). Направление pg-schema определяет по типу поля, а ренеймер позволяет дать обоим полям понятные имена manager и subordinates:

data Person = Person
  { name         :: Text
  , manager      :: Maybe Owner
  , subordinates :: [Owner]
  } deriving (Show, Generic)

selectSch (MyAnn "users") @Person conn qpEmpty

Person {name = "Alice", manager = Nothing, subordinates = ["name" =: "Bob","name" =: "Carol"]}
Person {name = "Bob", manager = Just ("name" =: "Alice"), subordinates = ["name" =: "Dave"]}
Person {name = "Carol", manager = Just ("name" =: "Alice"), subordinates = []}
Person {name = "Dave", manager = Just ("name" =: "Bob"), subordinates = []}
params: []
SQL, отформатированный для чтения 
select t0.name "name",
       case when t1.name is null then null else jsonb_build_object('name',t1.name) end "manager",
       array_to_json(array(
           select jsonb_build_object('name',t2.name)
           from tut.users t2
           where t2.manager_id=t0.id)) "subordinates"
from tut.users t0
left outer join tut.users t1 on t0.manager_id=t1.id;

Условия

Условия задаются функцией qWhere внутри qRoot. Для сравнения с параметром есть операторы =?, <?, >?, <=? и >=?, для сравнения строк по образцу — ~=? (like) и ~~? (ilike). Условия соединяются операторами &&& и |||, а pnot их отрицает. &&& связывает сильнее, чем |||, так же как and и or в SQL, поэтому лишние скобки не нужны:

selectSch (MyAnn "projects") @("title" := Text :. "status" := ProjectStatus) conn $ qRoot do
  qWhere $ "status" =? Project_status_active &&& "title" ~~? ("%site%" :: Text)
    ||| pnot ("status" =? Project_status_active) &&& pinArr "id" [3, 4 :: Int64]

"title" =: "Website" :. "status" =: Project_status_active
"title" =: "Docs" :. "status" =: Project_status_draft
"title" =: "Legacy" :. "status" =: Project_status_archived
params: [SomeToField Project_status_active,SomeToField "%site%",SomeToField Project_status_active,SomeToField (PGArray {fromPGArray = [3,4]})]
SQL, отформатированный для чтения 
select t0.title "title",
       t0.status "status"
from tut.projects t0
where (t0.status = ? and t0.title ilike ? or not (t0.status = ?) and t0.id = any(?::int8[]));

Здесь мы выбрали активные проекты, в названии которых есть «site», и, кроме того, неактивные проекты с id 3 или 4. pinArr проверяет, что значение входит в список. Список может быть и пустым, и тогда условие просто ложно. Обратите внимание, что колонка id в условии есть, хотя в результате её нет: условия могут ссылаться на любые колонки таблицы.

Отдельного внимания заслуживают колонки, допускающие NULL. Проверить, что значения нет, можно с помощью pnull. Для сравнения со значением, которое само может быть Nothing, есть оператор =??: он превращается в is not distinct from, и Nothing с ним совпадает с NULL. Ещё один способ проверить вхождение в список — pin, он принимает непустой список (NonEmpty):

selectSch (MyAnn "users") @("name" := Text :. "email" := Maybe Text) conn $ qRoot do
  qWhere $ pnull "email" ||| "email" =?? Just ("bob@example.com" :: Text)
    ||| pin "name" ("Dave" :| ["Eve" :: Text])

"name" =: "Bob" :. "email" =: Just "bob@example.com"
"name" =: "Carol" :. "email" =: Nothing
"name" =: "Dave" :. "email" =: Just "dave@example.com"
params: [SomeToField (Just "bob@example.com"),SomeToField (PGArray {fromPGArray = ["Dave","Eve"]})]
SQL, отформатированный для чтения 
select t0.name "name",
       t0.email "email"
from tut.users t0
where ((t0.email is null or t0.email is not distinct from ?) or t0.name = any(?::text[]));

Типы значений в условиях проверяются так же строго, как и поля результата. Если колонка допускает NULL, значение в сравнении тоже должно быть Maybe. Например, "managerId" =? (1 :: Int64) не скомпилируется:

• You have to use Maybe for nullable fields

  Table: NameNS "tut" "users"
  DB Field name: "manager_id"
  DB Field type: NameNS "pg_catalog" "int8"
  Haskell Field type: Int64

Правильно писать "managerId" =? Just (1 :: Int64). Заметьте, что в сообщении стоит имя manager_id: имена в условиях, как и поля результата, проходят через ренеймер, поэтому писать их можно и в camelCase, и так, как они названы в базе.

Ветки результата: qPath

Условия, сортировка и ограничение числа строк задаются не только для корневой таблицы, но и для любой ветки результата. Для этого служит qPath с именем связи: внутри него все указания относятся уже к таблице на другом конце связи. В следующем примере мы берём только активные проекты, сортируем их по названию в обратном порядке, а у каждого проекта оставляем одну задачу — с наибольшим приоритетом среди тех, у которых приоритет меньше 50 (ascf и descf задают сортировку по полю по возрастанию и по убыванию):

selectSch (MyAnn "projects") @Project conn $ qRoot do
  qWhere $ "status" =? Project_status_active
  qOrderBy [descf "title"]
  qPath "tasksProjectIdFkey" do
    qWhere $ "priority" <? (50 :: Int32)
    qOrderBy [descf "priority"]
    qLimit 1

Project {title = "Website", status = Project_status_active, projectOwner = "name" =: "Alice", tasksProjectIdFkey = [Task {seq = 2, title = "Frontend", priority = 30}]}
Project {title = "API", status = Project_status_active, projectOwner = "name" =: "Bob", tasksProjectIdFkey = [Task {seq = 2, title = "Endpoints", priority = 40}]}
params: [SomeToField 50,SomeToField Project_status_active]
SQL, отформатированный для чтения 
select t0.title "title",
       t0.status "status",
       jsonb_build_object('name',t1.name) "projectOwner",
       array_to_json(array(
           select jsonb_build_object('seq',t2.seq,'title',t2.title,'priority',t2.priority)
           from tut.tasks t2
           where t2.project_id=t0.id and t2.priority < ?
           order by t2.priority Desc nulls last
           limit 1)) "tasksProjectIdFkey"
from tut.projects t0
join tut.users t1 on t0.owner_id=t1.id
where t0.status = ?
order by t0.title Desc nulls last;

Условия на ветку попадают в подзапрос, который собирает задачи, и поэтому влияют только на содержимое списка задач, но не на то, какие проекты попадут в результат. Проект без подходящих задач останется в результате с пустым списком. Так ведут себя дочерние ветки. С веткой-родителем всё иначе: родитель присоединяется через join, поэтому условия, заданные в qPath к нему (например, qPath "projectOwner"), попадают в where основного запроса и отбирают уже строки корня.

Условия ветки привязаны к полю результата

Как мы видели в главе «Быстрый старт», условия, сортировка и ограничения из qPath применяются к полю результата с тем же именем связи. Если такого поля в типе нет, они молча не попадут в запрос. Для ветки-родителя это значит, что вместе с полем из типа пропадёт и отбор строк корня.

Отбор строк по связанным таблицам: pchild и pparent

Иногда нужно наоборот: отобрать сами строки по тому, что есть в связанной таблице. Например, найти проекты, у которых есть задача с приоритетом меньше 20. Для этого служит условие pchild. Оно принимает имя связи вместе со схемой базы, параметры выборки дочерних строк и условие на них. Имя связи, как и везде, проходит через ренеймер, поэтому мы пишем его так же, как в qPath: "tasksProjectIdFkey".

type Tut name = "tut" ->> name

selectSch (MyAnn "projects") @("title" := Text) conn $ qRoot do
  qWhere $ pchild (Tut "tasksProjectIdFkey") defTabParam ("priority" <? (20 :: Int32))

"title" =: "Website"
params: [SomeToField 20]
SQL, отформатированный для чтения 
select t0.title "title"
from tut.projects t0
where exists (
    select 1
    from (
        select *
        from tut.tasks t0q1
        where t0q1.project_id = t0.id) t0q1
    where t0q1.priority < ?);

Имя связи здесь — это тип: pchild, как и selectSch с аннотацией, принимает его видимым аргументом типа. Записать его прямо в вызове как "tut" ->> "tasksProjectIdFkey" не получится: оператор ->> есть и на уровне значений (им собирают имена в обычном коде), и GHC прочитает такое выражение как значение. Синоним типа Tut эту неоднозначность снимает. pchild превращается в exists с подзапросом. Параметры выборки defTabParam означают «все дочерние строки». Их можно ограничить: TabParam содержит сортировку (order) и limit/offset (lo, значение типа LO { limit, offset }; lo1 — это limit 1). Так можно сформулировать условие «среди первых N детей». Найдём проекты, у которых задача с наибольшим значением приоритета имеет приоритет меньше 50:

selectSch (MyAnn "projects") @("title" := Text) conn $ qRoot do
  qWhere $ pchild (Tut "tasksProjectIdFkey")
    defTabParam { order = [descf "priority"], lo = lo1 }
    ("priority" <? (50 :: Int32))

"title" =: "API"
params: [SomeToField 50]
SQL, отформатированный для чтения 
select t0.title "title"
from tut.projects t0
where exists (
    select 1
    from (
        select *
        from tut.tasks t0q1
        where t0q1.project_id = t0.id
        order by t0q1.priority Desc nulls last
        limit 1) t0q1
    where t0q1.priority < ?);

У «Website» первая задача в таком порядке — «Polish» с приоритетом 100, у «Docs» — «Outline» с приоритетом 50, поэтому в результате остался только «API».

pparent работает в обратную сторону: отбирает строки по их родителю. Найдём задачи активных проектов с приоритетом не ниже 30:

selectSch (MyAnn "tasks") @("title" := Text :. "priority" := Int32) conn $ qRoot do
  qWhere $ pparent (Tut "tasksProjectIdFkey") ("status" =? Project_status_active)
    &&& "priority" >=? (30 :: Int32)
  qOrderBy [ascf "priority"]

"title" =: "Frontend" :. "priority" =: 30
"title" =: "Endpoints" :. "priority" =: 40
"title" =: "Polish" :. "priority" =: 100
params: [SomeToField Project_status_active,SomeToField 30]
SQL, отформатированный для чтения 
select t0.title "title",
       t0.priority "priority"
from tut.tasks t0
where exists (
    select 1
    from (
        select *
        from tut.projects t0q1
        where t0q1.id = t0.project_id) t0q1
    where t0q1.status = ?) and t0.priority >= ?
order by t0.priority Asc nulls last;

Разница между pchild/pparent и qPath важна. pchild и pparent решают, какие строки попадут в результат, но сами связанные строки в результат не добавляют. qPath к дочерней ветке, наоборот, не влияет на отбор строк корня, а определяет, что окажется во вложенном списке. С веткой к родителю, как мы видели выше, всё иначе: родитель присоединяется через join, и условия в qPath к нему отбирают строки корня так же, как pparent. Удобно сначала решить, какие строки нужны в корне, а потом отдельно настроить содержимое дочерних веток.

Сортировка, limit, offset и distinct

С qOrderBy мы уже встречались: он принимает список полей, каждое с направлением сортировки (ascf — по возрастанию, descf — по убыванию). qLimit и qOffset ограничивают число строк. Возьмём вторую и третью задачи по убыванию приоритета:

selectSch (MyAnn "tasks") @("title" := Text :. "priority" := Int32) conn $ qRoot do
  qOrderBy [descf "priority"]
  qLimit 2
  qOffset 1

"title" =: "Outline" :. "priority" =: 50
"title" =: "Endpoints" :. "priority" =: 40
params: []
SQL, отформатированный для чтения 
select t0.title "title",
       t0.priority "priority"
from tut.tasks t0
order by t0.priority Desc nulls last
limit 2
offset 1;

qDistinct убирает повторяющиеся строки. Например, так можно узнать, какие статусы проектов встречаются в базе:

selectSch (MyAnn "projects") @("status" := ProjectStatus) conn $ qRoot do
  qDistinct
  qOrderBy [ascf "status"]

"status" =: Project_status_draft
"status" =: Project_status_active
"status" =: Project_status_archived
params: []
SQL, отформатированный для чтения 
select distinct t0.status "status"
from tut.projects t0
order by t0.status Asc nulls last;

Статусы упорядочены так, как значения объявлены в enum ('draft', 'active', 'archived' в tutorial/sql/00-create-schema.sql), а не по алфавиту. qDistinctOn оставляет по одной строке на каждое значение указанных полей, причём какая строка останется, определяет сортировка. Оставим у каждого проекта одну задачу с наименьшим приоритетом:

selectSch (MyAnn "tasks") @("projectId" := Int64 :. "title" := Text :. "priority" := Int32) conn $ qRoot do
  qDistinctOn [ascf "projectId"]
  qOrderBy [ascf "priority"]

"projectId" =: 1 :. ("title" =: "Design" :. "priority" =: 10)
"projectId" =: 2 :. ("title" =: "Schema" :. "priority" =: 20)
"projectId" =: 3 :. ("title" =: "Outline" :. "priority" =: 50)
params: []
SQL, отформатированный для чтения 
select distinct on (t0.project_id) t0.project_id "projectId",
       t0.title "title",
       t0.priority "priority"
from tut.tasks t0
order by t0.project_id Asc nulls last,t0.priority Asc nulls last;

PostgreSQL требует, чтобы поля из distinct on шли в начале order by, и qDistinctOn добавляет их туда сам.

Поскольку родитель присоединяется через join, в distinct on и order by можно использовать и его поля, вперемешку с полями корня. Каждое поле указывается внутри того qPath, к таблице которого оно относится, а порядок полей в запросе совпадает с порядком вызовов. Оставим для каждого сочетания номера задачи и статуса её проекта одну задачу — с наибольшим приоритетом:

selectSch (MyAnn "tasks")
  @( "seq" := Int32 :. "title" := Text :. "priority" := Int32
  :. "tasksProjectIdFkey" := ("title" := Text :. "status" := ProjectStatus) )
  conn $ qRoot do
    qDistinctOn [ascf "seq"]
    qPath "tasksProjectIdFkey" do
      qDistinctOn [ascf "status"]
    qOrderBy [descf "priority"]

"seq" =: 1 :. ("title" =: "Outline" :. ("priority" =: 50 :. "tasksProjectIdFkey" =: ("title" =: "Docs" :. "status" =: Project_status_draft)))
"seq" =: 1 :. ("title" =: "Schema" :. ("priority" =: 20 :. "tasksProjectIdFkey" =: ("title" =: "API" :. "status" =: Project_status_active)))
"seq" =: 2 :. ("title" =: "Endpoints" :. ("priority" =: 40 :. "tasksProjectIdFkey" =: ("title" =: "API" :. "status" =: Project_status_active)))
"seq" =: 3 :. ("title" =: "Polish" :. ("priority" =: 100 :. "tasksProjectIdFkey" =: ("title" =: "Website" :. "status" =: Project_status_active)))
params: []
SQL, отформатированный для чтения 
select distinct on (t0.seq,t1.status) t0.seq "seq",
       t0.title "title",
       t0.priority "priority",
       jsonb_build_object('title',t1.title,'status',t1.status) "tasksProjectIdFkey"
from tut.tasks t0
join tut.projects t1 on t0.project_id=t1.id
order by t0.seq Asc nulls last,t1.status Asc nulls last,t0.priority Desc nulls last;

Обратите внимание: у задачи поле tasksProjectIdFkey — это одна строка её проекта, а у проекта поле с тем же именем — список его задач. Имя связи одно, а смысл зависит от того, от какой таблицы мы идём.

В запросе получились distinct on (t0.seq, t1.status) и order by t0.seq, t1.status, t0.priority desc: поле корня, поле родителя и снова поле корня. Так же можно сортировать и без distinct on, вызывая qOrderBy в корне и внутри qPath к родителю.

В дочерних ветках работают все эти функции, как мы видели на примере qLimit в разделе про qPath. А вот qLimit, qOffset и qDistinct в ветке к родителю не имеют смысла, ведь родитель у строки один, и компилятор их там не пропустит.

Связь таблицы с самой собой

Для внешнего ключа, который ссылается на свою же таблицу, qPath не годится. Направление qPath определяет по связи: ведёт ли она из текущей таблицы или в неё. У связи users_manager_id_fkey верно и то и другое, и по имени нельзя понять, идём мы к начальнику или к подчинённым. (В типе результата такой неоднозначности нет: там направление видно по типу поля — список или одна строка.) Компилятор об этом скажет:

• qPath cannot be used for self-reference relation.
  Use qPathFromHere or qPathToHere.

  Table: NameNS "tut" "users"
  Relation: "users_manager_id_fkey"

Вместо qPath здесь используются qPathFromHere (по ключу из текущей строки, то есть к начальнику) и qPathToHere (к строкам, которые ссылаются на текущую, то есть к подчинённым). Выберем пользователей без начальника или с начальником id = 1, а в списке подчинённых оставим только тех, в чьём имени есть буква «o»:

selectSch (MyAnn "users") @Person conn $ qRoot do
  qWhere $ pnull "managerId" ||| "managerId" =? Just (1 :: Int64)
  qOrderBy [ascf "name"]
  qPathToHere "subordinates" do
    qWhere $ "name" ~~? ("%o%" :: Text)

Person {name = "Alice", manager = Nothing, subordinates = ["name" =: "Bob","name" =: "Carol"]}
Person {name = "Bob", manager = Just ("name" =: "Alice"), subordinates = []}
Person {name = "Carol", manager = Just ("name" =: "Alice"), subordinates = []}
params: [SomeToField "%o%",SomeToField (Just 1)]
SQL, отформатированный для чтения 
select t0.name "name",
       case when t1.name is null then null else jsonb_build_object('name',t1.name) end "manager",
       array_to_json(array(
           select jsonb_build_object('name',t2.name)
           from tut.users t2
           where t2.manager_id=t0.id and t2.name ilike ?)) "subordinates"
from tut.users t0
left outer join tut.users t1 on t0.manager_id=t1.id
where (t0.manager_id is null or t0.manager_id = ?)
order by t0.name Asc nulls last;

Подчинённый Боба Дейв в списке не оказался: в его имени нет буквы «o».

Имена manager и subordinates здесь — лишь два имени одного внешнего ключа, и направление задаёт не имя, а функция. Если бы мы написали qPathFromHere "manager" с тем же условием, оно относилось бы к начальнику и, как у любой ветки-родителя, отбирало бы самих пользователей: остались бы только те, у кого в имени начальника есть «o». На наших данных таких нет, и результат был бы пустым.

Агрегаты

Агрегатные функции тоже задаются типом результата. Поле типа Aggr' ACount Int64 превращается в count(*), а Aggr' AMax Int32 — в max по колонке с именем поля. Остальные поля без агрегатов становятся ключами группировки, и group by добавляется автоматически:

selectSch (MyAnn "tasks")
  @("projectId" := Int64 :. "cnt" := Aggr' ACount Int64 :. "priority" := Aggr' AMax Int32)
  conn $ qRoot do
    qOrderBy [ascf "projectId"]

"projectId" =: 1 :. ("cnt" =: Aggr' {unAggr' = 3} :. "priority" =: Aggr' {unAggr' = 100})
"projectId" =: 2 :. ("cnt" =: Aggr' {unAggr' = 2} :. "priority" =: Aggr' {unAggr' = 40})
"projectId" =: 3 :. ("cnt" =: Aggr' {unAggr' = 1} :. "priority" =: Aggr' {unAggr' = 50})
params: []
SQL, отформатированный для чтения 
select t0.project_id "projectId",
       count(*) "cnt",
       max(t0.priority) "priority"
from tut.tasks t0
group by project_id
order by t0.project_id Asc nulls last;

Aggr' предназначен для запросов с группировкой: в каждой группе есть хотя бы одна строка, поэтому max по обязательной колонке не может вернуть NULL, и Maybe не нужен. Если же группировки нет, а таблица пуста, max вернёт NULL. Для таких запросов служит Aggr: у всех функций, кроме count, он требует Maybe. Компилятор не запрещает использовать Aggr' и без группировки, но тогда запрос к пустой таблице упадёт при чтении результата, поэтому без группировки лучше всегда брать Aggr:

selectSch (MyAnn "tasks")
  @("cnt" := Aggr ACount Int64 :. "priority" := Aggr AMax (Maybe Int32))
  conn qpEmpty

"cnt" =: Aggr {unAggr = 6} :. "priority" =: Aggr {unAggr = Just 100}
params: []
SQL, отформатированный для чтения 
select count(*) "cnt",
       max(t0.priority) "priority"
from tut.tasks t0;

Поддерживаются функции count, min, max, sum и avg.

jsonb и массивы

Колонку типа jsonb можно читать в любой тип с инстансами FromJSON и ToJSON из aeson. Там, где значения разбирает или передаёт сам postgresql-simple, нужны ещё FromField (чтение в корне запроса) и ToField (плоские операции записи, например insertSch, которым заполнены данные этой главы). Проще всего определить их через fromJSONField и toJSONField. Колонка tasks.payload хранит оценку задачи:

newtype Payload = Payload { estimate :: Int } deriving (Show, Generic)

instance FromJSON Payload
instance ToJSON Payload

instance FromField Payload where fromField = fromJSONField
instance ToField Payload where toField = toJSONField

Массивы читаются в тип PgArr, который мы уже встречали в главе «Изменение данных в одной таблице». Его элементы имеют тип Maybe, потому что в массиве PostgreSQL может оказаться NULL, поэтому в выводе наблюдатели выглядят как [Just 2, Just 3]:

selectSch (MyAnn "tasks")
  @("title" := Text :. "payload" := Maybe Payload :. "watchers" := PgArr Int64)
  conn $ qRoot do
    qWhere $ pnot (pnull "payload")

"title" =: "Design" :. ("payload" =: Just (Payload {estimate = 3}) :. "watchers" =: PgArr {unPgArr = [Just 2,Just 3]})
"title" =: "Frontend" :. ("payload" =: Just (Payload {estimate = 8}) :. "watchers" =: PgArr {unPgArr = [Just 3]})
"title" =: "Schema" :. ("payload" =: Just (Payload {estimate = 5}) :. "watchers" =: PgArr {unPgArr = [Just 1]})
params: []
SQL, отформатированный для чтения 
select t0.title "title",
       t0.payload "payload",
       t0.watchers "watchers"
from tut.tasks t0
where not (t0.payload is null);

Запасной выход: UnsafeCol, pUnsafeCond и UnsafeOrd

На pg-schema можно записать многое, но не всё. Обидно, когда большой запрос к двум десяткам таблиц не ложится в схему из-за того, что в одном условии нужно сравнить сумму двух полей. Для таких случаев есть «небезопасные» средства: они позволяют вставить в запрос кусок SQL, а всё остальное по-прежнему проверяет компилятор. UnsafeCol описывает поле результата как произвольное SQL-выражение: в типе указываются колонки, которые подставляются вместо знаков ?, само выражение и тип результата. Знаки ? здесь означают именно колонки, а не параметры запроса. Имена колонок проверяются, а вот выражение и соответствие его результата типу (Int32) — нет: ошибка в них обнаружится только при выполнении. pUnsafeCond задаёт условие как произвольный текст, а UnsafeOrd — выражение для сортировки вместе с её направлением. Оба строят текст в небольшой монаде CondMonad, где qual @"поле" возвращает имя колонки текущей таблицы с правильным псевдонимом. Имя поля qual не проверяет, поэтому опечатка в нём тоже обнаружится только при выполнении:

selectSch (MyAnn "tasks")
  @("title" := Text :. "titleLength" := UnsafeCol '["title"] "length(?)" Int32)
  conn $ qRoot do
    qWhere $ pUnsafeCond do
      sq <- qual @"seq"
      priority <- qual @"priority"
      tell [SomeToField (40 :: Int32)]
      pure $ sq <> " + " <> priority <> " > ?"
    qOrderBy
      [ UnsafeOrd do
          title <- qual @"title"
          pure ("length(" <> title <> ")", Desc) ]

"title" =: "Endpoints" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 9
"title" =: "Outline" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 7
"title" =: "Polish" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 6
params: [SomeToField 40]
SQL, отформатированный для чтения 
select t0.title "title",
       length(t0.title) "titleLength"
from tut.tasks t0
where t0.seq + t0.priority > ?
order by length(t0.title) Desc nulls last;

Здесь мы выбрали задачи, у которых сумма номера и приоритета больше 40, для каждой вычислили длину названия и отсортировали задачи по этой длине. За правильность таких выражений отвечает уже программист, а не компилятор, поэтому пользоваться ими стоит только тогда, когда обычных средств действительно не хватает. Значение из программы, как число 40 в нашем условии, лучше передавать параметром, а не вклеивать в текст: tell добавляет его в список параметров, а в тексте на его месте ставится ?. Так в запрос не проберётся SQL-инъекция.

Резюме

  • Тип результата задаёт дочерние списки ([T]), родителя (T) и необязательного родителя (Maybe T).
  • Условия собираются из операторов =?, ~~?, &&&, |||, pnot, pin, pinArr, pnull, =??.
  • pchild и pparent отбирают строки корня, qPath к дочерней ветке задаёт её содержимое, а qPath к родителю тоже отбирает строки корня.
  • Сортировка задаётся на любом уровне, а limit, offset и distinct — в корне и в дочерних ветках.
  • Для связи таблицы с самой собой используются qPathFromHere и qPathToHere.
  • Агрегаты задаются типами Aggr и Aggr'.

Что дальше

Мы научились читать данные в любой нужной форме. Записывать данные сразу в несколько связанных таблиц — проекты вместе с задачами и событиями — можно так же одной операцией, и об этом рассказывает глава «Изменение связанных данных». Полный список правил для типов результата собран в справочнике.