Propel2 criterions

Propel2 do not have mysql’s full text search support out of the box.
Of course you can do

$movies = MovieQuery::create()
    -> where('MATCH(Movie.title, Movie.director) AGAINST(? IN BOOLEAN MODE)',
             'big lebowski')
    -> find();

credits to jengeb

This doesn’t looks good for reuse. You can create a Query helper class, but it is still doesn’t looks right.

Here comes the criterions.

Criterions are something like where clauses.

Lets make quick example about in criterion

First we need the ModelCriteria. Basically this is the Query class.

There is this method called `combine`….

Combine several named criterions with a logical operator

So, we need the Criterion.

The easiest way is using the getNewCriterion method

$criterion = $modelCriteria->getNewCriterion(
   'id', // sql field
   12345, // desired value
   '=' // condition name
);

Then we have to add the criterion to the query. This won’t apply the criterion filter!

$modelCriteria->addCond('myCondition', $criterion);

Lets add the criterion to desired filter.

$query->combine(['myCondition']);

This looks too complicated, but actually it is very helpful when you are building something like API.

So, lets back to the full text search. Here is example code of full text search criterion

use Propel\Runtime\ActiveQuery\Criterion\AbstractCriterion;
use Propel\Runtime\ActiveQuery\Criteria;

/**
 * Criterion for MySQL/MariaDB full text search
 *
 * @author po_taka
 */
class FullTextCriterion extends AbstractCriterion
{
    /**
     * Create a new instance.
     *
     * @param Criteria $outer      The outer class (this is an "inner" class).
     * @param string   $column     ignored
     * @param string   $value      The condition to be added to the query string
     * @param string   $comparison SQL MATCH modifier
     */
    public function __construct(Criteria $outer, $column, $value, $comparison = 'IN NATURAL LANGUAGE MODE')
    {
        return parent::__construct($outer, $column, $value, $comparison);
    }

    /**
     * Appends a Prepared Statement representation of the Criterion onto the buffer
     *
     * @param string &$sb    The string that will receive the Prepared Statement
     * @param array  $params A list to which Prepared Statement parameters will be appended
     */
    protected function appendPsForUniqueClauseTo(&$sb, array &$params)
    {
        $field = (null === $this->table) ? $this->column : $this->table . '.' . $this->column;

        $params[] = [
            'table' => $this->realtable,
            'column' => $this->column,
            'value' => $this->value,
        ];

        $sb .= " MATCH (" . $field . ") AGAINST( :p" . count($params) . " " . $this->comparison . ") ";
    }
}

This should be safe against sql injections 🙂 This is the benefit instead of using $query->where('MATCH (my_field) AGAINS "' . $param '")

Example usage

$criterion = new FullTextCriterion(
   $query,
   'my_field',
   'I\'m looking for this text :)'
 );
$modelCriteria->addCond('myCondition', $criterion);
$query->combine(['myCondition']);

I was planing to release some small package for propel2, but I don’t think that anyone will ever use it :/