PageRenderTime 42ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 0ms

/tests/Relation/OrderByTestCase.php

https://github.com/robo47/doctrine1
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. * Doctrine_Relation_OrderBy_TestCase
  23. *
  24. * @package Doctrine
  25. * @author Konsta Vesterinen <kvesteri@cc.hut.fi>
  26. * @license http://www.opensource.org/licenses/lgpl-license.php LGPL
  27. * @category Object Relational Mapping
  28. * @link www.doctrine-project.org
  29. * @since 1.0
  30. * @version $Revision$
  31. */
  32. class Doctrine_Relation_OrderBy_TestCase extends Doctrine_UnitTestCase
  33. {
  34. public function prepareTables()
  35. {
  36. $this->profiler = new Doctrine_Connection_Profiler();
  37. $this->conn->addListener($this->profiler);
  38. $this->tables[] = 'OrderByTest_Article';
  39. $this->tables[] = 'OrderByTest_Friend';
  40. $this->tables[] = 'OrderByTest_Group';
  41. $this->tables[] = 'OrderByTest_User';
  42. $this->tables[] = 'OrderByTest_UserGroup';
  43. $this->tables[] = 'OrderByTest_Category';
  44. $this->tables[] = 'OrderByTest_BlogPost';
  45. parent::prepareTables();
  46. }
  47. public function testFullDqlQuery()
  48. {
  49. $userTable = Doctrine::getTable('OrderByTest_User');
  50. $q = $userTable
  51. ->createQuery('u')
  52. ->select('u.id')
  53. ->leftJoin('u.Articles a')
  54. ->leftJoin('u.Groups g')
  55. ->leftJoin('u.Friends f')
  56. ->leftJoin('u.ChildrenUsers cu')
  57. ->leftJoin('u.ParentUser pu');
  58. $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');
  59. }
  60. public function testLazyLoadingQueries()
  61. {
  62. $userTable = Doctrine::getTable('OrderByTest_User');
  63. $this->assertEqual($userTable->getRelation('Articles')->getRelationDql(1), 'FROM OrderByTest_Article WHERE OrderByTest_Article.user_id IN (?) ORDER BY OrderByTest_Article.title ASC');
  64. $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');
  65. $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');
  66. $this->assertEqual($userTable->getRelation('ParentUser')->getRelationDql(1), 'FROM OrderByTest_User WHERE OrderByTest_User.id IN (?) ORDER BY OrderByTest_User.id ASC');
  67. $this->assertEqual($userTable->getRelation('ChildrenUsers')->getRelationDql(1), 'FROM OrderByTest_User WHERE OrderByTest_User.parent_user_id IN (?) ORDER BY OrderByTest_User.username ASC');
  68. $user = new OrderByTest_User();
  69. $user->username = 'jwage';
  70. $user->password = 'changeme';
  71. $user2 = new OrderByTest_User();
  72. $user2->username = 'parent';
  73. $user2->password = 'changeme';
  74. $user->ParentUser = $user2;
  75. $user->save();
  76. $articles = $user->Articles;
  77. $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');
  78. $groups = $user->Groups;
  79. $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');
  80. $friends = $user->Friends;
  81. $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');
  82. $childrenUsers = $user->ChildrenUsers;
  83. $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');
  84. $parentUser = $user->ParentUser;
  85. $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');
  86. }
  87. public function testMasterOrderBy()
  88. {
  89. $q = Doctrine::getTable('OrderByTest_Category')
  90. ->createQuery('c')
  91. ->select('c.id, p.id')
  92. ->leftJoin('c.Posts p');
  93. $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');
  94. $category = new OrderByTest_Category();
  95. $category->name = 'php';
  96. $category->save();
  97. $posts = $category->Posts;
  98. $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');
  99. }
  100. public function testOrderByFromQueryComesFirst()
  101. {
  102. $q = Doctrine::getTable('OrderByTest_Category')
  103. ->createQuery('c')
  104. ->select('c.id, p.id')
  105. ->leftJoin('c.Posts p')
  106. ->orderBy('p.title ASC');
  107. $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');
  108. }
  109. public function testWeirdSort()
  110. {
  111. $q = Doctrine::getTable('OrderByTest_WeirdSort')
  112. ->createQuery('w')
  113. ->select('w.id');
  114. $this->assertEqual($q->getSqlQuery(), 'SELECT o.id AS o__id FROM order_by_test__weird_sort o ORDER BY RAND()');
  115. }
  116. }
  117. class OrderByTest_Article extends Doctrine_Record
  118. {
  119. public function setTableDefinition()
  120. {
  121. $this->hasColumn('title', 'string', 255);
  122. $this->hasColumn('content', 'clob');
  123. $this->hasColumn('user_id', 'integer');
  124. }
  125. public function setUp()
  126. {
  127. $this->hasOne('OrderByTest_User as User', array(
  128. 'local' => 'user_id',
  129. 'foreign' => 'id'));
  130. }
  131. }
  132. class OrderByTest_Friend extends Doctrine_Record
  133. {
  134. public function setTableDefinition()
  135. {
  136. $this->hasColumn('user_id1', 'integer', null, array(
  137. 'primary' => true,
  138. ));
  139. $this->hasColumn('user_id2', 'integer', null, array(
  140. 'primary' => true,
  141. ));
  142. }
  143. }
  144. class OrderByTest_Group extends Doctrine_Record
  145. {
  146. public function setTableDefinition()
  147. {
  148. $this->hasColumn('name', 'string', 255, array(
  149. 'type' => 'string',
  150. 'length' => '255',
  151. ));
  152. }
  153. public function setUp()
  154. {
  155. $this->hasMany('OrderByTest_User as User', array(
  156. 'refClass' => 'OrderByTest_UserGroup',
  157. 'local' => 'group_id',
  158. 'foreign' => 'user_id'));
  159. }
  160. }
  161. class OrderByTest_User extends Doctrine_Record
  162. {
  163. public function setTableDefinition()
  164. {
  165. $this->hasColumn('login AS username', 'string', 255);
  166. $this->hasColumn('password', 'string', 255);
  167. $this->hasColumn('parent_user_id', 'integer');
  168. }
  169. public function setUp()
  170. {
  171. $this->hasMany('OrderByTest_Article as Articles', array(
  172. 'local' => 'id',
  173. 'foreign' => 'user_id',
  174. 'orderBy' => 'title ASC'));
  175. $this->hasMany('OrderByTest_Group as Groups', array(
  176. 'refClass' => 'OrderByTest_UserGroup',
  177. 'local' => 'user_id',
  178. 'foreign' => 'group_id',
  179. 'orderBy' => 'name ASC'));
  180. $this->hasMany('OrderByTest_User as Friends', array(
  181. 'refClass' => 'OrderByTest_Friend',
  182. 'local' => 'user_id1',
  183. 'foreign' => 'user_id2',
  184. 'equal' => true,
  185. 'orderBy' => 'username ASC'));
  186. $this->hasOne('OrderByTest_User as ParentUser', array(
  187. 'local' => 'parent_user_id',
  188. 'foreign' => 'id',
  189. 'orderBy' => 'id ASC'));
  190. $this->hasMany('OrderByTest_User as ChildrenUsers', array(
  191. 'local' => 'id',
  192. 'foreign' => 'parent_user_id',
  193. 'orderBy' => 'username ASC'));
  194. }
  195. }
  196. class OrderByTest_UserGroup extends Doctrine_Record
  197. {
  198. public function setTableDefinition()
  199. {
  200. $this->hasColumn('user_id', 'integer', null, array(
  201. 'primary' => true,
  202. ));
  203. $this->hasColumn('group_id', 'integer', null, array(
  204. 'primary' => true,
  205. ));
  206. }
  207. }
  208. class OrderByTest_Category extends Doctrine_Record
  209. {
  210. public function setTableDefinition()
  211. {
  212. $this->option('orderBy', 'name ASC');
  213. $this->hasColumn('name', 'string', 255);
  214. }
  215. public function setUp()
  216. {
  217. $this->hasMany('OrderByTest_BlogPost as Posts', array(
  218. 'local' => 'id',
  219. 'foreign' => 'category_id'
  220. ));
  221. }
  222. }
  223. class OrderByTest_BlogPost extends Doctrine_Record
  224. {
  225. public function setTableDefinition()
  226. {
  227. $this->option('orderBy', 'title ASC, is_first DESC');
  228. $this->hasColumn('title', 'string', 255);
  229. $this->hasColumn('is_first', 'boolean');
  230. $this->hasColumn('category_id', 'integer');
  231. }
  232. public function setUp()
  233. {
  234. $this->hasOne('OrderByTest_Category', array(
  235. 'local' => 'category_id',
  236. 'foreign' => 'id'
  237. ));
  238. }
  239. }
  240. class OrderByTest_WeirdSort extends Doctrine_Record
  241. {
  242. public function setTableDefinition()
  243. {
  244. $this->option('orderBy', 'RAND()');
  245. $this->hasColumn('title', 'string', 255);
  246. }
  247. }