3 namespace RedUNIT\Base;
5 use RedUNIT\Base as Base;
6 use RedBeanPHP\Facade as R;
7 use RedBeanPHP\OODBBean as OODBBean;
12 * Tests the @joined keyword, this keyword in an SQL snippet
13 * allows you to join another table and use one or more of its columns
14 * in the query snippet, for instance for sorting or filtering.
16 * @file RedUNIT/Base/Joins.php
17 * @desc Tests joins in ownLists and trees.
18 * @author Gabor de Mooij and the RedBeanPHP Community
19 * @license New BSD/GPLv2
21 * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
22 * This source file is subject to the New BSD/GPLv2 License that is bundled
23 * with this source code in the file license.txt.
25 class Joins extends Base
29 * Tests joins with ownCount().
31 public function testJoinsInCount()
34 $author = R::dispense( 'author' );
35 $book = R::dispense( 'book' );
36 $info = R::dispense( 'info' );
38 $author->xownBookList[] = $book;
40 $book = R::dispense( 'book' );
41 $info = R::dispense( 'info' );
43 $author->xownBookList[] = $book;
46 $author = $author->fresh();
47 $books = $author->withCondition(' @joined.info.title != ? ', array('x'))->countOwn('book');
49 $books = $author->withCondition(' @joined.info.title != ? ', array('y'))->countOwn('book');
51 $books = $author->withCondition(' @joined.info.title IN (?,?) ', array('x','y'))->countOwn('book');
60 public function testJoins()
63 list($a1, $a2, $a3) = R::dispense('area', 3);
64 list($p1, $p2) = R::dispense('person', 2);
65 list($v1, $v2, $v3, $v4) = R::dispense('visit', 4);
66 $a1->name = 'Belgium';
77 $v1->label = 'v1 to France';
78 $v2->label = 'v2 to Arabia';
79 $v3->label = 'v3 to Arabia';
80 $v4->label = 'v4 to Belgium';
81 R::storeAll( array($v1,$v2,$v3,$v4) );
82 $visits = $p1->ownVisit;
83 asrt( is_array( $visits ), TRUE );
84 asrt( count( $visits ), 2 );
86 foreach( $visits as $visit ) {
87 asrt( isset( $visit->label ), TRUE );
88 asrt( isset( $visit->name ), FALSE );
89 asrt( isset( $visit->visit_id ), FALSE );
90 $names[] = $visit->label;
92 $labelList = implode( ',', $names );
93 asrt( $labelList, 'v1 to France,v2 to Arabia' );
95 ->with('ORDER BY @joined.area.name ASC')->ownVisit;
96 asrt( is_array( $visits ), TRUE );
97 asrt( count( $visits ), 2 );
99 foreach( $visits as $visit ) {
100 asrt( isset( $visit->label ), TRUE );
101 asrt( isset( $visit->name ), FALSE );
102 asrt( isset( $visit->visit_id ), FALSE );
103 $names[] = $visit->label;
105 $labelList = implode( ',', $names );
106 asrt( $labelList, 'v2 to Arabia,v1 to France' );
110 * Helper for the next test.
112 * @param array $books the books we are going to check
113 * @param string $numberList the numbers that are expected
117 private function checkBookNumbers( $books, $numberList )
119 $numbers = explode( ',', $numberList );
120 asrt( is_array( $books ), TRUE );
121 asrt( count( $books ), count( $numbers ) );
123 $bookNumberArray = array();
124 foreach( $books as $book ) {
125 asrt( isset( $book->num ), TRUE );
126 asrt( isset( $book->title), FALSE );
127 $bookNumberArray[] = $book->num;
129 $bookNumbers = implode( ',', $bookNumberArray);
130 asrt( $bookNumbers, $numberList );
134 * Tests the more complicated scenarios for
139 private function testComplexCombinationsJoins()
141 $author = R::dispense( 'author' );
142 $books = R::dispense( 'book', 4 );
147 $books[0]->info = R::dispense('info')->setAttr('title', 'Learning PHP');
148 $books[1]->info = R::dispense('info')->setAttr('title', 'Learning PHP and JavaScript');
149 $books[2]->info = R::dispense('info')->setAttr('title', 'Learning Cobol');
150 $books[3]->info = R::dispense('info')->setAttr('title','Gardening for Beginners');
151 $books[0]->category = R::dispense('category')->setAttr('title', 'computers');
152 $books[1]->category = R::dispense('category')->setAttr('title', 'computers');
153 $books[2]->category = R::dispense('category')->setAttr('title', 'computers');
154 $books[3]->category = R::dispense('category')->setAttr('title','gardening');
155 $author->ownBookList = $books;
158 $books = $author->ownBookList;
159 $this->checkBookNumbers( $books, '0,1,2,3' );
160 //Just a basic Join...
161 $books = $author->withCondition(' @joined.info.title LIKE ? ORDER BY book.num ASC ', array( '%PHP%' ) )->ownBookList;
162 $this->checkBookNumbers( $books, '0,1' );
163 //Mix Join and criteria
164 $books = $author->withCondition(' @joined.info.title LIKE ? AND num > 0 ORDER BY book.num ASC ', array( '%PHP%' ) )->ownBookList;
165 $this->checkBookNumbers( $books, '1' );
167 $books = $author->withCondition(' @joined.info.title LIKE ? ORDER BY book.num ASC', array( '%ing%' ) )->ownBookList;
168 $this->checkBookNumbers( $books, '0,1,2,3' );
170 $books = $author->withCondition(' @joined.info.title LIKE ? AND @joined.category.title = ? ORDER BY book.num ASC', array( '%ing%', 'computers' ) )->ownBookList;
171 $this->checkBookNumbers( $books, '0,1,2' );
172 //Join the same type twice... and order
173 $books = $author->withCondition(' @joined.info.title LIKE ? AND @joined.category.title = ? ORDER BY @joined.info.title ASC ', array( '%ing%', 'computers' ) )->ownBookList;
174 $this->checkBookNumbers( $books, '2,0,1' );
175 //Join the same type twice
176 $books = $author->withCondition(' @joined.info.title LIKE ? AND @joined.info.title LIKE ? ORDER BY book.num ASC', array( '%ing%', '%Learn%' ) )->ownBookList;
177 $this->checkBookNumbers( $books, '0,1,2' );
178 //Join the same type 3 times and order
179 $books = $author->withCondition(' @joined.info.title LIKE ? AND @joined.info.title LIKE ? ORDER BY @joined.info.title DESC', array( '%ing%', '%Learn%' ) )->ownBookList;
180 $this->checkBookNumbers( $books, '1,0,2' );
181 //Join the same type 3 times and order and limit
182 $books = $author->withCondition(' @joined.info.title LIKE ? AND @joined.info.title LIKE ? ORDER BY @joined.info.title DESC LIMIT 1', array( '%ing%', '%Learn%' ) )->ownBookList;
183 $this->checkBookNumbers( $books, '1' );
184 //Other combinations I can think of...
185 $books = $author->withCondition(' @joined.category.title LIKE ? ORDER BY @joined.info.title DESC', array( '%ing%' ) )->ownBookList;
186 $this->checkBookNumbers( $books, '3' );
187 $books = $author->withCondition(' @joined.category.title LIKE ? AND num < 4 ORDER BY @joined.info.title DESC', array( '%ing%' ) )->ownBookList;
188 $this->checkBookNumbers( $books, '3' );
190 $books = $author->with(' ORDER BY @joined.category.title ASC, @joined.info.title ASC' )->ownBookList;
191 $this->checkBookNumbers( $books, '2,0,1,3' );
192 $books = $author->with(' ORDER BY @joined.category.title DESC, @joined.info.title ASC' )->ownBookList;
193 $this->checkBookNumbers( $books, '3,2,0,1' );
194 $books = $author->with(' ORDER BY @joined.category.title DESC, @joined.info.title ASC LIMIT 2' )->ownBookList;
195 $this->checkBookNumbers( $books, '3,2' );
199 * Tests the more complicated scenarios for
204 public function testComplexInFrozenMode()
207 $this->testComplexCombinationsJoins();
209 $this->testComplexCombinationsJoins();
214 * Tests R::setNarrowFieldMode() and
215 * OODBBean::ignoreJoinFeature().
217 public function testSystemWideSettingsForJoins()
220 $author = R::dispense( 'author' );
221 $book = R::dispense( 'book' );
222 $info = R::dispense( 'info' );
224 $author->xownBookList[] = $book;
227 $author = $author->fresh();
228 $books = $author->withCondition(' @joined.info.title != ? ', array('y1') )->xownBookList;
229 $firstBook = reset( $books );
230 asrt( isset( $firstBook->title ), FALSE );
231 R::setNarrowFieldMode( FALSE );
232 $author = $author->fresh();
233 $books = $author->withCondition(' @joined.info.title != ? ', array('y2') )->xownBookList;
234 $firstBook = reset( $books );
235 asrt( isset( $firstBook->title ), TRUE );
236 R::setNarrowFieldMode( TRUE );