CakePHP3のよく使うfindメソッドまとめ。
目次
・ ID指定取得(パターン①: get)
・ ID指定取得(パターン②: where)
・ カラム指定取得(id, email)
・ 〜以上(>=) / 〜以下(<=)
・ 件数カウント(COUNT)
・ 並び替え降順(ORDER BY)
・ 並び替えランダム(ORDER BY)
・ LIKE検索(あいまい検索)
・ OR検索
・ NOT検索
● 使用例のテーブル定義(usersテーブル)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | // ユーザーテーブル // status 0:仮登録 1:本登録 2:退会 CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `email` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `status` char(1) DEFAULT 0, `created` DATETIME DEFAULT NULL, `modified` DATETIME DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; // テストデータ INSERT INTO `users` (`id`, `email`, `password`, `status`, `created`, `modified`) VALUES (NULL, '001@cakephp3.com', '001', '0', NULL, NULL), (NULL, '002@cakephp3.com', '002', '1', NULL, NULL); |
◯ ID指定取得(パターン①: get)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $id = '1'; $user = $this->Users->get($id); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE Users.id = 1 LIMIT 1 |
※ get()は$idに該当するデータが存在しなかった場合は「Record not found エラー」が発生するので要注意！
◯ ID指定取得(パターン②: where)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $id = '1'; $user = $this->Users->find()->where(['id' => $id])->first(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE id = 1 LIMIT 1 |
◯ カラム指定取得(id, email)
1 2 3 4 5 6 7 8 9 10 11 12 13 | $id = '1'; $user = $this->Users->find()->select(['id', 'email'])->where(['id' => $id])->first(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email` FROM users Users WHERE id = 1 LIMIT 1 |
◯ 〜以上(>=) ※以下の場合、「<=」にする
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $num = 2; $user = $this->Users->find()->where(['id >=' => $num])->toArray(); // id: 2以上取得 // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE id >= 2 |
◯ 件数カウント(COUNT)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $status = '1'; $count = $this->Users->find()->where(['status' => $status])->count(); var_dump($count); // 1 // 生成SQL SELECT ( COUNT(*) ) AS `count` FROM users Users WHERE status = '1' |
◯ 並び替え降順(ORDER BY) ※配列で取得
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $user = $this->Users->find()->order(['id' => 'DESC'])->toArray(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users ORDER BY id DESC |
◯ 並び替えランダム(ORDER BY) ※配列で取得かつ最大取得件数5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $user = $this->Users->find()->order('rand()')->limit('5')->toArray(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users ORDER BY rand() LIMIT 5 |
◯ LIKE検索(あいまい検索) ※前方一致
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $search = '001'; $user = $this->Users->find()->where(['email LIKE' => $search.'%'])->toArray(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE email like '001%' |
◯ OR検索
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | // ID: 1または2 $user = $this->Users->find()->where(['OR' => [['id' => '1'], ['id' => '2']]])->toArray(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE ( id = 1 OR id = 2 ) |
◯ NOT検索
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | // ID: 1でない $user = $this->Users->find()->where(['NOT' => ['id' => '1']])->toArray(); // 生成SQL SELECT Users.id AS `Users__id`, Users.email AS `Users__email`, Users.password AS `Users__password`, Users.status AS `Users__status`, Users.created AS `Users__created`, Users.modified AS `Users__modified` FROM users Users WHERE NOT (id = 1) |
