逍遥阁

doctrineORM之QueryBuilder

关键词: doctrineORM QueryBuilder

doctrineORM之QueryBuilder

###得到QueryBuilder对象

```
$em = $this->getDoctrine()->getManager();
$qb = $em->getRepository('Bundle:Entity')->createQueryBuilder('别名');
```

###QueryBuilder方法集

#####add()

```
$qb = $qb->add('select', '别名');
$qb = $qb->add('from', 'Entity 别名');
$qb = $qb->add('orderBy', '别名.字段 ASC');
```

#####set($key, $value)

```
$qb = $qb->set('别名.字段', $qb->expr()->literal('1'));
$qb = $qb->set('别名.字段', '1');
$qb = $qb->set('别名.字段', $qb->expr()->sum('别名.字段', '1'));
```

#####select($select = null)

```
$qb = $qb->select('别名');
$qb = $qb->select('别名1','别名2');
$qb = $qb->select(array('别名'));
$qb = $qb->select(array('别名1','别名2'));
$qb = $qb->select($qb->expr()->select('别名1', '别名2'));
```

#####addSelect($select = null)

```
$qb = $qb->addSelect('别名.字段');
```

#####from($from, $alias, $indexBy = null)

```
$qb = $qb->from('Entity', '别名');
$qb = $qb->from('Entity', '别名', '别名.字段');
```

#####where($where)

会覆盖之前设置的条件

```
$qb = $qb->where('别名.字段=1');
$qb = $qb->where(
    $qb->expr()->orX(
        $qb->expr()->eq('别名.字段', '1'),
        $qb->expr()->like('别名.字段', '1')
    )
);
$qb = $qb->where('条件1', '条件2');
$qb = $qb->where('条件1 AND 条件2');
```

#####andWhere($where)

即使没有调用过where()也可以直接使用

```
$qb = $qb->andWhere($qb->expr()->orX($qb->expr()->lte('别名.字段', 10), '别名.字段=0'));
```

#####orWhere($where)

```
$qb = $qb->orWhere($qb->expr()->between('别名.字段', 1, 10));
```

#####orderBy($sort, $order = null)

会覆盖之前设置的条件

```
$qb = $qb->orderBy('别名.字段','ASC|DESC');
```

#####addOrderBy($sort, $order = null)

```
$qb = $qb->addOrderBy('别名.字段');
$qb = $qb->addOrderBy('别名.字段', 'ASC');
```

#####groupBy($groupBy)

会覆盖之前设置的条件

```
$qb = $qb->groupBy('别名.字段')
```

#####addGroupBy($groupBy)

```
$qb = $qb->addGroupBy('别名.字段')
```

#####having($having)

会覆盖之前设置的条件

```
$qb = $qb->having('条件');
$qb = $qb->having($qb->expr()->gte('别名.字段', 10));
```

#####andHaving($having)

```
$qb = $qb->andHaving($qb->expr()->gt($qb->expr()->count('别名.字段'), 0));
```

#####orHaving($having)

```
$qb = $qb->orHaving($qb->expr()->lte('别名.字段', '100'));
```

#####getType()

0(QueryBuilder::SELECT)

1(QueryBuilder::DELETE)

2(QueryBuilder::UPDATE)

#####getEntityManager()

```
$em = $qb->getEntityManager();
```

#####getDql()

```
$dql = $qb->getDql();
```

#####getQuery()

```
$query = $qb->getQuery();
$query->setQueryHint('a', 'b');
$query->useResultCache('id');
$result = $query->getResult();
$single = $query->getSingleResult();
$array = $query->getArrayResult();
$scalar = $query->getScalarResult();
$singleScalar = $query->getSingleScalarResult();
```

需要注意的是与直接写DQL是等价的

```
$query = $em->createQuery('SELECT 别名 FROM Entity 别名 WHERE 别名.id > 10');
```

#####delete($delete = null, $alias = null)

```
$qb = $qb->delete('Entity', '别名');
```

#####update($update = null, $alias = null)

```
$qb = $qb->update('Entity', '别名');
```

#####expr()

```
$qb->expr()->eq('别名.字段', '1')
$qb->expr()->like('别名.字段', '1')
$qb->expr()->andX()
$qb->expr()->orX()
$qb->expr()->select('别名1', '别名2')
$qb->expr()->literal('1')
$qb->expr()->sum('别名.字段', '1')
$qb->expr()->lte('别名.字段', 10)
$qb->expr()->gte('别名.字段', 10)
$qb->expr()->between('别名.字段', 1, 10)
$qb->expr()->count('别名.字段')
```

完整列表

```
andX($x = null)
orX($x = null)
eq($x, $y)
neq($x, $y)
lt($x, $y)
lte($x, $y)
gt($x, $y)
gte($x, $y)
isNull($x)
isNotNull($x)
prod($x, $y)
diff($x, $y)
sum($x, $y)
quot($x, $y)
exists($subquery)
all($subquery)
some($subquery)
any($subquery)
not($restriction)
in($x, $y)
notIn($x, $y)
like($x, $y)
notLike($x, $y)
between($val, $x, $y)
trim($x)
concat($x, $y)
substring($x, $from, $len)
lower($x)
upper($x)
length($x)
avg($x)
max($x)
min($x)
abs($x)
sqrt($x)
count($x)
countDistinct($x)
```

#####join|innerJoin|leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)

```
$qb = $qb->join('别名.Entity', '别名', Expr\Join::WITH, $qb->expr()->eq('别名.字段', '1'));
$qb = $qb->join('别名.Entity', '别名', 'WITH', '别名.字段=1');
$qb = $qb->join('别名.Entity', '别名', 'WITH', '别名.字段=1', '别名.字段');
```

#####setParameter($number, $value)

```
$qb = $qb->setParameter(1, 100);/*把?1设置为100*/
$qb = $qb->setParameter('name', 100);/*把:name设置为100*/
```

#####setParameters($array)

```
$qb = $qb->setParameters(array(1 => '100', 2 => '200'));
```

#####getParameter($number)

返回Parameter对象

```
$parameter = $qb->getParameter(1);
```

#####getParameters()

返回ArrayCollection对象

```
$parameters = $qb->getParameters();
```

#####setFirstResult($offset)

```
$qb = $qb->setFirstResult(0);
```

#####setMaxResults($limit)

```
$qb = $qb->setMaxResults(10);
```

admin
2017-02-13 11:46:10
php

文章分类

文章标签

文章归档

友情链接

doctrineORM之QueryBuilder