zend db 多表查询 LIMIT BUG 解决方法

来源：互联网发布：淘宝上的nike是正品吗编辑：程序博客网时间：2024/06/08 05:15

zend db 多表查询 LIMIT BUG 解决方法

将Zend/Db/Adapter/Pdo/mssql.php中如下方法替换。

public function limit($sql, $count, $offset = 0)
     {
        $count = intval($count);
        if ($count <= 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
        }

        $sql = preg_replace(
            '/^SELECT/s+(DISTINCT/s)?/i',
            'SELECT $1TOP ' . ($count+$offset) . ' ',
            $sql
            );

        if ($offset > 0) {
            $orderby = stristr($sql, 'ORDER BY');
            $mtype = stristr($sql, 'INNER JOIN');//多表分页判断2010-01-28

            if ($orderby !== false) {
                $orderParts = explode(',', substr($orderby, 8));
                $pregReplaceCount = null;
                $orderbyInverseParts = array();
    $orderbyInverseParts2 = array();//
                foreach ($orderParts as $orderPart) {
                    $orderPart = rtrim($orderPart);

     //
     if ($mtype!==false){
     $inv2='"outer_tbl"'.stristr($orderPart, '.');
     $orderbyInverseParts2[] = $inv2;
     }
     //

                    $inv = preg_replace('//s+desc$/i', ' ASC', $orderPart, 1, $pregReplaceCount);
                    if ($pregReplaceCount) {
      //
      if ($mtype!==false){
      $inv='"inner_tbl"'.stristr($inv, '.');
      }
      //
                        $orderbyInverseParts[] = $inv;
                        continue;
                    }
                    $inv = preg_replace('//s+asc$/i', ' DESC', $orderPart, 1, $pregReplaceCount);
                    if ($pregReplaceCount) {
      //
      if ($mtype!==false){
      $inv='"inner_tbl"'.stristr($inv, '.');
      }
      //
                        $orderbyInverseParts[] = $inv;
                        continue;
                    } else {
                        $orderbyInverseParts[] = $orderPart . ' DESC';
                    }
                }

                $orderbyInverse = 'ORDER BY ' . implode(', ', $orderbyInverseParts);
    $orderbyInverse2 = 'ORDER BY ' . implode(', ', $orderbyInverseParts2);
            }

            $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
            if ($orderby !== false) {


                $sql .= ' ' . $orderbyInverse . ' ';

            }
            $sql .= ') AS outer_tbl';
            if ($orderby !== false) {
    //
    if ($mtype!== false){
     $sql .= ' ' . $orderbyInverse2 . ' ';
    }else{
                $sql .= ' ' . $orderby;
    }
    //
            }
        }

return $sql;
}

A    SELECT "ts_cgb".*, "area_plan"."Name" FROM "ts_cgb"
INNER JOIN "area_plan" ON area_plan.Id = ts_cgb.PlanId WHERE (ts_cgb.Isbn like '%%') AND (ts_cgb.Sid = '1') AND (ts_cgb.CatalogId != '-1')

B    SELECT * FROM (SELECT TOP 1 * FROM (SELECT TOP 11 "ts_cgb".*, "area_plan"."Name" FROM "ts_cgb"
INNER JOIN "area_plan" ON area_plan.Id = ts_cgb.PlanId WHERE (ts_cgb.Isbn like '%%') AND (ts_cgb.Sid = '1') AND (ts_cgb.CatalogId != '-1') ORDER BY "ts_cgb"."Id" ASC) AS inner_tbl ORDER BY  "ts_cgb"."Id" DESC ) AS outer_tbl ORDER BY "ts_cgb"."Id" ASC

C SELECT * FROM (SELECT TOP 1 * FROM (SELECT TOP 11 "ts_cgb".*, "area_plan"."Name" FROM "ts_cgb"
INNER JOIN "area_plan" ON area_plan.Id = ts_cgb.PlanId WHERE (ts_cgb.Isbn like '%%') AND (ts_cgb.Sid = '1') AND (ts_cgb.CatalogId != '-1') ORDER BY "ts_cgb"."Id" ASC) AS inner_tbl ORDER BY "inner_tbl"."Id" DESC ) AS outer_tbl ORDER BY "outer_tbl"."Id" ASC

C 为修改后正确处理SQL 效果。