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