CODEWITHSUNDEEP
phpsqlparametersyii2query-builder
Čamo
Čamo

Reputation: 4160

How to add column to Yii2 query builder result which could be used to order by?

I have a Yii2 query object which contains this method to execute fulltext search

public function search($search)
{
    return $this->andWhere('MATCH(title, description, tags) AGAINST(:search) AS value', ['search' => $search])
        ->orderBy('value');
}

The whole code looks like

$dataProvider = new ActiveDataProvider([
    'query' => Video::find()->published()->search($search)
]);

Of course this does not work cause andWhere() does not allow AS value statement. Can somebody tell me please how to solve this problem? Thanks a lot.

Upvotes: 0

Views: 551

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

I guess you can include your search part in your select clause in order to perform sorting on results

Video::find()
     ->published()
     ->search($search)
     ->select(['*','MATCH(title, description, tags) AGAINST(:search) AS value'])
     ->orderBy('value');

But the problem is to bind the value for :search placeholder (not sure about that), For this I guess you can use yii\db\Expression class which takes params as second argument and this expression can be used in select() method, So now the query builder will look something like

Video::find()
     ->published()
     ->search($search)
     ->select(['*',
         new \yii\db\Expression('MATCH(title, description, tags) AGAINST(:search) AS value',['search' => $search])
     ])
     ->orderBy('value');

Soution is the params method:

$this->select(['*','MATCH(title, description, tags) AGAINST(:search) AS score'])
    ->andWhere('MATCH(title, description, tags) AGAINST(:search)', ['search' => $search])
    ->params(['search' => $search])
    ->orderBy(['score' => SORT_DESC]);

Upvotes: 1

Akshay Vanjare
Akshay Vanjare

Reputation: 655

If you want to use "value" then you have to write it in select statement. You can apply filter on result using "having" clause.

You can use any methods from below two

1.

$query = Product::find()
    ->andWhere('MATCH ('.Product::tableName().'.name) AGAINST(:q)'
    ->params(['q' => $this->q])
    ->orderBy('(MATCH ('.Product::tableName().'.name) AGAINST(:q)) DESC');

2

$query = Product::find();
    ->select(Product::tableName().'.*, MATCH ('.Product::tableName().'.name) AGAINST(:q) as VL')
    ->having('VL > 5')
    ->params(['q' => $this->q]);

Upvotes: 1

Related Questions