getCol() public static method

Executes SQL.
public static getCol ( string $sql, array $bindings = [] ) : array
$sql string SQL query to execute
$bindings array a list of values to be bound to query parameters
return array
Beispiel #1
0
 /**
  * Tests the various ways to fetch (select queries)
  * data using adapter methods in the facade.
  * Also tests the new R::getAssocRow() method, 
  * as requested in issue #324.
  */
 public function testFetchTypes()
 {
     R::nuke();
     $page = R::dispense('page');
     $page->a = 'a';
     $page->b = 'b';
     R::store($page);
     $page = R::dispense('page');
     $page->a = 'c';
     $page->b = 'd';
     R::store($page);
     $expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
     asrt(json_encode(R::getAll('SELECT * FROM page')), $expect);
     $expect = '{"1":"a","2":"c"}';
     asrt(json_encode(R::getAssoc('SELECT id, a FROM page')), $expect);
     asrt(json_encode(R::getAssoc('SELECT id, a, b FROM page')), $expect);
     $expect = '[{"id":"1","a":"a"},{"id":"2","a":"c"}]';
     asrt(json_encode(R::getAssocRow('SELECT id, a FROM page')), $expect);
     $expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
     asrt(json_encode(R::getAssocRow('SELECT id, a, b FROM page')), $expect);
     $expect = '{"id":"1","a":"a","b":"b"}';
     asrt(json_encode(R::getRow('SELECT * FROM page WHERE id = 1')), $expect);
     $expect = '"a"';
     asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 1')), $expect);
     $expect = '"b"';
     asrt(json_encode(R::getCell('SELECT b FROM page WHERE id = 1')), $expect);
     $expect = '"c"';
     asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 2')), $expect);
     $expect = '["a","c"]';
     asrt(json_encode(R::getCol('SELECT a FROM page')), $expect);
     $expect = '["b","d"]';
     asrt(json_encode(R::getCol('SELECT b FROM page')), $expect);
 }
Beispiel #2
0
 /**
  * Test common Facade usage scenarios.
  *
  * @return void
  */
 public function testCommonUsageFacade()
 {
     $toolbox = R::getToolBox();
     $adapter = $toolbox->getDatabaseAdapter();
     $writer = $toolbox->getWriter();
     $redbean = $toolbox->getRedBean();
     $pdo = $adapter->getDatabase();
     $a = new AssociationManager($toolbox);
     asrt(R::getRedBean() instanceof OODB, TRUE);
     asrt(R::getToolBox() instanceof ToolBox, TRUE);
     asrt(R::getDatabaseAdapter() instanceof Adapter, TRUE);
     asrt(R::getWriter() instanceof QueryWriter, TRUE);
     $book = R::dispense("book");
     asrt($book instanceof OODBBean, TRUE);
     $book->title = "a nice book";
     $id = R::store($book);
     asrt($id > 0, TRUE);
     $book = R::load("book", (int) $id);
     asrt($book->title, "a nice book");
     asrt(R::load('book', 999)->title, NULL);
     R::freeze(TRUE);
     try {
         R::load('bookies', 999);
         fail();
     } catch (\Exception $e) {
         pass();
     }
     R::freeze(FALSE);
     $author = R::dispense("author");
     $author->name = "me";
     R::store($author);
     $book9 = R::dispense("book");
     $author9 = R::dispense("author");
     $author9->name = "mr Nine";
     $a9 = R::store($author9);
     $book9->author_id = $a9;
     $bk9 = R::store($book9);
     $book9 = R::load("book", $bk9);
     $author = R::load("author", $book9->author_id);
     asrt($author->name, "mr Nine");
     R::trash($author);
     R::trash($book9);
     pass();
     $book2 = R::dispense("book");
     $book2->title = "second";
     R::store($book2);
     $book3 = R::dispense("book");
     $book3->title = "third";
     R::store($book3);
     asrt(count(R::find("book")), 3);
     asrt(count(R::findAll("book")), 3);
     asrt(count(R::findAll("book", " LIMIT 2")), 2);
     asrt(count(R::find("book", " id=id ")), 3);
     asrt(count(R::find("book", " title LIKE ?", array("third"))), 1);
     asrt(count(R::find("book", " title LIKE ?", array("%d%"))), 2);
     // Find without where clause
     asrt(count(R::findAll('book', ' order by id')), 3);
     R::trash($book3);
     R::trash($book2);
     asrt(count(R::getAll("SELECT * FROM book ")), 1);
     asrt(count(R::getCol("SELECT title FROM book ")), 1);
     asrt((int) R::getCell("SELECT 123 "), 123);
     $book = R::dispense("book");
     $book->title = "not so original title";
     $author = R::dispense("author");
     $author->name = "Bobby";
     R::store($book);
     $aid = R::store($author);
     $author = R::findOne("author", " name = ? ", array("Bobby"));
 }
Beispiel #3
0
 /**
  * Test Full fluid UUID support.
  *
  */
 public function testFullSupport()
 {
     //Rewire objects to support UUIDs.
     $oldToolBox = R::getToolBox();
     $oldAdapter = $oldToolBox->getDatabaseAdapter();
     $uuidWriter = new \UUIDWriterPostgres($oldAdapter);
     $newRedBean = new OODB($uuidWriter);
     $newToolBox = new ToolBox($newRedBean, $oldAdapter, $uuidWriter);
     R::configureFacadeWithToolbox($newToolBox);
     list($mansion, $rooms, $ghosts, $key) = R::dispenseAll('mansion,room*3,ghost*4,key');
     $mansion->name = 'Haunted Mansion';
     $mansion->xownRoomList = $rooms;
     $rooms[0]->name = 'Green Room';
     $rooms[1]->name = 'Red Room';
     $rooms[2]->name = 'Blue Room';
     $ghosts[0]->name = 'zero';
     $ghosts[1]->name = 'one';
     $ghosts[2]->name = 'two';
     $ghosts[3]->name = 'three';
     $rooms[0]->noLoad()->sharedGhostList = array($ghosts[0], $ghosts[1]);
     $rooms[1]->noLoad()->sharedGhostList = array($ghosts[0], $ghosts[2]);
     $rooms[2]->noLoad()->sharedGhostList = array($ghosts[1], $ghosts[3], $ghosts[2]);
     $rooms[2]->xownKey = array($key);
     //Can we store a bean hierachy with UUIDs?
     R::debug(1);
     $id = R::store($mansion);
     //exit;
     asrt(is_string($id), TRUE);
     asrt(strlen($id), 36);
     $haunted = R::load('mansion', $id);
     asrt($haunted->name, 'Haunted Mansion');
     asrt(is_string($haunted->id), TRUE);
     asrt(strlen($haunted->id), 36);
     asrt(is_array($haunted->xownRoomList), TRUE);
     asrt(count($haunted->ownRoom), 3);
     $rooms = $haunted->xownRoomList;
     //Do some counting...
     $greenRoom = NULL;
     foreach ($rooms as $room) {
         if ($room->name === 'Green Room') {
             $greenRoom = $room;
             break;
         }
     }
     asrt(!is_null($greenRoom), TRUE);
     asrt(is_array($greenRoom->with(' ORDER BY id ')->sharedGhostList), TRUE);
     asrt(count($greenRoom->sharedGhostList), 2);
     $names = array();
     foreach ($greenRoom->sharedGhost as $ghost) {
         $names[] = $ghost->name;
     }
     sort($names);
     $names = implode(',', $names);
     asrt($names, 'one,zero');
     $rooms = $haunted->xownRoomList;
     $blueRoom = NULL;
     foreach ($rooms as $room) {
         if ($room->name === 'Blue Room') {
             $blueRoom = $room;
             break;
         }
     }
     asrt(!is_null($blueRoom), TRUE);
     asrt(is_array($blueRoom->sharedGhostList), TRUE);
     asrt(count($blueRoom->sharedGhostList), 3);
     $names = array();
     foreach ($blueRoom->sharedGhost as $ghost) {
         $names[] = $ghost->name;
     }
     sort($names);
     $names = implode(',', $names);
     asrt($names, 'one,three,two');
     $rooms = $haunted->xownRoomList;
     $redRoom = NULL;
     foreach ($rooms as $room) {
         if ($room->name === 'Red Room') {
             $redRoom = $room;
             break;
         }
     }
     $names = array();
     foreach ($redRoom->sharedGhost as $ghost) {
         $names[] = $ghost->name;
     }
     sort($names);
     $names = implode(',', $names);
     asrt($names, 'two,zero');
     asrt(!is_null($redRoom), TRUE);
     asrt(is_array($redRoom->sharedGhostList), TRUE);
     asrt(count($redRoom->sharedGhostList), 2);
     //Can we repaint a room?
     $redRoom->name = 'Yellow Room';
     $id = R::store($redRoom);
     $yellowRoom = R::load('room', $id);
     asrt($yellowRoom->name, 'Yellow Room');
     asrt(!is_null($yellowRoom), TRUE);
     asrt(is_array($yellowRoom->sharedGhostList), TRUE);
     asrt(count($yellowRoom->sharedGhostList), 2);
     //Can we throw one ghost out?
     array_pop($yellowRoom->sharedGhost);
     R::store($yellowRoom);
     $yellowRoom = $yellowRoom->fresh();
     asrt($yellowRoom->name, 'Yellow Room');
     asrt(!is_null($yellowRoom), TRUE);
     asrt(is_array($yellowRoom->sharedGhostList), TRUE);
     asrt(count($yellowRoom->sharedGhostList), 1);
     //can we remove one of the rooms?
     asrt(R::count('key'), 1);
     $list = $mansion->withCondition(' "name" = ? ', array('Blue Room'))->xownRoomList;
     $room = reset($list);
     unset($mansion->xownRoomList[$room->id]);
     R::store($mansion);
     asrt(R::count('room'), 2);
     //and what about its dependent beans?
     asrt(R::count('key'), 0);
     asrt(R::count('ghost_room'), 3);
     //and can we find ghosts?
     $ghosts = R::find('ghost');
     asrt(count($ghosts), 4);
     $ghosts = R::findAll('ghost', 'ORDER BY id');
     asrt(count($ghosts), 4);
     $ghosts = R::findAll('ghost', 'ORDER BY id LIMIT 2');
     asrt(count($ghosts), 2);
     $ghostZero = R::findOne('ghost', ' "name" = ? ', array('zero'));
     asrt($ghostZero instanceof OODBBean, TRUE);
     //can we create link properties on existing tables?
     $blackRoom = R::dispense('room');
     $blackRoom->name = 'Black Room';
     $ghostZero->link('ghost_room', array('mood' => 'grumpy'))->room = $blackRoom;
     R::store($ghostZero);
     $ghostZero = $ghostZero->fresh();
     $list = $ghostZero->sharedRoomList;
     asrt(count($list), 3);
     $ghostZero = $ghostZero->fresh();
     $list = $ghostZero->withCondition(' ghost_room.mood = ? ', array('grumpy'))->sharedRoomList;
     asrt(count($list), 1);
     //can we load a batch?
     $ids = R::getCol('SELECT id FROM ghost');
     $ghosts = R::batch('ghost', $ids);
     asrt(count($ghosts), 4);
     //can we do an aggregation?
     $ghosts = $greenRoom->aggr('ownGhostRoom', 'ghost', 'ghost');
     asrt(count($ghosts), 2);
     //can we duplicate the mansion?
     asrt(R::count('mansion'), 1);
     asrt(R::count('room'), 3);
     asrt(R::count('ghost'), 4);
     $copy = R::dup($mansion);
     R::store($copy);
     asrt(R::count('mansion'), 2);
     asrt(R::count('room'), 5);
     //black room does not belong to mansion 1
     asrt(R::count('ghost'), 4);
     //can we do some counting using the list?
     asrt($copy->countOwn('room'), 2);
     $rooms = $copy->withCondition(' "name" = ? ', array('Green Room'))->xownRoomList;
     $room = reset($rooms);
     asrt($room->countShared('ghost'), 2);
     //Finally restore old toolbox
     R::configureFacadeWithToolbox($oldToolBox);
 }
Beispiel #4
0
 public static function shortuuid()
 {
     $uuid = R::getCol('select UUID_SHORT()');
     return $uuid[0];
 }