Skip to content

Latest commit

 

History

History
258 lines (192 loc) · 7.76 KB

xml_methods.md

File metadata and controls

258 lines (192 loc) · 7.76 KB

Defining methods in XML

A factory is generated by JelixDao 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 JelixDao 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: JelixDao generates SQL queries for you, without security issues.

<method> element

XML Methods in a JelixDao file are declared with the <method> element. It should have at least 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 these 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 method types

Syntax

  <method type="select"> <!-- or type="selectfirst"  -->
     <parameter .../>
     <conditions .../>
     <order .../>
     <limit .../>
  </method>

The select type methods return a result set, whereas the ones of selectfirst type return the first record object corresponding to the criteria.

You should notice that it is not possible to get only few properties. Record objects will have all properties defined into the <record> element. This would not have sens in the JelixDao 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"> 
   ..
</method>

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>

Usage

The use of XML methods is easy, like any PHP methods.

Here is an example of the use of the previous getFewRecord method.

$dao = $loader->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
   echo $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 method type

  <method type="count" name="countSomething">
     <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.

You can use the same elements, <parameter> and <conditions>, as for select methods.

delete method type

  <method type="delete" name="deleteSomething">
     <parameter .../>
     <conditions .. />
  </method>

Generate a method which executes a DELETE SQL statement. You can use the same elements, <parameter> and <conditions>, as for select methods.

update method type

  <method type="update">
     <parameter />
     <conditions />
     <values />
  </method>

This sort of method execute an UPDATE SQL statement. In addition of the <parameter> and <conditions> element, you can use a <values> element which should contain one or more <value> elements. These elements indicate which property to update, with which value.

    <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, or the expr attribute. The value attribute should contain a raw 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 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 raw 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="2022" 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 contain anything else than a parameter name.