<？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\;