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 (
insert, etc.) as indicated in the previous section. However,
we often need some other behaviors: selecting some specific records, updating
specific fields etc.
<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
<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
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
<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
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"> ..
<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
property indicates the property on which the order will be
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
<parameter name="bar"/> <parameter name="baz" /> <order> <orderitem property="$bar" way="$baz" /> </order>
limit element can be added so you can limit the number of retrieved records.
<limit offset="5" count="10" />
Like in <orderitem>,
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
$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
<parameter name="price" /> <values> <value property="product_name" value="jelix"/> <value property="price" expr="$price"/> </values>
property value contain the property name which will be updated. And you
should use either the
value attribute, either the
expr attribute. The
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 <conditions> tags imbricated to make "or"/"and" groups. If
logic attribute is not specified, its value is
AND by default. It
should contains the "AND" or "OR" value.
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
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(la_table.create_date) = 2017
The in/notin case ¶
<notin> are equivalent to
foo IN ( list_of_values ) or
foo NOT IN (list_of_values ). The use of
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" />
<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
cannot can't contain anything else than a parameter name.