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 :/