/tests/Relation/OrderByTestCase.php
PHP | 290 lines | 217 code | 43 blank | 30 comment | 0 complexity | 9f78199a0d02b05c12d4387165c10f52 MD5 | raw file
1<?php
2/*
3 * $Id$
4 *
5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
16 *
17 * This software consists of voluntary contributions made by many individuals
18 * and is licensed under the LGPL. For more information, see
19 * <http://www.doctrine-project.org>.
20 */
21
22/**
23 * Doctrine_Relation_OrderBy_TestCase
24 *
25 * @package Doctrine
26 * @author Konsta Vesterinen <kvesteri@cc.hut.fi>
27 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL
28 * @category Object Relational Mapping
29 * @link www.doctrine-project.org
30 * @since 1.0
31 * @version $Revision$
32 */
33class Doctrine_Relation_OrderBy_TestCase extends Doctrine_UnitTestCase
34{
35 public function prepareTables()
36 {
37 $this->profiler = new Doctrine_Connection_Profiler();
38 $this->conn->addListener($this->profiler);
39
40 $this->tables[] = 'OrderByTest_Article';
41 $this->tables[] = 'OrderByTest_Friend';
42 $this->tables[] = 'OrderByTest_Group';
43 $this->tables[] = 'OrderByTest_User';
44 $this->tables[] = 'OrderByTest_UserGroup';
45 $this->tables[] = 'OrderByTest_Category';
46 $this->tables[] = 'OrderByTest_BlogPost';
47 parent::prepareTables();
48 }
49
50 public function testFullDqlQuery()
51 {
52 $userTable = Doctrine::getTable('OrderByTest_User');
53 $q = $userTable
54 ->createQuery('u')
55 ->select('u.id')
56 ->leftJoin('u.Articles a')
57 ->leftJoin('u.Groups g')
58 ->leftJoin('u.Friends f')
59 ->leftJoin('u.ChildrenUsers cu')
60 ->leftJoin('u.ParentUser pu');
61
62 $this->assertEqual($q->getSqlQuery(), 'SELECT o.id AS o__id FROM order_by_test__user o LEFT JOIN order_by_test__article o2 ON o.id = o2.user_id LEFT JOIN order_by_test__user_group o4 ON (o.id = o4.user_id) LEFT JOIN order_by_test__group o3 ON o3.id = o4.group_id LEFT JOIN order_by_test__friend o6 ON (o.id = o6.user_id1 OR o.id = o6.user_id2) LEFT JOIN order_by_test__user o5 ON (o5.id = o6.user_id2 OR o5.id = o6.user_id1) AND o5.id != o.id LEFT JOIN order_by_test__user o7 ON o.id = o7.parent_user_id LEFT JOIN order_by_test__user o8 ON o.parent_user_id = o8.id ORDER BY o2.title ASC, o3.name ASC, o4.name ASC, o5.login ASC, o6.login ASC, o7.login ASC, o8.id ASC');
63 }
64
65 public function testLazyLoadingQueries()
66 {
67 $userTable = Doctrine::getTable('OrderByTest_User');
68
69 $this->assertEqual($userTable->getRelation('Articles')->getRelationDql(1), 'FROM OrderByTest_Article WHERE OrderByTest_Article.user_id IN (?) ORDER BY OrderByTest_Article.title ASC');
70 $this->assertEqual($userTable->getRelation('Groups')->getRelationDql(1), 'FROM OrderByTest_Group.OrderByTest_UserGroup WHERE OrderByTest_Group.OrderByTest_UserGroup.user_id IN (?) ORDER BY OrderByTest_Group.name ASC');
71 $this->assertEqual($userTable->getRelation('Friends')->getRelationDql(1), 'FROM OrderByTest_User.OrderByTest_Friend WHERE OrderByTest_User.OrderByTest_Friend.user_id1 IN (?) ORDER BY OrderByTest_User.username ASC');
72 $this->assertEqual($userTable->getRelation('ParentUser')->getRelationDql(1), 'FROM OrderByTest_User WHERE OrderByTest_User.id IN (?) ORDER BY OrderByTest_User.id ASC');
73 $this->assertEqual($userTable->getRelation('ChildrenUsers')->getRelationDql(1), 'FROM OrderByTest_User WHERE OrderByTest_User.parent_user_id IN (?) ORDER BY OrderByTest_User.username ASC');
74
75 $user = new OrderByTest_User();
76 $user->username = 'jwage';
77 $user->password = 'changeme';
78
79 $user2 = new OrderByTest_User();
80 $user2->username = 'parent';
81 $user2->password = 'changeme';
82 $user->ParentUser = $user2;
83 $user->save();
84
85 $articles = $user->Articles;
86 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT o.id AS o__id, o.title AS o__title, o.content AS o__content, o.user_id AS o__user_id FROM order_by_test__article o WHERE (o.user_id IN (?)) ORDER BY o.title ASC');
87
88 $groups = $user->Groups;
89 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT o.id AS o__id, o.name AS o__name, o2.user_id AS o2__user_id, o2.group_id AS o2__group_id FROM order_by_test__group o LEFT JOIN order_by_test__user_group o2 ON o.id = o2.group_id WHERE (o2.user_id IN (?)) ORDER BY o.name ASC');
90
91 $friends = $user->Friends;
92 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT order_by_test__user.id AS order_by_test__user__id, order_by_test__user.login AS order_by_test__user__login, order_by_test__user.password AS order_by_test__user__password, order_by_test__user.parent_user_id AS order_by_test__user__parent_user_id, order_by_test__friend.user_id1 AS order_by_test__friend__user_id1, order_by_test__friend.user_id2 AS order_by_test__friend__user_id2 FROM order_by_test__user INNER JOIN order_by_test__friend ON order_by_test__user.id = order_by_test__friend.user_id2 OR order_by_test__user.id = order_by_test__friend.user_id1 WHERE order_by_test__user.id IN (SELECT user_id2 FROM order_by_test__friend WHERE user_id1 = ?) OR order_by_test__user.id IN (SELECT user_id1 FROM order_by_test__friend WHERE user_id2 = ?) ORDER BY order_by_test__user.login ASC');
93
94 $childrenUsers = $user->ChildrenUsers;
95 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT o.id AS o__id, o.login AS o__login, o.password AS o__password, o.parent_user_id AS o__parent_user_id FROM order_by_test__user o WHERE (o.parent_user_id IN (?)) ORDER BY o.login ASC');
96
97 $parentUser = $user->ParentUser;
98 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT o.id AS o__id, o.login AS o__login, o.password AS o__password, o.parent_user_id AS o__parent_user_id FROM order_by_test__user o WHERE (o.parent_user_id IN (?)) ORDER BY o.login ASC');
99 }
100
101 public function testMasterOrderBy()
102 {
103 $q = Doctrine::getTable('OrderByTest_Category')
104 ->createQuery('c')
105 ->select('c.id, p.id')
106 ->leftJoin('c.Posts p');
107
108 $this->assertEqual($q->getSqlQuery(), 'SELECT o.id AS o__id, o2.id AS o2__id FROM order_by_test__category o LEFT JOIN order_by_test__blog_post o2 ON o.id = o2.category_id ORDER BY o.name ASC, o2.title ASC, o2.is_first DESC');
109
110 $category = new OrderByTest_Category();
111 $category->name = 'php';
112 $category->save();
113
114 $posts = $category->Posts;
115
116 $this->assertEqual($this->profiler->pop()->getQuery(), 'SELECT o.id AS o__id, o.title AS o__title, o.is_first AS o__is_first, o.category_id AS o__category_id FROM order_by_test__blog_post o WHERE (o.category_id IN (?)) ORDER BY o.title ASC, o.is_first DESC');
117 }
118
119 public function testOrderByFromQueryComesFirst()
120 {
121 $q = Doctrine::getTable('OrderByTest_Category')
122 ->createQuery('c')
123 ->select('c.id, p.id')
124 ->leftJoin('c.Posts p')
125 ->orderBy('p.title ASC');
126
127 $this->assertEqual($q->getSqlQuery(), 'SELECT o.id AS o__id, o2.id AS o2__id FROM order_by_test__category o LEFT JOIN order_by_test__blog_post o2 ON o.id = o2.category_id ORDER BY o2.title ASC, o.name ASC, o2.is_first DESC');
128 }
129
130 public function testWeirdSort()
131 {
132 $q = Doctrine::getTable('OrderByTest_WeirdSort')
133 ->createQuery('w')
134 ->select('w.id');
135
136 $this->assertEqual($q->getSqlQuery(), 'SELECT o.id AS o__id FROM order_by_test__weird_sort o ORDER BY RAND()');
137 }
138}
139
140class OrderByTest_Article extends Doctrine_Record
141{
142 public function setTableDefinition()
143 {
144 $this->hasColumn('title', 'string', 255);
145 $this->hasColumn('content', 'clob');
146 $this->hasColumn('user_id', 'integer');
147 }
148
149 public function setUp()
150 {
151 $this->hasOne('OrderByTest_User as User', array(
152 'local' => 'user_id',
153 'foreign' => 'id'));
154 }
155}
156
157class OrderByTest_Friend extends Doctrine_Record
158{
159 public function setTableDefinition()
160 {
161 $this->hasColumn('user_id1', 'integer', null, array(
162 'primary' => true,
163 ));
164 $this->hasColumn('user_id2', 'integer', null, array(
165 'primary' => true,
166 ));
167 }
168}
169
170class OrderByTest_Group extends Doctrine_Record
171{
172 public function setTableDefinition()
173 {
174 $this->hasColumn('name', 'string', 255, array(
175 'type' => 'string',
176 'length' => '255',
177 ));
178 }
179
180 public function setUp()
181 {
182 $this->hasMany('OrderByTest_User as User', array(
183 'refClass' => 'OrderByTest_UserGroup',
184 'local' => 'group_id',
185 'foreign' => 'user_id'));
186 }
187}
188
189class OrderByTest_User extends Doctrine_Record
190{
191 public function setTableDefinition()
192 {
193 $this->hasColumn('login AS username', 'string', 255);
194 $this->hasColumn('password', 'string', 255);
195 $this->hasColumn('parent_user_id', 'integer');
196 }
197
198 public function setUp()
199 {
200 $this->hasMany('OrderByTest_Article as Articles', array(
201 'local' => 'id',
202 'foreign' => 'user_id',
203 'orderBy' => 'title ASC'));
204
205 $this->hasMany('OrderByTest_Group as Groups', array(
206 'refClass' => 'OrderByTest_UserGroup',
207 'local' => 'user_id',
208 'foreign' => 'group_id',
209 'orderBy' => 'name ASC'));
210
211 $this->hasMany('OrderByTest_User as Friends', array(
212 'refClass' => 'OrderByTest_Friend',
213 'local' => 'user_id1',
214 'foreign' => 'user_id2',
215 'equal' => true,
216 'orderBy' => 'username ASC'));
217
218 $this->hasOne('OrderByTest_User as ParentUser', array(
219 'local' => 'parent_user_id',
220 'foreign' => 'id',
221 'orderBy' => 'id ASC'));
222
223 $this->hasMany('OrderByTest_User as ChildrenUsers', array(
224 'local' => 'id',
225 'foreign' => 'parent_user_id',
226 'orderBy' => 'username ASC'));
227 }
228}
229
230class OrderByTest_UserGroup extends Doctrine_Record
231{
232 public function setTableDefinition()
233 {
234 $this->hasColumn('user_id', 'integer', null, array(
235 'primary' => true,
236 ));
237 $this->hasColumn('group_id', 'integer', null, array(
238 'primary' => true,
239 ));
240 }
241}
242
243
244class OrderByTest_Category extends Doctrine_Record
245{
246 public function setTableDefinition()
247 {
248 $this->option('orderBy', 'name ASC');
249
250 $this->hasColumn('name', 'string', 255);
251 }
252
253 public function setUp()
254 {
255 $this->hasMany('OrderByTest_BlogPost as Posts', array(
256 'local' => 'id',
257 'foreign' => 'category_id'
258 ));
259 }
260}
261
262class OrderByTest_BlogPost extends Doctrine_Record
263{
264 public function setTableDefinition()
265 {
266 $this->option('orderBy', 'title ASC, is_first DESC');
267
268 $this->hasColumn('title', 'string', 255);
269 $this->hasColumn('is_first', 'boolean');
270 $this->hasColumn('category_id', 'integer');
271 }
272
273 public function setUp()
274 {
275 $this->hasOne('OrderByTest_Category', array(
276 'local' => 'category_id',
277 'foreign' => 'id'
278 ));
279 }
280}
281
282class OrderByTest_WeirdSort extends Doctrine_Record
283{
284 public function setTableDefinition()
285 {
286 $this->option('orderBy', 'RAND()');
287
288 $this->hasColumn('title', 'string', 255);
289 }
290}