Active Data provider

Yii 2.0
1

Hi,
I am getting stuck in database query. I have a table with two column one for date and other for time.

I want to filter the records with current date time means if only those records will show those contain the date and time value greater than or equal to current datetime. Can you please guide in writing this sql query?

Thanks

2

Hi,

Could you show us the following?

  1. the table schema
  2. the definition of the model, and
  3. what you have tried so far
3

Hi @softark,
These are my table columns

        'id' => Yii::t('app', 'ID'),
        'name' => Yii::t('app', 'Name'),
        'unique_id' => Yii::t('app', 'Unique ID'),
        'user_id' => Yii::t('app', 'User ID'),
        'created_at' => Yii::t('app', 'Created At'),
        'updated_at' => Yii::t('app', 'Updated At'),
        'is_active' => Yii::t('app', 'Is Active'),
        'tournament_id' => Yii::t('app', 'Tournament ID'),
        'match_address' => Yii::t('app', 'Match Address'),
        'match_city' => Yii::t('app', 'Match City'),
        'match_state' => Yii::t('app', 'Match State'),
        'match_country' => Yii::t('app', 'Match Country'),
        'match_zipcode' => Yii::t('app', 'Match Zipcode'),
        'ground_id' => Yii::t('app', 'Ground ID'),
        'time' => Yii::t('app', 'Time'),
        'date' => Yii::t('app', 'Date'),
        'match_type' => Yii::t('app', 'Match Type'),
        'format' => Yii::t('app', 'Format'),
        'over' => Yii::t('app', 'Over'),

and there is nothing special in my model definition, it is generated model with gii tools with labels , rules and table name function

 class Match extends ActiveRecord
{
   /**
 * {@inheritdoc}
 */
    public static function tableName()
   {
       return 'match';
    }
}

and I have tried below mentioned code in my controller

if(isset($requestParams['upcoming']) && $requestParams['upcoming']==1){
            $query->andHaving(['>=','date',date('Y-m-d')]);
        }
    
        $dataProvider =  Yii::createObject([
            'class' => ActiveDataProvider::className(),
            'query' => $query,
            'pagination' => [
                'params' => $requestParams,
                'pageSize'=>50,
            ],
            'sort' => [
                'params' => $requestParams,
            ],
        ]);
4

Looks OK to me. How does it fail? What error do you get with it?

5

Hi @softark,
I want to filter the records based on current date and time.
by using above code, I am able to filter records based on current date and I am not getting idea , how can I filter records based on current datetime

7

This is MySQL specific, and I haven’t tested it, but something like the following may work.

$query->andWhere(
    '(date_add(CAST(`date` AS DATETIME), INTERVAL `time` HOUR_SECOND)) >= :now',
     [':now' => $date('Y-m-d H:i:s')]);

Please check date_add() in MySQL’s manual.

8

Thanks @softark for your valuable answer.