Realworldを作るために対応するクエリ一覧

realworld-select-statements-pickup.sql

-- 対応するクエリ一覧

-- select句(優先度: 3)
--- 全カラムのselect
select * from  `articles` where `id` = 1\G

--- カラムを指定するselect
select `article_id` from `articles` inner join `article_tag` on `articles`.`id` = `article_tag`.`article_id` where `article_tag`.`tag_id` = 2 order by `created_at` desc\G

--- 集約関数のselect(selectRaw?)
select max(`batch`) as aggregate from `migrations`\G
select count(*) as aggregate from `users` where `username` = 'test12345' and `id` <> 1\G

--- サブクエリのselect
select `articles`.*, (select count(*) from `users` inner join `favorites` on `users`.`id` = `favorites`.`user_id` where `articles`.`id` = `favorites`.`article_id`) as `favorited_count` from `articles` order by `created_at` desc limit 20 offset 0\G

-- where句(優先度: 3)
--- 条件一つ(等号、不等号、LIKE)
select * from `users` where `id` = 1 limit 1\G
select * from `articles` where `slug` = 'ut-magni-consequatur-facere-dolorum-natus' or `slug` LIKE 'ut-magni-consequatur-facere-dolorum-natus-%'\G

--- nullとの比較(引数がnullでoperatorが=のときはIS NULLにする?)
select count(*) as aggregate from `articles` where `user_id` is null\G

--- 条件一つ(サブクエリを引数に取るもの、IN句はリストでも出来るようにする?)
select * from  `favorites` where `user_id` = 2 and `article_id` in (1)\G

--- 複数の条件
select * from `users` where `username` = 'test12345' and `email` = 'test12345@test.com' and `bio` = 'hello' and `image` = 'http://test.com/test.jpg' limit 1\G

-- join句(優先度: 2)
--- inner join
select `article_id` from `articles` inner join `article_tag` on `articles`.`id` = `article_tag`.`article_id` where `article_tag`.`tag_id` = 1 order by `created_at` desc\G

-- order by句(優先度: 1.5)
--- カラム一つ
select * from `comments` where `comments`.`article_id` = 1 and `comments`.`article_id` is not null order by `created_at` desc\G

-- limit句(優先度: 1)
select * from `comments` where `id` = '1' limit 1\G

-- TODO:
-- GROUP BY句(単一カラム、複数カラム)
-- HAVING句
-- OFFSET句(ページネーションで使用する?)
-- ORDER BY句の複数カラム
-- JOIN句(left outer join, right outer join)