Quick links: Content - sections - sub sections
EN FR

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.