} } }

    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(arraynew 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)->joinarray(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\;


    我们永远不要期待别人的拯救,只有自己才能升华自己。自己已准备好了多少容量,方能吸引对等的人与我们相遇,否则再美好的人出现、再动人的事情降临身边,我们也没有能量去理解与珍惜,终将擦肩而过。—— 姚谦《品味》
    分享到: