1: <?php /* vim: set tabstop=4 expandtab : */
 2: class SelectTest extends AMysql_TestCase {
 3: 
 4:     public function testComplexQuery()
 5:     {
 6:         $select = $this->_amysql->select('t2alias.*');
 7:         $select 
 8:             ->option('DISTINCT')
 9:             ->from(array ('table1', 't2alias' => 'table2'))
10:             ->from(array ('t3alias' => 'table3'), array ('t3_col1' => 'col1', 't3_col2' => 'col2'))
11:             ->column (array ('t1_col1' => 'table1.col1'))
12:             ->columnLiteral('table7, table8, CURRENT_TIMESTAMP AS ctimestamp')
13:             ->join(
14:                 '',
15:                 array ('t4alias' => 'table4'),
16:                 't4alias.t1_id = table1.id',
17:                 array ('t4lol', 't4lol2aliased' => 't4lol2')
18:             )
19:             ->join('left', array ('table5'), 't2alias.colx = table5.coly', array (), true)
20:             ->join('cross', array ('table6'), 't3alias.colx = table6.coly', array ())
21:             ->groupByLiteral('t2alias.col1')
22:             ->groupBy('t2alias.col2', true, true)
23:             ->groupBy('t2alias.col3', true)
24:             ->having('1 = 1')
25:             ->having('2 = 2')
26:             ->orderByLiteral('t3alias.col1')
27:             ->orderBy('t3alias.col2', true, true)
28:             ->orderBy('t3alias.col3', true)
29:             ->where('3 = :where3')
30:             ->where('4 = 4')
31:             ->limit(100)
32:             ->offset(200)
33:         ;
34:         $unboundSql = $select->getUnboundSql();
35:         $expected = 'SELECT DISTINCT ' .
36:             '`t2alias`.*, `t3alias`.`col1` AS `t3_col1`, `t3alias`.`col2` AS `t3_col2`, '
37:             . '`table1`.`col1` AS `t1_col1`, `t4alias`.`t4lol`, ' .
38:             '`t4alias`.`t4lol2` AS `t4lol2aliased`, ' .
39:             'table7, table8, CURRENT_TIMESTAMP AS ctimestamp' . "\n" .
40:             'FROM `table1`, `table2` AS `t2alias`, `table3` AS `t3alias`' . "\n" .
41:             'LEFT JOIN `table5` ON (t2alias.colx = table5.coly)' . "\n" .
42:             'JOIN `table4` AS `t4alias` ON (t4alias.t1_id = table1.id)' . "\n" .
43:             'CROSS JOIN `table6` ON (t3alias.colx = table6.coly)' . "\n" .
44:             'WHERE 3 = :where3 AND 4 = 4' . "\n" .
45:             'GROUP BY `t2alias`.`col2` DESC, t2alias.col1, `t2alias`.`col3` DESC' . "\n" .
46:             'HAVING 1 = 1 AND 2 = 2' . "\n" .
47:             'ORDER BY `t3alias`.`col2` DESC, t3alias.col1, `t3alias`.`col3` DESC' . "\n" .
48:             'LIMIT 100' . "\n" .
49:             'OFFSET 200'
50:         ;
51:         $this->assertEquals($expected, $unboundSql);
52:     }
53: 
54:     public function testBlankQuery()
55:     {
56:         $select = $this->_amysql->select();
57:         $unboundSql = $select->getUnboundSql();
58:         $expected = 'SELECT ';
59:         $this->assertEquals($expected, $unboundSql);
60:     }
61: 
62:     public function testSelectExecute()
63:     {
64:         $data = array (
65:             array (
66:                 'string' => 3
67:             ),
68:             array (
69:                 'string' => 'blah',
70:             )
71:         );
72:         $this->_amysql->insert($this->tableName, $data);
73: 
74:         $select = $this->_amysql->select();
75:         $select
76:             ->column('*')
77:             ->from($this->tableName)
78:             ->whereBind('string = :string', 'string', 'blah');
79:         $select->execute();
80:         $rows = $select->fetchAllAssoc();
81:         $expected = array (
82:             array (
83:                 'id' => '2',
84:                 'string' => 'blah',
85:             )
86:         );
87:         $this->assertEquals($expected, $rows);
88:     }
89: }
90: ?>
91: 
92: 
93: