Doctrine 查询语法

Doctrine Query Language - Doctrine Object Relational Mapper (ORM) 

 

显性的事务 qgmvc封装

$orm = orm();
$orm->begin();
try{
        ......
        $orm->commit();
}catch (\Throwable | \Exception $e){
       $orm->rollBack();
}

 Repository

$repository->find($id);
$repository->findAll();
$repository->findOneByName('Foo');
$repository->findAllOrderedByName();
$repository->findOneBy(array('name' => 'foo', 'price' => 19.99));
$repository->findBy(array('name' => 'foo'),array('price' => 'ASC'));

DQL  createQuery

query = $em->createQuery('SELECT p FROM AcmeStoreBundle:Product p WHERE p.price > :price ORDER BY p.price ASC')->setParameter('price', '19.99′);
$products = $query->getResult();

//取一条结果
try {
        $product = $query->getSingleResult();
} catch (\Doctrine\Orm\NoResultException $e) {
        $product = null;
}

原生SQL

$sql = 'SELECT * FROM sys_user';
$result = $orm->query($sql); //等同 $this->manager->getConnection()->query($sql);
dd($result->fetch()); 

createQueryBuilder

qgmvc
build($tag) = $orm->getManager()->getRepository($entityName)->createQueryBuilder($tag)

build('e')->select('e')->getQuery()->getResult(); //对象
build('e')->select('e')->getQuery()->toArray(); //数据

->select('a','b') 
或者
->select('a.id,a.name,b.type_name')
->from('Custom\Entity\Book','a')
->Join(     
'Custom\Entity\BookType',
     'b',
     \Doctrine\ORM\Query\Expr\Join::LEFT_JOIN,
     'a.type=b.id' )
->addOrderBy('a.id', 'DESC')
->getQuery()
->getResult();

 

WHERE

->where( 'e.' . $fieldName. " LIKE '%test1648719565%'")
->andWhere("e." . $fieldName . " <> '" . $keyword . "'")

WHERE PARAM

->where('p.price > :price')
->setParameter('price', '19.99′)

排序
->orderBy('e.' . $fieldName, 'DESC')

条数 

->setFirstResult(1)

->setMaxResults(20)
 

取日期时间中的日期分组与统计

"SELECT SUBSTRING(e.".$fieldName.",1,10) field,COUNT(1) total FROM ".$this->entityName." e" GROUP BY field