How to make friends Yii (ActiveDataProvider) and Text Search in PostgreSQL

Using PostgreSQL tsearch2 in a project on Yii

Any site is primarily texts. In order for texts to be convenient to edit, they are often stored in the database. At the same time, additional features appear, such as a convenient search by the contents of the text field. Good old LIKE is good, but not always. There are more advanced things like tsearch2 in PostgreSQL. How to use it in the Yii Framework, I will tell under the cut.

Preamble

Once I had to implement full-text search on one of the sites that I created for me. There is no need to google for a long time to work with tsearch2 and think about how it works, because there is a comprehensive guide on the site. What is the beauty of using tsearch2 compared with LIKE, I think, too, I don’t need to explain. This site is full of information. So, if in the application to write SQL queries in an explicit form, then there are no problems, but I use Yii and I would like everything to be done in the way recommended by the developers. However, this is not the only reason. To use the CListView widget, we need to prepare an instance of the CActiveDataProvider class. This is where the fun began.

Decision

To use the CActiveDataProvider, we need to initialize an object of the CDbCriteria class, however, it is not possible to change the FROM field to add a function call there that forms a request to the tsearch2 engine, like:

SELECT ... FROM ..., to_tsquery ( $ sh_string ) AS q ...

')
All that remains is to use the model that is filled in by a call to the findAllBySql method, but the mechanism built into CActiveDataProvider only supports findAll. So how do we convert a model obtained from a pure SQL query into a CActiveDataProvider? The solution was found on Stackoverflow, but the troubles did not end there. I, in the table where the texts were stored, the key field was not called id, but txt_id, in this connection I had to make a small but very important amendment to the text from the answer. That's what happened with me.
In the controller:

$ _shString = '' ;
if ( isset ( $ _GET [ 'sh' ] ) )
{
$ _shString = implode ( '&' , explode ( '' , $ _GET [ 'sh' ] ) ) ;
$ _qry = "
SELECT
txt_id
ts_headline (txt, q, 'StartSel = <strong>, StopSel = </ strong>, MaxWords = 35, MinWords = 15') AS txt,
ts_rank (fti_txt, q) AS rank
FROM
texts, to_tsquery (: sh) AS q
WHERE
user_id =: uid AND fti_txt @@ q
ORDER BY rank DESC
" ;

$ _model = Texts :: model ( ) -> findAllBySql ( $ _qry , array ( ': uid' => Yii :: app ( ) -> user -> id , ': sh' => $ _shString ) ) ;
}

In the presentation:

$ this -> widget ( 'zii.widgets.CListView' , array (
'id' => 'search-results' ,
'dataProvider' => new CArrayDataProvider ( $ model , array ( 'keyField' => 'txt_id' ) ) ,
'itemView' => '_text' ,
) )

That same editing refers to

array ( 'keyField' => 'txt_id' )

UPD. 2012-04-02

As the respected Rive wrote in the posts, the problem is that with this approach all search results will be selected both initially and when navigating through the pages in the CListView. In search of a solution, I had to ask a question on the framework forum to which a short but completely comprehensive answer was immediately received! Who is too lazy to read say that the essence of the answer: Use CSqlDataProvider

My code turned out like this. In the controller:

$ _shString = '' ;
if ( isset ( $ _GET [ 'sh' ] ) )
{
$ _shString = implode ( '&' , explode ( '' , $ _GET [ 'sh' ] ) ) ;

$ _cnt = Yii :: app ( ) -> db -> createCommand ( 'SELECT count (*) FROM texts, to_tsquery (: sh) AS q WHERE user_id =: uid AND fti_txt @@ q' ) -> queryScalar ( array ( ': uid' => Yii :: app ( ) -> user -> id , ': sh' => $ _shString ) ) ;

$ _qry = "
SELECT
txt_id
ts_headline (txt, q, 'StartSel = <strong>, StopSel = </ strong>, MaxWords = 35, MinWords = 15') AS txt,
ts_rank (fti_txt, q) AS rank
FROM
texts, to_tsquery (: sh) AS q
WHERE
user_id =: uid AND fti_txt @@ q
ORDER BY rank DESC
" ;

$ dataProvider = new CSqlDataProvider ( $ _qry , array (
'totalItemCount' => $ _cnt ,
'params' => array ( ': uid' => Yii :: app ( ) -> user -> id , ': sh' => $ _shString ) ,
'keyField' => 'txt_id' ,
'pagination' => array (
'pageSize' => 20 ,
) ,
) )
}

Next in view:

$ this -> widget ( 'zii.widgets.CListView' , array (
'id' => 'search-results' ,
'dataProvider' => $ dataProvider ,
'itemView' => '_text' ,
) )

As you can see, to draw each element of the list, an auxiliary view _text.php is used, here is the simplest version of its contents:

<div class = "view">
<? php
// echo CHtml :: encode ($ data-> txt);
// echo $ data-> txt;
echo $ data [ 'txt' ] ;
?>
</ div>

Special attention should be paid to two points:

1. When creating an instance of CSqlDataProvider, you must separately find and transfer to it the total number of records returned by the request. It turns out that initially two requests of the same severity are executed. This is written in the official documentation.
2. Obtained during the “passage” of the query results, the $ data component inside the provider has the format of an array, not an object. The source _text.php I gave is not in vain, here you need to use echo $ data ['txt']; instead of echo $ data-> txt;

I hope this experience will be useful to someone!