duanlang1196 2017-04-06 16:30
浏览 774
已采纳

Yii2日期范围搜索

i got this error when create date range search in search model.

Integrity constraint violation – yii\db\IntegrityException

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous

here's my model

 public $start_date;
    public $end_date;
public function rules()
        {
            return [
                [['attachment', 'id_client', 'delete_on', 'created_at', 'created_by', 'updated_at', 'updated_by', 'from_location', 'to_location','printed_at', 'lock','verify','verify_by','approved','approved_by'], 'integer'],
                [['policy_num'], 'autonumber', 'format'=>'formatPolicy'],
                [['policy_num','premium_policy'], 'string'],
                [['start_date','end_date'], 'date', 'format'=>'dd-MM-yyyy'],    
                [['from_location', 'to_location'], 'string', 'max' => 55],
                [['location_address'], 'string', 'max' => 100],
                [['attachment'], 'required'],
                [['deductible'], 'string', 'max' => 100],
                [['lock'], 'default', 'value' => '0'],
                [['lock'], 'mootensai\components\OptimisticLockValidator']
            ];
        }

here's my search model

    public function rules()
        {
            return [
                [['id', 'policy_num', 'attachment', 'id_client', 'delete_on','created_by', 'updated_by', 'printed_at'], 'integer'],
                [['cover_rate'], 'number'],
                [['start_date','end_date','created_at','updated_at'], 'date','format'=>'yyyy-mm-dd'],
            ];
        }

        public function search2($params)
            {
                $query = AskPolicy::find();
                $query->joinWith(['client'])->where(['id_client'=>Yii::$app->user->identity->id_client]);

                $dataProvider = new ActiveDataProvider([
                    'query' => $query,
                ]);

                $this->load($params);

            if (!$this->validate()) {
                // uncomment the following line if you do not want to return any records when validation fails
                // $query->where('0=1');
                return $dataProvider;
            }

            $query->andFilterWhere([
                'id' => $this->id,
                'policy_num' => $this->policy_num,
                'ask_policy.created_at' => $this->created_at,
                'ask_policy.updated_at' => $this->updated_at,
                'printed_at' => $this->printed_at,
            ]);

    //         $query->andFilterWhere(['>=', 'ask_policy.created_at', $this->start_date]);
//         $query->andFilterWhere(['<=', 'ask_policy.created_at', $this->end_date]);

            $query->andFilterWhere(['like',"(date_format(FROM_UNIXTIME(`created_at` ), '%Y-%m-%d' ))", $this->start_date])
            ->andFilterWhere(['like', "(date_format(FROM_UNIXTIME(`updated_at` ), '%Y-%m-%d' ))", $this->end_date]);

            return $dataProvider;
        }

if i use below code : search start date and end date not working

$query->andFilterWhere(['>=', 'ask_policy.created_at', $this->start_date]);
$query->andFilterWhere(['<=', 'ask_policy.created_at', $this->end_date]);

how to the best way convert integer datetime in Yii2 for date range search ? i was searching but not find tutorial with good explanation.

  • 写回答

2条回答 默认 最新

  • douju8113 2017-04-12 06:26
    关注

    your query is joined with client and both models has created_at field. you can set alias for current model with

    $query->alias('t');
    

    and alias joined table with

    $query->joinWith(['client as cli']);
    

    then if you want to use created_at from main model you can use t.created_at and if you want to use created_at from joined model you can use cli.created_at.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 远程安装一下vasp
  • ¥15 自己做的代码上传图片时,报错
  • ¥15 Lingo线性规划模型怎么搭建
  • ¥15 关于#python#的问题,请各位专家解答!区间型正向化
  • ¥15 unity从3D升级到urp管线,打包ab包后,材质全部变紫色
  • ¥50 comsol温度场仿真无法模拟微米级激光光斑
  • ¥15 上传图片时提交的存储类型
  • ¥15 VB.NET如何绘制倾斜的椭圆
  • ¥15 arbotix没有/cmd_vel话题
  • ¥15 odoo17的分包重新供应路线如何设置?可从销售订单中实时直接触发采购订单或相关单据