CODEWITHSUNDEEP
yii2
code-zoop
code-zoop

Reputation: 7368

Complicated sorting based on date Yii2

I am creating a search in yii2.

$q = $_GET['q'];

$dataProvider = new ActiveDataProvider([
    'query' => company::find()
        ->where('name like :name', [':name' => "$q%"])
]);
return $this->render('index', ['dataProvider' => $dataProvider,]);

I also have two date properties (valid payment from and to):

from_date and to_date

I need to display the result starting with the one who has a valid payment first.

How should the qyery look if I want to display the companies with valid payment first? They should all sort on name ASC..

I have a function to get this value in the model, but not i the query:

public function getValidPayment()
{
    $today = date("Y-m-d");
    return (($this->from_date <= $today) && ($today <= $this->to_date));
}

This is yii2

Thanks!

Edit: Just to clarify what I need:

  1. Query companies based on name (name like :name')
  2. priority the one that has valid from and to date (from_date and to_date)
  3. display the rest that does not have a valid from and to date (both groups in the query should sort on name ASC).

Maybe I should do this in 2 queries, and somehow combine them?

Upvotes: 1

Views: 2608

Answers (1)

Degger
Degger

Reputation: 4313

'query' => company::find()
  ->select([
    'valid_payment'=>"(('".$today."'<=to_date) AND (from_date<='".$today."'))",
    name,
    //...some fields you required in your grid
  ])
  ->where('name like :name', [':name' => "$q%"])
  ->orderBy('valid_payment DESC, name ASC')

and don't forget initialize $today

Upvotes: 2

Related Questions