Searching between two dates in yii2
The date can be presented in different formats. The table itself looks like this:
book varchar(250) NOT NULL,
date INT NOT NULL
Now my problem is that I cannot implement search in a range between two dates. For example, there are 5 books with different dates, but the start date starts at 31/12/14
and the end date starts 31/02/15
. Therefore, when the user selects a range between these dates, he must provide all the books in that date range.
Is there a way to do this in Yii2? I couldn't find anything so far
UPDATE
I am implementing a custom filter that does not belong GridView
and looks like a separate block outside the table.
It looks like this:
<div class="custom-filter">
Date range:
<input name="start" />
<input name="end" />
Book name:
<input name="book" />
</div>
source to share
If you are getting start and end in date format, but the date in your database table is of type INT, you should do something like this:
//Get values and format them in unix timestamp
$start = Yii::$app->formatter->asTimestamp(Yii::$app->request->post('start'));
$end = Yii::$app->formatter->asTimestamp(Yii::$app->request->post('end'));
//Book name from your example form
$bookName = Yii::$app->request->post('book');
//Then you can find in base:
$books = Book::find()
->where(['between', 'date', $start, $end])
->andWhere(['like', 'book', $bookName])
->all();
Don't forget to check the values given in the post.
source to share
Assuming the date stored as an integer represents a Unix timestamp, you can create a model class and apply yii \ validators \ DateValidator to the start
and end
attributes.
/**
* Class which holds all kind of searchs on Book model.
*/
class BookSearch extends Book
{
// Custom properties to hold data from input fields
public $start;
public $end;
/**
* @inheritdoc
*/
public function rules()
{
return [
['start', 'date', 'timestampAttribute' => 'start', 'format' => 'php:d/m/y'],
['end', 'date', 'timestampAttribute' => 'end', 'format' => 'php:d/m/y']
];
}
public function searchByDateRange($params)
{
$this->load($params);
// When validation pass, $start and $end attributes will have their values converted to unix timestamp.
if (!$this->validate()) {
return false;
}
$query = Book::find()->andFilterWhere(['between', 'date', $this->start, $this->end]);
return true;
}
}
See more about timestampAttribute
in this documentation .
source to share
use Yii2 Active Record and access books between two dates like this.
public static function getBookBetweenDates($lower, $upper)
{
return Book::find()
->where(['and', "date>=$lower", "date<=$upper"])
->all();
}
I am assuming you are using an active writing class and you have created a Book.php (appropriate name based on the table name). As a model file.
source to share