zend 2.2 db 应用例子
添加时间:2013-8-8 点击量:
<?php
use Zend\Db\Sql\Select;
// basic table
¥0 = new Select;
¥0->(foo);
// SELECT foo. FROM foo;
// table as TableIdentifier
¥1 = new Select;
¥1->(new TableIdentifier(foo, bar));
// SELECT bar.foo. FROM bar.foo;
// table with alias
¥2 = new Select;
¥2->(array(f => foo));
// SELECT f. FROM foo AS f;
// table with alias with table as TableIdentifier
¥3 = new Select;
¥3->(array(f => new TableIdentifier(foo)));
// SELECT f. FROM foo AS f;
// columns
¥4 = new Select;
¥4->(foo)->columns(array(bar, baz));
// SELECT foo.bar AS bar, foo.baz AS baz FROM foo;
// columns with AS associative array
¥5 = new Select;
¥5->(foo)->columns(array(bar => baz));
// SELECT foo.baz AS bar FROM foo;
// columns with AS associative array mixed
¥6 = new Select;
¥6->(foo)->columns(array(bar => baz, bam));
// SELECT foo.baz AS bar, foo.bam AS bam FROM foo;
// columns where value is Expression, with AS
¥7 = new Select;
¥7->(foo)->columns(array(bar => new Expression(COUNT(some_column))));
// SELECT COUNT(some_column) AS bar FROM foo;
// columns where value is Expression
¥8 = new Select;
¥8->(foo)->columns(array(new Expression(COUNT(some_column) AS bar)));
// SELECT COUNT(some_column) AS bar FROM foo;
// columns where value is Expression with parameters
¥9 = new Select;
¥9->(foo)->columns(
array(
new Expression(
(COUNT(?) + ?) AS ?,
array(some_column, 5, bar),
array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_IDENTIFIER)
)
)
);
// SELECT (COUNT(some_column) + ?) AS bar FROM foo;
// array(column1 => 5);
//
// SELECT (COUNT(some_column) + \5\) AS bar FROM foo;
// joins (plain)
¥10 = new Select;
¥10->(foo)->join(zac, m = n);
// SELECT foo., zac. FROM foo INNER JOIN zac ON m = n;
// join with columns
¥11 = new Select;
¥11->(foo)->join(zac, m = n, array(bar, baz));
// SELECT foo., zac.bar AS bar, zac.baz AS baz FROM foo INNER JOIN zac ON m = n;
// join with alternate type
¥12 = new Select;
¥12->(foo)->join(zac, m = n, array(bar, baz), Select::JOIN_OUTER);
// SELECT foo., zac.bar AS bar, zac.baz AS baz FROM foo OUTER JOIN zac ON m = n;
// join with column aliases
¥13 = new Select;
¥13->(foo)->join(zac, m = n, array(BAR => bar, BAZ => baz));
// SELECT foo., zac.bar AS BAR, zac.baz AS BAZ FROM foo INNER JOIN zac ON m = n;
// join with table aliases
¥14 = new Select;
¥14->(foo)->join(array(b => bar), b.foo_id = foo.foo_id);
// SELECT foo., b. FROM foo INNER JOIN bar AS b ON b.foo_id = foo.foo_id;
// where (simple string)
¥15 = new Select;
¥15->(foo)->where(x = 5);
// SELECT foo. FROM foo WHERE x = 5;
// where (returning parameters)
¥16 = new Select;
¥16->(foo)->where(array(x = ? => 5));
// SELECT foo. FROM foo WHERE x = ?;
// array(where1 => 5);
//
// SELECT foo. FROM foo WHERE x = \5\;
// group
¥17 = new Select;
¥17->(foo)->group(array(col1, col2));
// SELECT foo. FROM foo GROUP BY col1, col2;
¥18 = new Select;
¥18->(foo)->group(col1)->group(col2);
// SELECT foo. FROM foo GROUP BY col1, col2;
¥19 = new Select;
¥19->(foo)->group(new Expression(DAY(?), array(col1), array(Expression::TYPE_IDENTIFIER)));
// SELECT foo. FROM foo GROUP BY DAY(col1);
// having (simple string)
¥20 = new Select;
¥20->(foo)->having(x = 5);
// SELECT foo. FROM foo HAVING x = 5;
// having (returning parameters)
¥21 = new Select;
¥21->(foo)->having(array(x = ? => 5));
// SELECT foo. FROM foo HAVING x = ?;
// array(having1 => 5);
//
// SELECT foo. FROM foo HAVING x = \5\;
// order
¥22 = new Select;
¥22->(foo)->order(c1);
// SELECT foo. FROM foo ORDER BY c1 ASC;
// multiple order parts
¥23 = new Select;
¥23->(foo)->order(array(c1, c2));
// SELECT foo. FROM foo ORDER BY c1 ASC, c2 ASC;
// mulitple order parts
¥24 = new Select;
¥24->(foo)->order(array(c1 => DESC, c2 => Asc)); // notice partially lower case ASC
// SELECT foo. FROM foo ORDER BY c1 DESC, c2 ASC;
¥25 = new Select;
¥25->(foo)->order(array(c1 => asc))->order(c2 desc); // notice partially lower case ASC
// SELECT foo. FROM foo ORDER BY c1 ASC, c2 DESC;
// limit
¥26 = new Select;
¥26->(foo)->limit(5);
// SELECT foo. FROM foo LIMIT ?;
// array(limit => 5);
//
// SELECT foo. FROM foo LIMIT \5\;
// limit with offset
¥27 = new Select;
¥27->(foo)->limit(5)->offset(10);
// SELECT foo. FROM foo LIMIT ? OFFSET ?;
// array(limit => 5, offset => 10);
//
// SELECT foo. FROM foo LIMIT \5\ OFFSET \10\;
// joins with a few keywords in the on clause
¥28 = new Select;
¥28->(foo)->join(zac, (m = n AND c.x) BETWEEN x AND y.z);
// SELECT foo., zac. FROM foo INNER JOIN zac ON (m = n AND c.x) BETWEEN x AND y.z;
// order with compound name
¥29 = new Select;
¥29->(foo)->order(c1.d2);
// SELECT foo. FROM foo ORDER BY c1.d2 ASC;
// group with compound name
¥30 = new Select;
¥30->(foo)->group(c1.d2);
// SELECT foo. FROM foo GROUP BY c1.d2;
// join with expression in ON part
¥31 = new Select;
¥31->(foo)->join(zac, new Expression((m = n AND c.x) BETWEEN x AND y.z));
// SELECT foo., zac. FROM foo INNER JOIN zac ON (m = n AND c.x) BETWEEN x AND y.z;
// subs
¥32sub = new Select;
¥32sub->(bar)->where->like(y, %Foo%);
¥32 = new Select;
¥32->(array(x => ¥32sub));
// SELECT x. FROM (SELECT bar. FROM bar WHERE y LIKE ?) AS x;
// SELECT x. FROM (SELECT bar. FROM bar WHERE y LIKE \%Foo%\) AS x;
// use array in where, predicate in where
¥33 = new Select;
¥33->(table)->columns(array())->where(array(
c1 => null,
c2 => array(1, 2, 3),
new \Zend\Db\Sql\Predicate\IsNotNull(c3)
));
// SELECT table. FROM table WHERE c1 IS NULL AND c2 IN (?, ?, ?) AND c3 IS NOT NULL;
// SELECT table. FROM table WHERE c1 IS NULL AND c2 IN (\1\, \2\, \3\) AND c3 IS NOT NULL;
// Expression objects in order
¥34 = new Select;
¥34->(table)->order(array(
new Expression(isnull(?) DESC, array(name), array(Expression::TYPE_IDENTIFIER)),
name
));
// SELECT table. FROM table ORDER BY isnull(name) DESC, name ASC;
// join with Expression object in COLUMNS part (ZF2-514)
¥35 = new Select;
¥35->(foo)->columns(array())->join(bar, m = n, array(thecount => new Expression(COUNT())));
// SELECT COUNT() AS thecount FROM foo INNER JOIN bar ON m = n;
// multiple joins with expressions
¥36 = new Select;
¥36->(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)
)));
// 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;
//
// 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\;
我们永远不要期待别人的拯救,只有自己才能升华自己。自己已准备好了多少容量,方能吸引对等的人与我们相遇,否则再美好的人出现、再动人的事情降临身边,我们也没有能量去理解与珍惜,终将擦肩而过。—— 姚谦《品味》
<?php
use Zend\Db\Sql\Select;
// basic table
¥0 = new Select;
¥0->(foo);
// SELECT foo. FROM foo;
// table as TableIdentifier
¥1 = new Select;
¥1->(new TableIdentifier(foo, bar));
// SELECT bar.foo. FROM bar.foo;
// table with alias
¥2 = new Select;
¥2->(array(f => foo));
// SELECT f. FROM foo AS f;
// table with alias with table as TableIdentifier
¥3 = new Select;
¥3->(array(f => new TableIdentifier(foo)));
// SELECT f. FROM foo AS f;
// columns
¥4 = new Select;
¥4->(foo)->columns(array(bar, baz));
// SELECT foo.bar AS bar, foo.baz AS baz FROM foo;
// columns with AS associative array
¥5 = new Select;
¥5->(foo)->columns(array(bar => baz));
// SELECT foo.baz AS bar FROM foo;
// columns with AS associative array mixed
¥6 = new Select;
¥6->(foo)->columns(array(bar => baz, bam));
// SELECT foo.baz AS bar, foo.bam AS bam FROM foo;
// columns where value is Expression, with AS
¥7 = new Select;
¥7->(foo)->columns(array(bar => new Expression(COUNT(some_column))));
// SELECT COUNT(some_column) AS bar FROM foo;
// columns where value is Expression
¥8 = new Select;
¥8->(foo)->columns(array(new Expression(COUNT(some_column) AS bar)));
// SELECT COUNT(some_column) AS bar FROM foo;
// columns where value is Expression with parameters
¥9 = new Select;
¥9->(foo)->columns(
array(
new Expression(
(COUNT(?) + ?) AS ?,
array(some_column, 5, bar),
array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_IDENTIFIER)
)
)
);
// SELECT (COUNT(some_column) + ?) AS bar FROM foo;
// array(column1 => 5);
//
// SELECT (COUNT(some_column) + \5\) AS bar FROM foo;
// joins (plain)
¥10 = new Select;
¥10->(foo)->join(zac, m = n);
// SELECT foo., zac. FROM foo INNER JOIN zac ON m = n;
// join with columns
¥11 = new Select;
¥11->(foo)->join(zac, m = n, array(bar, baz));
// SELECT foo., zac.bar AS bar, zac.baz AS baz FROM foo INNER JOIN zac ON m = n;
// join with alternate type
¥12 = new Select;
¥12->(foo)->join(zac, m = n, array(bar, baz), Select::JOIN_OUTER);
// SELECT foo., zac.bar AS bar, zac.baz AS baz FROM foo OUTER JOIN zac ON m = n;
// join with column aliases
¥13 = new Select;
¥13->(foo)->join(zac, m = n, array(BAR => bar, BAZ => baz));
// SELECT foo., zac.bar AS BAR, zac.baz AS BAZ FROM foo INNER JOIN zac ON m = n;
// join with table aliases
¥14 = new Select;
¥14->(foo)->join(array(b => bar), b.foo_id = foo.foo_id);
// SELECT foo., b. FROM foo INNER JOIN bar AS b ON b.foo_id = foo.foo_id;
// where (simple string)
¥15 = new Select;
¥15->(foo)->where(x = 5);
// SELECT foo. FROM foo WHERE x = 5;
// where (returning parameters)
¥16 = new Select;
¥16->(foo)->where(array(x = ? => 5));
// SELECT foo. FROM foo WHERE x = ?;
// array(where1 => 5);
//
// SELECT foo. FROM foo WHERE x = \5\;
// group
¥17 = new Select;
¥17->(foo)->group(array(col1, col2));
// SELECT foo. FROM foo GROUP BY col1, col2;
¥18 = new Select;
¥18->(foo)->group(col1)->group(col2);
// SELECT foo. FROM foo GROUP BY col1, col2;
¥19 = new Select;
¥19->(foo)->group(new Expression(DAY(?), array(col1), array(Expression::TYPE_IDENTIFIER)));
// SELECT foo. FROM foo GROUP BY DAY(col1);
// having (simple string)
¥20 = new Select;
¥20->(foo)->having(x = 5);
// SELECT foo. FROM foo HAVING x = 5;
// having (returning parameters)
¥21 = new Select;
¥21->(foo)->having(array(x = ? => 5));
// SELECT foo. FROM foo HAVING x = ?;
// array(having1 => 5);
//
// SELECT foo. FROM foo HAVING x = \5\;
// order
¥22 = new Select;
¥22->(foo)->order(c1);
// SELECT foo. FROM foo ORDER BY c1 ASC;
// multiple order parts
¥23 = new Select;
¥23->(foo)->order(array(c1, c2));
// SELECT foo. FROM foo ORDER BY c1 ASC, c2 ASC;
// mulitple order parts
¥24 = new Select;
¥24->(foo)->order(array(c1 => DESC, c2 => Asc)); // notice partially lower case ASC
// SELECT foo. FROM foo ORDER BY c1 DESC, c2 ASC;
¥25 = new Select;
¥25->(foo)->order(array(c1 => asc))->order(c2 desc); // notice partially lower case ASC
// SELECT foo. FROM foo ORDER BY c1 ASC, c2 DESC;
// limit
¥26 = new Select;
¥26->(foo)->limit(5);
// SELECT foo. FROM foo LIMIT ?;
// array(limit => 5);
//
// SELECT foo. FROM foo LIMIT \5\;
// limit with offset
¥27 = new Select;
¥27->(foo)->limit(5)->offset(10);
// SELECT foo. FROM foo LIMIT ? OFFSET ?;
// array(limit => 5, offset => 10);
//
// SELECT foo. FROM foo LIMIT \5\ OFFSET \10\;
// joins with a few keywords in the on clause
¥28 = new Select;
¥28->(foo)->join(zac, (m = n AND c.x) BETWEEN x AND y.z);
// SELECT foo., zac. FROM foo INNER JOIN zac ON (m = n AND c.x) BETWEEN x AND y.z;
// order with compound name
¥29 = new Select;
¥29->(foo)->order(c1.d2);
// SELECT foo. FROM foo ORDER BY c1.d2 ASC;
// group with compound name
¥30 = new Select;
¥30->(foo)->group(c1.d2);
// SELECT foo. FROM foo GROUP BY c1.d2;
// join with expression in ON part
¥31 = new Select;
¥31->(foo)->join(zac, new Expression((m = n AND c.x) BETWEEN x AND y.z));
// SELECT foo., zac. FROM foo INNER JOIN zac ON (m = n AND c.x) BETWEEN x AND y.z;
// subs
¥32sub = new Select;
¥32sub->(bar)->where->like(y, %Foo%);
¥32 = new Select;
¥32->(array(x => ¥32sub));
// SELECT x. FROM (SELECT bar. FROM bar WHERE y LIKE ?) AS x;
// SELECT x. FROM (SELECT bar. FROM bar WHERE y LIKE \%Foo%\) AS x;
// use array in where, predicate in where
¥33 = new Select;
¥33->(table)->columns(array())->where(array(
c1 => null,
c2 => array(1, 2, 3),
new \Zend\Db\Sql\Predicate\IsNotNull(c3)
));
// SELECT table. FROM table WHERE c1 IS NULL AND c2 IN (?, ?, ?) AND c3 IS NOT NULL;
// SELECT table. FROM table WHERE c1 IS NULL AND c2 IN (\1\, \2\, \3\) AND c3 IS NOT NULL;
// Expression objects in order
¥34 = new Select;
¥34->(table)->order(array(
new Expression(isnull(?) DESC, array(name), array(Expression::TYPE_IDENTIFIER)),
name
));
// SELECT table. FROM table ORDER BY isnull(name) DESC, name ASC;
// join with Expression object in COLUMNS part (ZF2-514)
¥35 = new Select;
¥35->(foo)->columns(array())->join(bar, m = n, array(thecount => new Expression(COUNT())));
// SELECT COUNT() AS thecount FROM foo INNER JOIN bar ON m = n;
// multiple joins with expressions
¥36 = new Select;
¥36->(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)
)));
// 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;
//
// 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\;
我们永远不要期待别人的拯救,只有自己才能升华自己。自己已准备好了多少容量,方能吸引对等的人与我们相遇,否则再美好的人出现、再动人的事情降临身边,我们也没有能量去理解与珍惜,终将擦肩而过。—— 姚谦《品味》