Phalcon多表查询带分页

* 本文讨论的是Phalcon多表查询带分页的方法，需要返回总数，每页数据数，总页数等数据，便于前端显示；

* 方法1中（1）的本质是先进行全表查询，再分页，即使不分页，也比方法2（PHQL查询）慢很多；

*  所以大数据量的时候建议使用方法2（PHQL）；

 

1、通过面向对象的方式来创建查询

(1)

public static function getList($stat = 'all', $pageNum = 1, $pageSize = 20, $sortType = 'DESC')
{
$offset = ($pageNum - 1) * $pageSize;
$query = Order::query();
$order = Order::class;
$user = User::class;

$query->columns(array("$order.id", "account", "userName", "$user.mobile", "companyName",
"code", "productName", "stat", "nextValidDate", "validEndDate"));
//$query->join($user, "$order. account = $user. id", null, "LEFT");
$query->leftJoin($user, "$order.account = $user.id");
if ($stat != null || $stat !== 'all') {
    $query->where("stat = :stat:");
    $bindArray = array("stat" => $stat);
    $query->bind($bindArray);
}

$query->orderBy("nextValidDate ". $sortType);

//分页查询不返回总数
//$query->limit($pageSize, $offset);
//$result = $query->execute();

//分页查询返回总数，便于前端排版
$result = $query->execute();
$paginator = new PaginatorModel(array(
'data' => $result,
'limit' => $pageSize,
'page' => $pageNum
));

$page = $paginator->getPaginate();

return $page;
}

* 类名::class

返回带命名空间的类名（是php5.5+ 以后添加的一个新功能）

(2)另一种调用方式：

public static function getList($stat = 'all', $pageNum = 1, $pageSize = 20, $sortType = 'DESC')
    {
        $order = Order::class;
        $user = User::class;
        $result = getDI()->get('modelsManager')->createBuilder()
            ->columns(array("o.id", "account", "userName", "u.mobile", "companyName",
                "code", "name", "stat", "nextValidDate", "validEndDate"))
            ->addfrom($order,'o')
            ->leftjoin($user, 'o.account = u.id','u');
        if ($stat != null || $stat !== 'all') {
            $result->where("stat = :stat:", array("stat" => $stat));
        }
        $result = $result->orderBy("nextValidDate ". $sortType)
            ->limit($pageSize, $offset)
            ->getQuery()
            ->execute();

        $count = getDI()->get('modelsManager')->createBuilder()
            ->addfrom($order,'o')
            ->leftjoin($user, 'o.account = u.id','u');
        if ($stat != null || $stat !== 'all') {
            $count->where("stat = :stat:", array("stat" => $stat));
        }
        $count = $count->getQuery()
            ->execute()
            ->count();
        //总数
        $totalItems = $count; //总条数
        $totalPages = ceil($totalItems/$pageSize);//上取整,获取总页数
        $first = 1; //第一页
        $before = (($pageNum -1) > 0 ) ? ($pageNum-1):1;//前一页
        $last = $totalPages;
        $next = (($pageNum+1) < $totalPages) ? ($pageNum+1):$totalPages;//后一页

        $page = [
            'first' => (int)$first,
            'before' => (int)$before,
            'current' => (int)$pageNum,
            'last' => (int)$last,
            'next' => (int)$next,
            'total_pages' => (int)$totalPages,
            'total_items' => (int)$totalItems,
            'limit' => $pageSize,
            'items' => $result,
        ];

        return $page;
    }

 

2、PHQL查询

public static function getList($stat = 'all', $pageNum = 1, $pageSize = 20, $sortType = 'desc')
{
$condition = [];
$bindArray = [];
$limitPage = ($pageNum - 1) * $pageSize;
$sql = "select a.id, b.id as account, user_name as userName, b.mobile, company_name as companyName,
code, product_name as productName, stat as stat,
`next_valid_date` as nextValidDate, `valid_end_date` as validEndDate";
$sqlCount = "select count(*) as count "; //计数select语句



//from语句
$sqlFrom = " from `order` as a
left join `user` as b
on a.account = b.id ";

$sqlWhere = "";

if ($stat != null || $stat !== 'all') {
$condition[] = "stat = :stat";
$bindArray['stat'] = $stat;
}


//where语句
$allCondition = implode(" and ", $condition);
if (!empty($allCondition)) {
$sqlWhere .= " where {$allCondition}";
}


//排序语句
$sqlOrder = " order by a.next_valid_date ". $sortType;

//分页计算语句
$sqlLimit = " limit {$limitPage}, {$pageSize}";

$connection = getDI()->get('rDatabase');
$page = [];

//分页相关计数语句
$sqlCount .= $sqlFrom. $sqlWhere;
$count = $connection->fetchAll($sqlCount, \Phalcon\Db::FETCH_ASSOC, $bindArray);

//总数
$totalItems = $count[0]['count']; //总条数
$totalPages = ceil($totalItems/$pageSize);//上取整获取总页数
$first = 1; //第一页
$before = (($pageNum -1) > 0 ) ? ($pageNum-1):1;//前一页
$last = $totalPages;
$next = (($pageNum+1) < $totalPages) ? ($pageNum+1):$totalPages;//后一页

$page = [
'first' => (int)$first,
'before' => (int)$before,
'current' => (int)$pageNum,
'last' => (int)$last,
'next' => (int)$next,
'total_pages' => (int)$totalPages,
'total_items' => (int)$totalItems,
'limit' => $pageSize,
];

//查询sql语句
$sql .= $sqlFrom. $sqlWhere. $sqlOrder. $sqlLimit;

$result = $connection->fetchAll($sql, \Phalcon\Db::FETCH_ASSOC, $bindArray);
$page['items'] = $result;

return $page;
}

 

3. 耗时检测

（毫秒级）

$t1 = microtime(true);
/*
 * 运行的代码
 */
$t2 = microtime(true);
echo '耗时'.round($t2-$t1,3).'秒<br>';