「Php/Symfony/Symfony2/doctrine/基本」の版間の差分
提供: 初心者エンジニアの簡易メモ
(→複数条件で1レコード取得) |
|||
(同じ利用者による、間の10版が非表示) | |||
行141: | 行141: | ||
==insert処理== | ==insert処理== | ||
− | + | use Acme\HelloBundle\Entity\User; | |
+ | $user = new User(); | ||
$user->setName('taro'); | $user->setName('taro'); | ||
$user->setDescription('太郎です'); | $user->setDescription('太郎です'); | ||
行154: | 行155: | ||
| 1 | taro | 太郎です | | | 1 | taro | 太郎です | | ||
+----+-----------+-------------------+ | +----+-----------+-------------------+ | ||
+ | |||
+ | トランザクションでinsert処理される | ||
+ | [2016-12-27 10:58:30] doctrine.DEBUG: "START TRANSACTION" [] [] | ||
+ | [2016-12-27 10:58:30] doctrine.DEBUG: INSERT INTO users (name, description) VALUES (?, ?) {"1":"taro","2":"太郎です"} [] | ||
+ | [2016-12-27 10:58:30] doctrine.DEBUG: "COMMIT" [] [] | ||
==select処理== | ==select処理== | ||
行186: | 行192: | ||
==複数条件で1レコード取得== | ==複数条件で1レコード取得== | ||
− | |||
$user = $repository->findOneBy(array('name' => 'taro', 'description' => "太郎です")); | $user = $repository->findOneBy(array('name' => 'taro', 'description' => "太郎です")); | ||
if ($user) { | if ($user) { | ||
行192: | 行197: | ||
} | } | ||
Daoクラスにないプロパティ(リレーション用のカラムとか)を条件に加えると502になる | Daoクラスにないプロパティ(リレーション用のカラムとか)を条件に加えると502になる | ||
+ | |||
+ | ==DQLで実行== | ||
+ | $em = $this->getDoctrine()->getEntityManager(); | ||
+ | $query = $em->createQuery( | ||
+ | "SELECT u.name,u.description FROM AcmeHelloBundle:User u WHERE u.name = :name ORDER BY u.description ASC" | ||
+ | )->setParameter('name', 'taro'); | ||
+ | $users = $query->getResult(); | ||
+ | var_dump($users); // 連想配列が戻ってくる | ||
==順序を設定して取得== | ==順序を設定して取得== | ||
− | + | $users = $repository->findBy(array('name' => 'taro'), array('description' => 'ASC')); | |
− | $ | + | |
==更新処理== | ==更新処理== | ||
行212: | 行224: | ||
$em->remove($user); | $em->remove($user); | ||
$em->flush(); | $em->flush(); | ||
+ | |||
+ | ==sql実行ログ== | ||
+ | $ tail -f app/logs/dev.log | ||
+ | [2016-12-26 17:58:49] doctrine.DEBUG: SELECT t0.id AS id1, t0.name AS name2, t0.description AS description3, t0.category_id AS category_id4 FROM users t0 WHERE t0.id = ? [6] [] | ||
+ | |||
+ | ==persist取り消し== | ||
+ | detachでpersistで追加したものを取り消せる | ||
+ | $em = $this->getDoctrine()->getEntityManager(); | ||
+ | $em->persist($user); | ||
+ | $em->detach($user); | ||
+ | |||
+ | ==em確認== | ||
+ | var_dump($this->em->getConnection()->getParams()); | ||
+ | [driver] => pdo_mysql | ||
+ | [host] => localhost | ||
+ | [port] => 3306 | ||
+ | [dbname] => testdb | ||
+ | [user] => user1 | ||
+ | [password] => **** | ||
+ | [charset] => UTF8 | ||
+ | [driverOptions] => Array | ||
+ | ( | ||
+ | ) | ||
+ | |||
+ | ==sql_quote== | ||
+ | $quoted = $conn->quote('value'); | ||
+ | $quoted = $conn->quote('1234', \PDO::PARAM_INT); | ||
==参考== | ==参考== | ||
http://docs.symfony.gr.jp/symfony2/book/doctrine.html | http://docs.symfony.gr.jp/symfony2/book/doctrine.html | ||
+ | |||
+ | http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html |
2017年2月10日 (金) 09:57時点における最新版
目次
[非表示]db設定
app/config/parameters.yml parameters database_driver = pdo_mysql database_host = localhost database_name = project1 database_user = user1 database_password = *****
dbのschemaを作る
php app/console doctrine:database:create
or
create database project1;
テーブルクラス作成
-Acme/HelloBundle/Entity/User.php
namespace Acme\HelloBundle\Entity; class User { protected $name; protected $description; }
generateでテーブルクラスを作った場合
dbとのマッピング処理などが追加された記述をして作成される
$ php app/console doctrine:generate:entity --entity="AcmeHelloBundle:User" --fields="name:string(255) description:text"
-Acme/HelloBundle/Entity/User.php
namespace Acme\HelloBundle\Entity; use Doctrine\ORM\Mapping as ORM; /** * User * * @ORM\Table() * @ORM\Entity */ class User { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var string * * @ORM\Column(name="name", type="string", length=255) */ private $name; /** * @var string * * @ORM\Column(name="description", type="text") */ private $description; /** * Get id * * @return integer */ public function getId() { return $this->id; } /** * Set name * * @param string $name * @return User */ public function setName($name) { $this->name = $name; return $this; } /** * Get name * * @return string */ public function getName() { return $this->name; } /** * Set description * * @param string $description * @return User */ public function setDescription($description) { $this->description = $description; return $this; } /** * Get description * * @return string */ public function getDescription() { return $this->description; } }
db側のtable名を小文字の複数に変更
このままだとUserテーブルができるので、usersテーブルになるようにEntity/User.phpのアノテーションを書き換える
変更前
/** * User * * @ORM\Table() * @ORM\Entity */
変更後
/** * User * * @ORM\Table(name="users") * @ORM\Entity */
generateしたテーブルクラスからdb側のtableを作る
$ php app/console doctrine:schema:update --force CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` longtext COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
insert処理
use Acme\HelloBundle\Entity\User; $user = new User(); $user->setName('taro'); $user->setDescription('太郎です'); $em = $this->getDoctrine()->getEntityManager(); $em->persist($user); $em->flush(); // ここでinsert処理がされる
select * from users; +----+-----------+-------------------+ | id | name | description | +----+-----------+-------------------+ | 1 | taro | 太郎です | +----+-----------+-------------------+
トランザクションでinsert処理される
[2016-12-27 10:58:30] doctrine.DEBUG: "START TRANSACTION" [] [] [2016-12-27 10:58:30] doctrine.DEBUG: INSERT INTO users (name, description) VALUES (?, ?) {"1":"taro","2":"太郎です"} [] [2016-12-27 10:58:30] doctrine.DEBUG: "COMMIT" [] []
select処理
$repository = $this->getDoctrine() ->getRepository('AcmeHelloBundle:User'); $user = $repository->find(1); // userクラスにデータを入れて取得 if ($user) { echo $user->getId(); // 1 echo $user->getName(); // taro echo $user->getDescription(); // 太郎です }
1レコード取得
$user = $repository->findOneById(1); echo $user->getId();
全取得
$users = $repository->findAll(); foreach ($users as $user) { echo $user->getId()."\n"; }
条件付き1レコード取得
$user = $repository->findOneByName('taro'); echo $user->getId();
条件付き複数行取得
$users = $repository->findByName('taro'); foreach ($users as $user) { echo $user->getName()."\n"; }
複数条件で1レコード取得
$user = $repository->findOneBy(array('name' => 'taro', 'description' => "太郎です")); if ($user) { echo $user->getName(); }
Daoクラスにないプロパティ(リレーション用のカラムとか)を条件に加えると502になる
DQLで実行
$em = $this->getDoctrine()->getEntityManager(); $query = $em->createQuery( "SELECT u.name,u.description FROM AcmeHelloBundle:User u WHERE u.name = :name ORDER BY u.description ASC" )->setParameter('name', 'taro'); $users = $query->getResult(); var_dump($users); // 連想配列が戻ってくる
順序を設定して取得
$users = $repository->findBy(array('name' => 'taro'), array('description' => 'ASC'));
更新処理
$em = $this->getDoctrine()->getEntityManager(); $repository = $this->getDoctrine() ->getRepository('AcmeHelloBundle:User'); $user = $repository->find(1); $user->setName('jiro'); $em->flush();
削除処理
$em = $this->getDoctrine()->getEntityManager(); $repository = $this->getDoctrine() ->getRepository('AcmeHelloBundle:User'); $user = $repository->find(2); $em->remove($user); $em->flush();
sql実行ログ
$ tail -f app/logs/dev.log [2016-12-26 17:58:49] doctrine.DEBUG: SELECT t0.id AS id1, t0.name AS name2, t0.description AS description3, t0.category_id AS category_id4 FROM users t0 WHERE t0.id = ? [6] []
persist取り消し
detachでpersistで追加したものを取り消せる
$em = $this->getDoctrine()->getEntityManager(); $em->persist($user); $em->detach($user);
em確認
var_dump($this->em->getConnection()->getParams()); [driver] => pdo_mysql [host] => localhost [port] => 3306 [dbname] => testdb [user] => user1 [password] => **** [charset] => UTF8 [driverOptions] => Array ( )
sql_quote
$quoted = $conn->quote('value'); $quoted = $conn->quote('1234', \PDO::PARAM_INT);