Section: Manipulating a result set
« Instantiation and use of a dao factory and record | ^ jDao: relational object mapping | Declaring methods in the XML file » |
− Table of content
Creating, modifying and deleting records in a database is very useful. However, it's not as useful as being able to fetch records and use them. The page on the use of a dao factory and record showed you the create, the delete and the update. This is the page on the fetch, or the "select data" part of jDao.
Depending on how and how much results are needed, the method is somewhat different.
Retrieve a single record ¶
To retrieve a single record by specifying it's key (the primary key in the database),
the method to use is called get()
.
There is no need to further parse the return value of the get()
function since
it will fetch the specified record and return it as an object. The object is design
so that each column name is the name of a variable, and the column value, the variable value.
// instantiation of the factory
$myFactory = jDao::get("foo");
// retrieve a record whose content corresponds
// to the record with identifier = 3
$baz = $myFactory->get(3);
// the id of the record is 3, as we specified
echo $baz->id;
In the case where the primary key is several fields, declared like this in your
xml file, primarykey="key1,key2"
, you have to give all key values at te same time:
$myFactory = jDao::get("foo");
// 3 and 4 are values of key1 and key2
$baz = $myFactory->get(3, 4);
or
$myFactory = jDao::get("foo");
// 3 and 4 are values of key1 and key2
$baz = $myFactory->get(array(3, 4));
Careful: the order of the value should be the same as the order of the declaration
of keys in the primarykey attribute. Eg, $myFactory->get(4, 3)
is different
from $myFactory->get(3, 4)
.
The delete()
method works in the same way.
Retrieving several records ¶
When all the records are needed, the findAll()
method is used. This method
will return a result set, (jDbResultSet
class) containing all the results
from the database in the order they where added to the database. Remember that
this class implements the Iterator
interface, so it can be used into a
foreach statement.
// instantiation of the factory
$myFactory = jDao::get("foo");
// retrieve a complete list of the foo type records
$list = $myFactory->findAll();
foreach ($list as $row) {
// $row content a record
echo $row->id;
}
You can use also two other methods to retrieve records. The first one being fetch()
which fetches one row from the result set as an object and advances the internal
pointer one step.
// fetch the records one at the time
while ($row = $list->fetch) {
// do something with the record
echo $row->id;
}
The second one being fetchAll()
which returns an array containing an object
for each row in the result set.
// fetch all the records as an array of objects
$rows = $list->fetchAll();
foreach ($rows as $rowID => $row) {
// do something with the record
echo $row->id;
}
You can make other retrieving methods by specifying them in the xml file (see Declaring methods in the XML file and Adding PHP methods to the factory) .
Retrieve record(s) through criteria ¶
To retrieve record(s) through criteria, there are three simple methods available,
findBy()
, countBy()
and deleteBy()
. These methods have one
required parameter, a jDaoConditions
object, which contains all the conditions.
$a_name = "something";
// create the jDaoConditions object
$conditions = jDao::createConditions();
$conditions->addCondition('label', '=', $a_name);
$conditions->addCondition('status', '=', 5);
$list = $myFactory->findBy($conditions);
$count = $myFactory->countBy($conditions);
In the same way as the findAll()
method, findBy()
returns a
jDbResultSet
object, which gives you the list of records corresponding
to indicated criteria.
The addCondition()
method take as parameter a property name, an operator
(SQL), and a value.
You can specify an order of select with the method addItemOrder()
, and group
various criterion together with startGroup()
and endGroup()
:
$conditions = jDao::createConditions();
// condition : label = $a_name AND (status=5 OR status=4) ORDER BY label desc
$conditions->addCondition('label','=',$a_name);
$conditions->startGroup('OR');
$conditions->addCondition('status','=',5);
$conditions->addCondition('status','=',4);
$conditions->endGroup();
$conditions->addItemOrder('label','desc');
$list = $myFactory->findBy($conditions);
To add a limit, you can specify additional parameters to the findBy()
method:
the offset and the number of records to fetch.
$list = $myFactory->findBy($conditions, 0, 15);
As for the countBy()
method it takes one additional parameter: the property
name that we want to apply a DISTINCT clause.
// SELECT COUNT(DISTINCT table.label)...
$count = $myFactory->countBy($conditions, 'label');
Lastly, the deleteBy()
method allow to delete records corresponding to indicated
criteria, it returns the number of deleted records:
$nb_deleted = $myFactory->deleteBy($conditions);
You will see that you can get the same result by defining methods in the xml file.
However, you will choose findBy()
or a xml-defined method, depending of the context.
Criteria-based retrieving is best used when you don't know the number of criterion and their type. This includes things such as a complex search form, where the user can choose which criterion are applied. Criteria-based retrieving is also best used when the query is used only one time or very rarely. This is because the xml-defined methods are compiled to PHP, and thus are included each time you call the factory. It may not be useful to always include some code which is seldomly used. Using criteria-based retrieving will improve the global performances in that case.
In the other cases, it is recommended to use xml methods, especially when you know the criteria by advance (without forcibly knowing their value of course), and when it is an often used research. jDao generates SQL queries during the generation of the PHP file, so this is a process it has not to do when executing the page.
For example, we often redefine the findAll
method in XML, to be able to specify
a retrieving order...
SQL operators ¶
As explained before, addCondition()
tak a sql operator as a second parameter.
Here are the operators supported:
- LIKE, NOT LIKE, ILIKE,
- IN, NOT IN,
- IS, IS NOT,
- IS NULL, IS NOT NULL,
- MATCH, REGEXP, NOT REGEXP, RLIKE, SOUNDS LIKE
~
,!~
,~*
,!~*
(operators for regular expression for postgresql)
Special case with NULL ¶
To test a (non) NULL value, you can use any supported operators, even "=" or "!=". Don't forget to pass the php null, not the string "NULL".
$conditions->addCondition('status','=', null); // equivalent to IS NULL
$conditions->addCondition('status','!=', null); // equivalent to IS NOT NULL
$conditions->addCondition('status','IS', null);
$conditions->addCondition('status','IS NOT', null);
$conditions->addCondition('status','IS NULL', null);
$conditions->addCondition('status','IS NOT NULL', null);
$conditions->addCondition('status','LIKE', null);
$conditions->addCondition('status','NOT LIKE', null);
...
Regular expressions ¶
Some databases like Postgresql support regular expressions. For postgresql you can then use keywords ~
, !~
, ~*
, !~*
.
$conditions->addCondition('status','~', '^test');
...
Other operator like REGEXP
, NOT REGEXP
are also supported. Verify first if it is compatible with your database.
Doing a GroupBy ¶
jDaoConditions
enables you to indicate on which properties you want to create a group. Just call the addItemGroup()
method. The only parameter required is the name of the column where the grouping will occur.
$conditions = jDao::createConditions();
$conditions->addCondition('label', '=', $name);
$conditions->addItemGroup('status');
$list = $maFactory->findBy($conditions);