From RedBean

Contents 1 Querying 1.1 When should I use SQL instead of Beans 2 How to fire Queries in RedBean? 2.1 Transactions

Querying

As detailed in the previous chapters, RedBean is not an ORM layer that denies the existence of SQL. On the contrary, RedBean allows and encourages you to write SQL because SQL is considered to be both powerful and elegant. You should not try to replace SQL with OO based techniques (see Finding Beans); if you need SQL, use SQL. If you need OO use OO (or in our case Beans).

When should I use SQL instead of Beans

This question is hard to answer because it depends on many factors and especially the context of your code. There are however some rules of thumb I use to determine whether I need to use an SQL based approach instead of an OOP based approach. I personally tend to use plain old SQL over objects if:

• An OOP equivalent of the required SQL becomes too complex or too slow
• The problem at hand is a 'typical' database problem.

Of course the decision is up to you and this text is merely an opinion. However this also explains why RedBean lacks certain features and more importantly; why it does not try to mimic SQL with some sort of object oriented pseudo language.

How to fire Queries in RedBean?

To execute SQL statements with RedBean, you need to access the Database Adapter. The Adapter can be achieved in various ways; for instance (using the R-facade, see Tutorial):

$database = R::$adapter;

Also see: All R-methods

or if you have a RedBean Toolbox:

$database = $toolbox->getDatabaseAdapter();

To execute an SQL query:

$database->exec( "update page set title='test' where id=1" );

To fetch a multidimensional resultset directly after firing the query:

$database->get( "select * from page" );

Note that you can use bindings as well:

$database->get( "select * from page where 
title = :title", 
array("title"=>"home") );

or:

$database->get( "select * from page where 
title = ?", 
array("home") );

To fetch a single row:

$database->getRow("select * from page limit 1");

To fetch a single column:

$database->getCol("select title from page");

To fetch a single cell:

$database->getCell("select title from page limit 1");

To get the latest insert-id:

$database->getInsertID();

To get the number of rows affected:

$database->getAffectedRows();

To escape a value or columname for use in custom SQL use:

$database->escape( $value );

To get the original result resource to do your own processing:

$database->getRaw();

Transactions

To start a transaction:

$database->startTransaction();

To commit:

$database->commit();

And finally, to roll back a transaction:

$database->rollback();

Transactions can also be used in combination with the Unit Of Work pattern. RedBean offers a very powerful variation of the Unit_of_Work pattern.