Skip to content

Instantly share code, notes, and snippets.

@tashik

tashik/example.php

Forked from ralphschindler/example.php
Created May 31, 2016 14:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tashik/93de18d95b34a21967b7029b02e9168f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tashik/93de18d95b34a21967b7029b02e9168f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ralphschindler ralphschindler created this gist Oct 24, 2012.
    266 changes: 266 additions & 0 deletions example.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,266 @@
    <?php

    use Zend\Db\Sql\Select;

    // basic table
    $select0 = new Select;
    $select0->from('foo');
    // 'SELECT "foo".* FROM "foo"';


    // table as TableIdentifier
    $select1 = new Select;
    $select1->from(new TableIdentifier('foo', 'bar'));
    // 'SELECT "bar"."foo".* FROM "bar"."foo"';


    // table with alias
    $select2 = new Select;
    $select2->from(array('f' => 'foo'));
    // 'SELECT "f".* FROM "foo" AS "f"';


    // table with alias with table as TableIdentifier
    $select3 = new Select;
    $select3->from(array('f' => new TableIdentifier('foo')));
    // 'SELECT "f".* FROM "foo" AS "f"';


    // columns
    $select4 = new Select;
    $select4->from('foo')->columns(array('bar', 'baz'));
    // 'SELECT "foo"."bar" AS "bar", "foo"."baz" AS "baz" FROM "foo"';


    // columns with AS associative array
    $select5 = new Select;
    $select5->from('foo')->columns(array('bar' => 'baz'));
    // 'SELECT "foo"."baz" AS "bar" FROM "foo"';


    // columns with AS associative array mixed
    $select6 = new Select;
    $select6->from('foo')->columns(array('bar' => 'baz', 'bam'));
    // 'SELECT "foo"."baz" AS "bar", "foo"."bam" AS "bam" FROM "foo"';


    // columns where value is Expression, with AS
    $select7 = new Select;
    $select7->from('foo')->columns(array('bar' => new Expression('COUNT(some_column)')));
    // 'SELECT COUNT(some_column) AS "bar" FROM "foo"';


    // columns where value is Expression
    $select8 = new Select;
    $select8->from('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
    $select9 = new Select;
    $select9->from('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)
    $select10 = new Select;
    $select10->from('foo')->join('zac', 'm = n');
    // 'SELECT "foo".*, "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n"';


    // join with columns
    $select11 = new Select;
    $select11->from('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
    $select12 = new Select;
    $select12->from('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
    $select13 = new Select;
    $select13->from('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
    $select14 = new Select;
    $select14->from('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)
    $select15 = new Select;
    $select15->from('foo')->where('x = 5');
    // 'SELECT "foo".* FROM "foo" WHERE x = 5';


    // where (returning parameters)
    $select16 = new Select;
    $select16->from('foo')->where(array('x = ?' => 5));
    // 'SELECT "foo".* FROM "foo" WHERE x = ?';
    // array('where1' => 5);
    //
    // 'SELECT "foo".* FROM "foo" WHERE x = \'5\'';


    // group
    $select17 = new Select;
    $select17->from('foo')->group(array('col1', 'col2'));
    // 'SELECT "foo".* FROM "foo" GROUP BY "col1", "col2"';


    $select18 = new Select;
    $select18->from('foo')->group('col1')->group('col2');
    // 'SELECT "foo".* FROM "foo" GROUP BY "col1", "col2"';


    $select19 = new Select;
    $select19->from('foo')->group(new Expression('DAY(?)', array('col1'), array(Expression::TYPE_IDENTIFIER)));
    // 'SELECT "foo".* FROM "foo" GROUP BY DAY("col1")';


    // having (simple string)
    $select20 = new Select;
    $select20->from('foo')->having('x = 5');
    // 'SELECT "foo".* FROM "foo" HAVING x = 5';


    // having (returning parameters)
    $select21 = new Select;
    $select21->from('foo')->having(array('x = ?' => 5));
    // 'SELECT "foo".* FROM "foo" HAVING x = ?';
    // array('having1' => 5);
    //
    // 'SELECT "foo".* FROM "foo" HAVING x = \'5\'';


    // order
    $select22 = new Select;
    $select22->from('foo')->order('c1');
    // 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC';


    // multiple order parts
    $select23 = new Select;
    $select23->from('foo')->order(array('c1', 'c2'));
    // 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC, "c2" ASC';


    // mulitple order parts
    $select24 = new Select;
    $select24->from('foo')->order(array('c1' => 'DESC', 'c2' => 'Asc')); // notice partially lower case ASC
    // 'SELECT "foo".* FROM "foo" ORDER BY "c1" DESC, "c2" ASC';


    $select25 = new Select;
    $select25->from('foo')->order(array('c1' => 'asc'))->order('c2 desc'); // notice partially lower case ASC
    // 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC, "c2" DESC';


    // limit
    $select26 = new Select;
    $select26->from('foo')->limit(5);
    // 'SELECT "foo".* FROM "foo" LIMIT ?';
    // array('limit' => 5);
    //
    // 'SELECT "foo".* FROM "foo" LIMIT \'5\'';


    // limit with offset
    $select27 = new Select;
    $select27->from('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
    $select28 = new Select;
    $select28->from('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
    $select29 = new Select;
    $select29->from('foo')->order('c1.d2');
    // 'SELECT "foo".* FROM "foo" ORDER BY "c1"."d2" ASC';


    // group with compound name
    $select30 = new Select;
    $select30->from('foo')->group('c1.d2');
    // 'SELECT "foo".* FROM "foo" GROUP BY "c1"."d2"';


    // join with expression in ON part
    $select31 = new Select;
    $select31->from('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';


    // subselects
    $select32subselect = new Select;
    $select32subselect->from('bar')->where->like('y', '%Foo%');
    $select32 = new Select;
    $select32->from(array('x' => $select32subselect));
    // '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
    $select33 = new Select;
    $select33->from('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
    $select34 = new Select;
    $select34->from('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)
    $select35 = new Select;
    $select35->from('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
    $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)
    )));
    // '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\'';