symfony2中的经典查询

1.这是symfony2中的连表查询

$sql = 'SELECT g        FROM AppBundle:Goods g,             AppBundle:GoodsRelated gr        Where g.id = gr.relatedGoodsId        AND g.status  = :status        AND gr.goodsId = :goodsId        ORDER BY g.onShelfTime DESC';$params['status'] = Goods::STATUS_ON_SHELF;$params['goodsId'] = $goodsId;$query = $this->entityManager->createQuery($sql);$resultList = $query->setParameters($params)->setMaxResults($maxNumber)->getResult();

执行sql语句

SELECT c0_.id AS id_0 FROM cs_goods c0_, cs_goods_related c1_ WHERE c0_.id = c1_.related_goods_id AND c0_.status = 1 AND c1_.goods_id = '00ec49a3-a652-11e5-97ab-ac162d8b0550' ORDER BY c0_.on_shelf_time DESC LIMIT 10;
2.带分页的dql写法
public function getList($page, $pageSize, $targetUser, $startDate, $endDate){    if (empty($endDate)) {        $endDate = $startDate;    }    $startTimestamp = strtotime($startDate);    $endTimestamp = strtotime($endDate);    if ($startTimestamp > $endTimestamp) {        throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);    }    $warehouseId = $targetUser->getWarehouseId();    $sql = 'SELECT i FROM AppBundle:InventoryOrder i WHERE';    $sql .= ' i.warehouseId = :warehouseId';    if ($startTimestamp) {        $start = date('Y-m-d 00:00:00', $startTimestamp);        $end = date('Y-m-d 23:59:59', $endTimestamp);        $index = ' AND i.submitTime';        $sql .= $index;        $sql .= ' BETWEEN :startDate AND :endDate ';        $params['endDate'] = $end;        $params['startDate'] = $start;    }    $params['warehouseId'] = $warehouseId;    $sql .= ' ORDER By i.submitTime DESC, i.createTime DESC';    $query = $this->entityManager->createQuery($sql);    $offset = $pageSize * ($page - 1);    $orderList = $query->setParameters($params)->setFirstResult($offset)->setMaxResults($pageSize)->getResult();    $data = array();    if (!empty($orderList)) {        foreach ($orderList as $order) {            $operatorIds[] = $order->getOperatorId();        }        foreach ($orderList as $order) {            $data[] = InventoryOrderDTOBuilder::build($order);        }    }    return $data;}
3.这是where in的写法，也是使用queryBuilder的写法
$queryBuilder = $this->getEntityManager()->createQueryBuilder();$resultList = $queryBuilder->select('s')    ->from('AppBundle:StockHistory', 's')    ->Where('s.materialId = :materialId')    ->andWhere($queryBuilder->expr()->in('s.action', $actionList))    ->orderBy('s.createTime', 'DESC')    ->setParameter('materialId', $materialId)    ->getQuery()->getResult();return $resultList;

4.这是常用的查询：对于时间，是可选的时候，处理方式

private function _validateTime($startDate, $endDate){    if (empty($startDate) && empty($endDate)) {        return;    }    if (empty($startDate) && !empty($endDate) ) {        throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);    }    if (!empty($startDate) && empty($endDate)) {        $endDate = $startDate;    }    $startTimestamp = strtotime($startDate)?strtotime($startDate):false;    $endTimestamp = strtotime($endDate)?strtotime($endDate):false;    if ($startTimestamp === false) {        throw new \Exception("日期格式非法", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);    } else {        $start = date('Y-m-d 00:00:00', $startTimestamp);    }    if($startTimestamp === false){        throw new \Exception("日期格式非法", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);    } else {        $end = date('Y-m-d 23:59:59', $endTimestamp);    }    return array('start' => $start, 'end' => $end);}

这是一个经典的查询 queryBuilder的写法

private function _getOrderList($targetUser, $page, $pageSize, $startDate, $endDate, $status, $paymentId, $settlementId, $order, $orderBy){    // 验证支付方式    $this->_validatePayment($paymentId);    // 验证结算类型    $this->_validateSettlement($settlementId);    // 验证时间并获取时间    $timeRange = $this->_validateTime($startDate, $endDate);    $query = $this->entityManager->getRepository('AppBundle:SalesOrder')->createQueryBuilder('p');    if (!empty($status) && in_array($status, array(SalesOrder::UNCHECK_ORDER, SalesOrder::NOT_SEND_ORDER, SalesOrder::SENT_ORDER, SalesOrder::STATUS_SUBMITTED))) {        $query->where('p.status = :status')            ->setParameter('status', "$status");    }    if (!empty($status) && !in_array($status, array(SalesOrder::UNCHECK_ORDER, SalesOrder::NOT_SEND_ORDER, SalesOrder::SENT_ORDER, SalesOrder::STATUS_SUBMITTED))) {        throw new \Exception("参数不正确", BaseException::ERROR_CODE_ILLEGAL_PARAMETER);    }    $orderBy = $this->_confirmOrderBy($status, $orderBy);    $warehouseId = $targetUser->getWarehouseId();    $query->andWhere('p.warehouseId = :warehouseId')          ->setParameter('warehouseId', "$warehouseId")          ->orderBy($orderBy, $order)          ->setFirstResult($pageSize * ($page-1))          ->setMaxResults($pageSize);  //相当于limit  取多少条数据 setLimit(100);*/   if (!empty($timeRange)) {        $start = $timeRange['start'];        $end = $timeRange['end'];        $query->andWhere("{$orderBy} >= :start")              ->setParameter('start', "$start")              ->andWhere("{$orderBy} <= :end")              ->setParameter('end', "$end");    }    if (!empty($paymentId)) {        $query->andWhere('p.paymentId = :paymentId')            ->setParameter('paymentId', "$paymentId");    }    if (!empty($settlementId)) {        $query->andWhere('p.settlementId = :settlementId')            ->setParameter('settlementId', "$settlementId");    }    $resultList = $query->getQuery()->getResult();    return $resultList;}