Php/Symfony/Symfony2/doctrine/基本
提供: 初心者エンジニアの簡易メモ
目次
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);
