ZF2联合+分页 [英] ZF2 Union + Pagination

问题描述

我正在使用ZF2和SQL Server2012.我正在尝试编写带有分页的联合查询.

I'm using ZF2 and SQL Server 2012. I'm trying to write a union query with pagination.

似乎对ZF2中的工会的支持还不完全.没有union()方法，但是我看到有一个combine()方法.它使用联合创建查询，但是将ORDER BY放在第一个查询上，而不是整个联合查询上.因此，当ZF2分页器尝试添加限制/偏移量部分时，SQL Server会感到窒息，因为它在整个查询中必须具有ORDER BY.

It seems like support for unions in ZF2 is not fully there. There is no union() method, but I see that there is a combine() method. It creates the query with the union, but it puts the ORDER BY on the first query instead of on the entire unioned query. So when the ZF2 paginator tries to add the limit/offset parts, SQL Server chokes because it has to have an ORDER BY on the whole query.

这是我的代码:

public function fetchPage($fkADUser, array $orderBy, $page = 1, $limit = 20) {

    $inboxSelect = $this->inboxTableGateway->getSql()
        ->select()
        ->columns([
            'pkid',
        ])
        ->join(['f' => 'Fax'], 'FaxInbound.fkFax = f.pkid', [
            'PageCount',
            'CreateTime',
        ])
        ->where(['f.fkADUser = ?' => $fkADUser])
        ->where('FaxInbound.DeleteTime IS NOT NULL');

    $sentSelect = $this->sentTableGateway->getSql()
        ->select()
        ->columns([
            'pkid',
        ])
        ->join(['f' => 'Fax'], 'FaxOutbound.fkFax = f.pkid', [
            'PageCount',
            'CreateTime',
        ])
        ->where(['f.fkADUser = ?' => $fkADUser])
        ->where('FaxOutbound.DeleteTime IS NOT NULL');

    $inboxSelect->combine($sentSelect)->order($orderBy);

    return $this->inboxTableGateway->paginate($inboxSelect, $page, $limit, $this->resultSetPrototype);
}

这是从SQL Profiler生成的查询:

And here is the query generated (from SQL Profiler):

(
    SELECT
        [FaxInbound].[pkid] AS [pkid],
        [f].[PageCount] AS [PageCount],
        [f].[CreateTime] AS [CreateTime]
    FROM
        [FaxInbound]
        INNER JOIN [Fax] AS [f]
            ON [FaxInbound].[fkFax] = [f].[pkid]
    WHERE
        f.fkADUser = @P1
        AND FaxInbound.DeleteTime IS NOT NULL
    ORDER BY
        [CreateTime] DESC
) UNION (
    SELECT
        [FaxOutbound].[pkid] AS [pkid],
        [f].[PageCount] AS [PageCount],
        [f].[CreateTime] AS [CreateTime]
    FROM
        [FaxOutbound]
        INNER JOIN [Fax] AS [f]
            ON [FaxOutbound].[fkFax] = [f].[pkid]
    WHERE
        f.fkADUser = @P2
        AND FaxOutbound.DeleteTime IS NOT NULL
)
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY

请注意，ORDER BY位于第一个查询上，而不是整个联合查询上.所以我收到的错误消息是:

Notice that the ORDER BY is on the first query instead of the entire unioned query. So the error messages I get are:

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ORDER'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'OFFSET'.
Msg 153, Level 15, State 2, Line 29
Invalid usage of the option NEXT in the FETCH statement.

但是，如果将ORDER BY移到外部，恰好在OFFSET之前，则查询有效.

But if I move the ORDER BY outside, just before the OFFSET, the query works.

所以问题是，有人知道如何将ORDER BY放在整个查询中而不是第一个查询中吗?还是我可以通过手动编写的查询来使用ZF2分页器?

So the question is, does anyone know how to put the ORDER BY on the whole query instead of the first one? Or, Is there a way for me to use the ZF2 Paginator with a manually written query?

推荐答案

ZF2中似乎有一个错误.我将其添加到 ZF2问题跟踪器.

Looks like there is a bug in ZF2. I added it to the ZF2 issue tracker.

我通过重新排序Zend \ Db \ Sql \ Select :: $ specifications中的条目来解决此问题.

I fixed the issue by reordering the entries in Zend\Db\Sql\Select::$specifications.

原文:

protected $specifications = array(
    'statementStart' => '%1$s',
    self::SELECT => array(...),
    self::JOINS  => array(...),
    self::WHERE  => 'WHERE %1$s',
    self::GROUP  => array(...),
    self::HAVING => 'HAVING %1$s',
    self::ORDER  => array(...),
    self::LIMIT  => 'LIMIT %1$s',
    self::OFFSET => 'OFFSET %1$s',
    'statementEnd' => '%1$s',
    self::COMBINE => '%1$s ( %2$s )',
);

新功能:

protected $specifications = array(
    'statementStart' => '%1$s',
    self::SELECT => array(...),
    self::JOINS  => array(...),
    self::WHERE  => 'WHERE %1$s',
    self::GROUP  => array(...),
    self::HAVING => 'HAVING %1$s',
    'statementEnd' => '%1$s',
    self::COMBINE => '%1$s ( %2$s )',
    self::ORDER  => array(...),
    self::LIMIT  => 'LIMIT %1$s',
    self::OFFSET => 'OFFSET %1$s',
);

我只是将ORDER，LIMIT和OFFSET移到了最后.这为我解决了这个问题.

I just moved ORDER, LIMIT, and OFFSET to the end. This fixed the query for me.

这篇关于ZF2联合+分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！