PHP Select::combine方法代码示例

 public function getAllColumns()
 {
     $selectOne = new Select();
     $selectOne->columns(['column_name' => 'COLUMN_NAME', 'sort' => new Expression('IFNULL(sort, ORDINAL_POSITION)'), 'type' => new Expression('UCASE(C.DATA_TYPE)'), 'char_length' => 'CHARACTER_MAXIMUM_LENGTH', 'is_nullable' => 'IS_NULLABLE', 'default_value' => 'COLUMN_DEFAULT', 'comment' => new Expression('IFNULL(comment, COLUMN_COMMENT)'), 'column_type' => 'COLUMN_TYPE', 'column_key' => 'COLUMN_KEY']);
     $selectOne->from(['C' => new TableIdentifier('COLUMNS', 'INFORMATION_SCHEMA')]);
     $selectOne->join(['D' => 'directus_columns'], 'C.COLUMN_NAME = D.column_name AND C.TABLE_NAME = D.table_name', ['ui', 'hidden_list' => new Expression('IFNULL(hidden_list, 0)'), 'hidden_input' => new Expression('IFNULL(hidden_input, 0)'), 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(D.required, 0)')], $selectOne::JOIN_LEFT);
     $selectOne->join(['T' => new TableIdentifier('TABLES', 'INFORMATION_SCHEMA')], 'C.TABLE_NAME = T.TABLE_NAME', ['table_name' => 'TABLE_NAME'], $selectOne::JOIN_LEFT);
     $selectOne->where(['C.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_TYPE' => 'BASE TABLE']);
     $selectTwo = new Select();
     $selectTwo->columns(['column_name', 'sort', 'type' => new Expression('UCASE(data_type)'), 'char_length' => new Expression('NULL'), 'is_nullable' => new Expression('"NO"'), 'default_value' => new Expression('NULL'), 'comment', 'column_type' => new Expression('NULL'), 'column_key' => new Expression('NULL'), 'ui', 'hidden_list', 'hidden_input', 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(required, 0)'), 'table_name']);
     $selectTwo->from('directus_columns');
     $where = new Where();
     $where->addPredicate(new In('data_type', ['alias', 'MANYTOMANY', 'ONETOMANY']));
     $selectTwo->where($where);
     $selectOne->combine($selectTwo, $selectOne::COMBINE_UNION, 'ALL');
     $selectOne->order('table_name');
     $sql = new Sql($this->adapter);
     $statement = $sql->prepareStatementForSqlObject($selectOne);
     return iterator_to_array($statement->execute());
 }

//.........这里部分代码省略.........
     $select36 = new Select();
     $select36->from('foo')->join('tableA', new Predicate\Operator('id', '=', 1))->join('tableB', new Predicate\Operator('id', '=', 2))->join('tableC', new Predicate\PredicateSet(array(new Predicate\Operator('id', '=', 3), new Predicate\Operator('number', '>', 20))));
     $sqlPrep36 = 'SELECT "foo".*, "tableA".*, "tableB".*, "tableC".* FROM "foo"' . ' INNER JOIN "tableA" ON "id" = :join1part1 INNER JOIN "tableB" ON "id" = :join2part1 ' . 'INNER JOIN "tableC" ON "id" = :join3part1 AND "number" > :join3part2';
     $sqlStr36 = 'SELECT "foo".*, "tableA".*, "tableB".*, "tableC".* FROM "foo" ' . 'INNER JOIN "tableA" ON "id" = \'1\' INNER JOIN "tableB" ON "id" = \'2\' ' . 'INNER JOIN "tableC" ON "id" = \'3\' AND "number" > \'20\'';
     $internalTests36 = array();
     $useNamedParams36 = true;
     /**
      * @author robertbasic
      * @link https://github.com/zendframework/zf2/pull/2714
      */
     $select37 = new Select();
     $select37->from('foo')->columns(array('bar'), false);
     $sqlPrep37 = $sqlStr37 = 'SELECT "bar" AS "bar" FROM "foo"';
     $internalTests37 = array('processSelect' => array(array(array('"bar"', '"bar"')), '"foo"'));
     // @link https://github.com/zendframework/zf2/issues/3294
     // Test TableIdentifier In Joins
     $select38 = new Select();
     $select38->from('foo')->columns(array())->join(new TableIdentifier('bar', 'baz'), 'm = n', array('thecount' => new Expression("COUNT(*)")));
     $sqlPrep38 = $sqlStr38 = 'SELECT COUNT(*) AS "thecount" FROM "foo" INNER JOIN "baz"."bar" ON "m" = "n"';
     $internalTests38 = array('processSelect' => array(array(array('COUNT(*)', '"thecount"')), '"foo"'), 'processJoins' => array(array(array('INNER', '"baz"."bar"', '"m" = "n"'))));
     // subselect in join
     $select39subselect = new Select();
     $select39subselect->from('bar')->where->like('y', '%Foo%');
     $select39 = new Select();
     $select39->from('foo')->join(array('z' => $select39subselect), 'z.foo = bar.id');
     $sqlPrep39 = 'SELECT "foo".*, "z".* FROM "foo" INNER JOIN (SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "z" ON "z"."foo" = "bar"."id"';
     $sqlStr39 = 'SELECT "foo".*, "z".* FROM "foo" INNER JOIN (SELECT "bar".* FROM "bar" WHERE "y" LIKE \'%Foo%\') AS "z" ON "z"."foo" = "bar"."id"';
     $internalTests39 = array('processJoins' => array(array(array('INNER', '(SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "z"', '"z"."foo" = "bar"."id"'))));
     // @link https://github.com/zendframework/zf2/issues/3294
     // Test TableIdentifier In Joins, with multiple joins
     $select40 = new Select();
     $select40->from('foo')->join(array('a' => new TableIdentifier('another_foo', 'another_schema')), 'a.x = foo.foo_column')->join('bar', 'foo.colx = bar.colx');
     $sqlPrep40 = $sqlStr40 = 'SELECT "foo".*, "a".*, "bar".* FROM "foo"' . ' INNER JOIN "another_schema"."another_foo" AS "a" ON "a"."x" = "foo"."foo_column"' . ' INNER JOIN "bar" ON "foo"."colx" = "bar"."colx"';
     $internalTests40 = array('processSelect' => array(array(array('"foo".*'), array('"a".*'), array('"bar".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"another_schema"."another_foo" AS "a"', '"a"."x" = "foo"."foo_column"'), array('INNER', '"bar"', '"foo"."colx" = "bar"."colx"'))));
     $select41 = new Select();
     $select41->from('foo')->quantifier(Select::QUANTIFIER_DISTINCT);
     $sqlPrep41 = $sqlStr41 = 'SELECT DISTINCT "foo".* FROM "foo"';
     $internalTests41 = array('processSelect' => array(SELECT::QUANTIFIER_DISTINCT, array(array('"foo".*')), '"foo"'));
     $select42 = new Select();
     $select42->from('foo')->quantifier(new Expression('TOP ?', array(10)));
     $sqlPrep42 = 'SELECT TOP ? "foo".* FROM "foo"';
     $sqlStr42 = 'SELECT TOP \'10\' "foo".* FROM "foo"';
     $internalTests42 = array('processSelect' => array('TOP ?', array(array('"foo".*')), '"foo"'));
     $select43 = new Select();
     $select43->from(array('x' => 'foo'))->columns(array('bar'), false);
     $sqlPrep43 = 'SELECT "bar" AS "bar" FROM "foo" AS "x"';
     $sqlStr43 = 'SELECT "bar" AS "bar" FROM "foo" AS "x"';
     $internalTests43 = array('processSelect' => array(array(array('"bar"', '"bar"')), '"foo" AS "x"'));
     $select44 = new Select();
     $select44->from('foo')->where('a = b');
     $select44b = new Select();
     $select44b->from('bar')->where('c = d');
     $select44->combine($select44b, Select::COMBINE_UNION, 'ALL');
     $sqlPrep44 = $sqlStr44 = '( SELECT "foo".* FROM "foo" WHERE a = b ) UNION ALL ( SELECT "bar".* FROM "bar" WHERE c = d )';
     $internalTests44 = array('processCombine' => array('UNION ALL', 'SELECT "bar".* FROM "bar" WHERE c = d'));
     // limit with offset
     $select45 = new Select();
     $select45->from('foo')->limit("5")->offset("10");
     $sqlPrep45 = 'SELECT "foo".* FROM "foo" LIMIT ? OFFSET ?';
     $sqlStr45 = 'SELECT "foo".* FROM "foo" LIMIT \'5\' OFFSET \'10\'';
     $params45 = array('limit' => 5, 'offset' => 10);
     $internalTests45 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'), 'processOffset' => array('?'));
     // functions without table
     $select46 = new Select();
     $select46->columns(array(new Expression('SOME_DB_FUNCTION_ONE()'), 'foo' => new Expression('SOME_DB_FUNCTION_TWO()')));
     $sqlPrep46 = 'SELECT SOME_DB_FUNCTION_ONE() AS Expression1, SOME_DB_FUNCTION_TWO() AS "foo"';
     $sqlStr46 = 'SELECT SOME_DB_FUNCTION_ONE() AS Expression1, SOME_DB_FUNCTION_TWO() AS "foo"';
     $params46 = array();
     $internalTests46 = array();
     // limit with big offset and limit
     $select47 = new Select();
     $select47->from('foo')->limit("10000000000000000000")->offset("10000000000000000000");
     $sqlPrep47 = 'SELECT "foo".* FROM "foo" LIMIT ? OFFSET ?';
     $sqlStr47 = 'SELECT "foo".* FROM "foo" LIMIT \'10000000000000000000\' OFFSET \'10000000000000000000\'';
     $params47 = array('limit' => 1.0E+19, 'offset' => 1.0E+19);
     $internalTests47 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'), 'processOffset' => array('?'));
     //combine and union with order at the end
     $select48 = new Select();
     $select48->from('foo')->where('a = b');
     $select48b = new Select();
     $select48b->from('bar')->where('c = d');
     $select48->combine($select48b);
     $select48combined = new Select();
     $select48 = $select48combined->from(array('sub' => $select48))->order('id DESC');
     $sqlPrep48 = $sqlStr48 = 'SELECT "sub".* FROM (( SELECT "foo".* FROM "foo" WHERE a = b ) UNION ( SELECT "bar".* FROM "bar" WHERE c = d )) AS "sub" ORDER BY "id" DESC';
     $internalTests48 = array('processCombine' => null);
     //Expression as joinName
     $select49 = new Select();
     $select49->from(new TableIdentifier('foo'))->join(array('bar' => new Expression('psql_function_which_returns_table')), 'foo.id = bar.fooid');
     $sqlPrep49 = $sqlStr49 = 'SELECT "foo".*, "bar".* FROM "foo" INNER JOIN psql_function_which_returns_table AS "bar" ON "foo"."id" = "bar"."fooid"';
     $internalTests49 = array('processSelect' => array(array(array('"foo".*'), array('"bar".*')), '"foo"'), 'processJoins' => array(array(array('INNER', 'psql_function_which_returns_table AS "bar"', '"foo"."id" = "bar"."fooid"'))));
     /**
      * $select = the select object
      * $sqlPrep = the sql as a result of preparation
      * $params = the param container contents result of preparation
      * $sqlStr = the sql as a result of getting a string back
      * $internalTests what the internal functions should return (safe-guarding extension)
      */
     return array(array($select0, $sqlPrep0, array(), $sqlStr0, $internalTests0), array($select1, $sqlPrep1, array(), $sqlStr1, $internalTests1), array($select2, $sqlPrep2, array(), $sqlStr2, $internalTests2), array($select3, $sqlPrep3, array(), $sqlStr3, $internalTests3), array($select4, $sqlPrep4, array(), $sqlStr4, $internalTests4), array($select5, $sqlPrep5, array(), $sqlStr5, $internalTests5), array($select6, $sqlPrep6, array(), $sqlStr6, $internalTests6), array($select7, $sqlPrep7, array(), $sqlStr7, $internalTests7), array($select8, $sqlPrep8, array(), $sqlStr8, $internalTests8), array($select9, $sqlPrep9, $params9, $sqlStr9, $internalTests9), array($select10, $sqlPrep10, array(), $sqlStr10, $internalTests10), array($select11, $sqlPrep11, array(), $sqlStr11, $internalTests11), array($select12, $sqlPrep12, array(), $sqlStr12, $internalTests12), array($select13, $sqlPrep13, array(), $sqlStr13, $internalTests13), array($select14, $sqlPrep14, array(), $sqlStr14, $internalTests14), array($select15, $sqlPrep15, array(), $sqlStr15, $internalTests15), array($select16, $sqlPrep16, $params16, $sqlStr16, $internalTests16), array($select17, $sqlPrep17, array(), $sqlStr17, $internalTests17), array($select18, $sqlPrep18, array(), $sqlStr18, $internalTests18), array($select19, $sqlPrep19, array(), $sqlStr19, $internalTests19), array($select20, $sqlPrep20, array(), $sqlStr20, $internalTests20), array($select21, $sqlPrep21, $params21, $sqlStr21, $internalTests21), array($select22, $sqlPrep22, array(), $sqlStr22, $internalTests22), array($select23, $sqlPrep23, array(), $sqlStr23, $internalTests23), array($select24, $sqlPrep24, array(), $sqlStr24, $internalTests24), array($select25, $sqlPrep25, array(), $sqlStr25, $internalTests25), array($select26, $sqlPrep26, $params26, $sqlStr26, $internalTests26), array($select27, $sqlPrep27, $params27, $sqlStr27, $internalTests27), array($select28, $sqlPrep28, array(), $sqlStr28, $internalTests28), array($select29, $sqlPrep29, array(), $sqlStr29, $internalTests29), array($select30, $sqlPrep30, array(), $sqlStr30, $internalTests30), array($select31, $sqlPrep31, array(), $sqlStr31, $internalTests31), array($select32, $sqlPrep32, array(), $sqlStr32, $internalTests32), array($select33, $sqlPrep33, array(), $sqlStr33, $internalTests33), array($select34, $sqlPrep34, array(), $sqlStr34, $internalTests34), array($select35, $sqlPrep35, array(), $sqlStr35, $internalTests35), array($select36, $sqlPrep36, array(), $sqlStr36, $internalTests36, $useNamedParams36), array($select37, $sqlPrep37, array(), $sqlStr37, $internalTests37), array($select38, $sqlPrep38, array(), $sqlStr38, $internalTests38), array($select39, $sqlPrep39, array(), $sqlStr39, $internalTests39), array($select40, $sqlPrep40, array(), $sqlStr40, $internalTests40), array($select41, $sqlPrep41, array(), $sqlStr41, $internalTests41), array($select42, $sqlPrep42, array(), $sqlStr42, $internalTests42), array($select43, $sqlPrep43, array(), $sqlStr43, $internalTests43), array($select44, $sqlPrep44, array(), $sqlStr44, $internalTests44), array($select45, $sqlPrep45, $params45, $sqlStr45, $internalTests45), array($select46, $sqlPrep46, $params46, $sqlStr46, $internalTests46), array($select47, $sqlPrep47, $params47, $sqlStr47, $internalTests47), array($select48, $sqlPrep48, array(), $sqlStr48, $internalTests48), array($select49, $sqlPrep49, array(), $sqlStr49, $internalTests49));
 }