Section: Declaring methods in the XML file
|« Manipulating a result set||^ jDao: relational object mapping||Adding PHP methods to the factory »|
A factory is generated by jDao and contains some methods (find, findAll, get, insert, etc.) as indicated in the previous section. However, we often need some other behaviors : selecting some specific records, updating specific fields etc.
The <factory> section in a jDao file allows you to define some additional methods which will execute some SQL queries. The advantage over creating PHP methods in a PHP class, is that you don't have worries about SQL injection, about creating complex SQL queries: jDao generates SQL queries for you, without security issues.
<method> element ¶
XML Methods in a jDao file are declared with the <method> element. It should have at leas one attribute, name, indicating the name of the method. There are different type of method, and then you should indicate the type of the method in the type attribute.
You can add one or several <parameter> element which define parameters for the method. Each of this elements should have a name attribute, and you can indicate a default value for the parameter, with the default attribute.
<method name="mySelect"> <parameter name="foo" /> <parameter name="bar" default="20"/> ... </method>
Here are now the different methods. The <conditions> element is described later.
select, selectfirst type methods ¶
<method type="select"> <!-- or type="selectfirst" --> <parameter .../> <conditions .../> <order .../> <limit .../> </method>
select type methods return a list of objects; whereas the ones of
selectfirst type return the first object corresponding to the criteria. This objects are records, which contain properties as you described in the <record> element.
You should notice that it is not possible to get only few properties. This would not have sens in the jDao pattern to have a record which would have some empty properties (we couldn't update the record properly). If you want to select only few properties, you should create an other Dao.
You can add a distinct attribute to only retrieve the distinct elements:
<method type="select" name="findThem" distinct="true"> ..
You can also add a groupby attribute to add a GROUP BY clause. (use it only with mysql, the generated code doesn't really work on other databases)
<method type="select" name="findThem" groupby="foo"> ..
The optional <condition> element describes a condition (WHERE clause in SQL). See the section later.
You can also add a order element so you can specify the order of the retrieved records (ORDER clause in SQL). So you should addd one or more <orderitem> elements, which contain a property attribute and a way attribute. property indicates the property on which the order will be applied, and way should contain "asc" or "desc" values, or a name of one of method parameters (the name should begin with a $)
<order> <orderitem property="foo" way="asc" /> </order>
You can also indicate a parameter name in the property attribute :
<parameter name="bar"/> <parameter name="baz" /> <order> <orderitem property="$bar" way="$baz" /> </order>
A limit element can be added so you can limit the number of retrieven records.
<limit offset="5" count="10" />
Like in <orderitem>, count and offset can contain, either a number, either a parameter name:
<method type="select" name="getFewRecord" > <parameter name="count"/> <parameter name="offset"/> <limit offset="$offset" count="$count"/> </method>
Using such methods ¶
The use of XML methods is natural for a PHP developer.
Here is an example of the use of the previous getFewRecord method.
$dao = jDao::get('module~list'); // we retrieve the factory $records = $dao->getFewRecord(10, 5); // we call the method with its arguments, as described in our xml dao file foreach ($records as $record) // now we can loop over the list of results jLog::log($record->name); // we access of one of the property of the record
So, for each XML methods declared in the dao file, a PHP method is generated and usable directly in your scripts.
Count type methods ¶
<method type="count"> <parameter .../> <conditions .../> </method>
This kind of method is equivalent to a
SELECT COUNT(*), with the indicated conditions.
If you want to do a
SELECT COUNT(DISTINCT a_field), you have to indicate the property into a
Delete type method ¶
<method type="delete"> <parameter .../> <conditions .. /> </method>
Generate a method which execute a DELETE SQL statement.
Update type method ¶
<method type="update"> <parameter /> <conditions /> <values /> </method>
This sort of method execute an UPDATE SQL statement. You can add an additionnal <values> element which should contain one or more <value> elements.
<parameter name="price" /> <values> <value property="product_name" value="jelix"/> <value property="price" expr="$price"/> </values>
The property value contain the property name which will be updated. And you should use either the value attribute, either the expr attribute. The value attribute should contains a value. The expr attribute should contain a SQL expression or a method parameter.
Conditions clause ¶
In previous method declaration, you can use the <conditions> element. Here is this element, with its possible child elements:
<conditions logic="and"> <eq property="foo" value=""/> <neq property="foo" value=""/> <lt property="foo" value=""/> <gt property="foo" value=""/> <lteq property="foo" value=""/> <gteq property="foo" value=""/> <like property="foo" value=""/> <notlike property="foo" value="" /> <isnull property="foo"/> <notisnull property="foo"/> <in property="foo" value=""/> <notin property="foo" value=""/> </conditions>
We can give several <conditions> tags imbricated to make "or"/"and" groups. If the logic attribute is not specified, its value is AND by default. It should contains the "AND" or "OR" value.
The value attribute must have a value. The type of this value is the type of the property. If you prefer to use a SQL expression or to specify a method parameter, you have to use the
The in/notin case ¶
or<in property="foo" value="'toto','titi','tata'" />
You will use expr when you will have a method parameter ( thus a list of dynamic values ) :<in property="foo" expr="$liste" />
This parameter must contain a PHP table of values. And the expr attribute cannot can't contain anything else than a parameter name.