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);

参考

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