Section: Declaring methods in the XML file
| « Manipulating a result set | ^ jDao: relational object mapping | Adding PHP methods to the factory » |
− Table of content
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,
or 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.
Example:
<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 ¶
Syntax ¶
<method type="select"> <!-- or type="selectfirst" -->
<parameter .../>
<conditions .../>
<order .../>
<limit .../>
</method>
The 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">
..
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 add 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 retrieved 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 distinct attribute.
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 additional
<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 an 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 nested <conditions> tags 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 expr attribute.
In the condition, if you want to compare the given value to a calculated value
of the field, you can use the pattern attribute, which should contain
an SQL expression, and the string %s, that will be replaced by the field name.
Example, with a comparison with the year part of a datetime field:
<eq property="create_date" value="2017" pattern="YEAR(%s)" />
The generated SQL will be:
WHERE YEAR(the_table.create_date) = 2017
The in/notin case ¶
<in> and <notin> are equivalent to foo IN ( list_of_values ) or
foo NOT IN (list_of_values ). The use of value and expr is different
than in other elements. If you have a static list of values, you will put it in
the value attribute like you would do in SQL:
<in property="foo" value="5,3,2" />
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="$list" />
This parameter must contain a PHP array of values. And the expr attribute
cannot can't contain anything else than a parameter name.
