php 如何在Yii2中添加“CASE WHEN...”到有很多关系

2uluyalo 于 2023-03-28 发布在 PHP

关注(0)|答案(1)|浏览(219)

正确的Mysql请求

SELECT  `task`.`type` AS `type`, `task`.`user_author_id` AS `user_author_id`
FROM `task_goal`
LEFT JOIN `user`
          ON `task_goal`.`user_author_id` = `user`.`id`
LEFT JOIN `task`
    ON (`task_goal`.`id` = `task`.`goal_id`) AND CASE WHEN `task`.`type` = 'personal' and `task`.`user_author_id` != `user`.`id`
     THEN `user`.`show_personal_task` = 1
     ELSE true
        END;

Yii2请求task_goal并加入HasMany关系“tasks”

$query = TaskGoal::find();
$query->select(['task_goal.*','task_goal.type AS goal_type'])->andWhere(['task_goal.active'=>true])
->joinWith([
'tasks'=>function(ActiveQuery  $itemQuery) {
    $itemQuery->andOnCondition(['task.active' => true]);
}]);

关系

public function getTasks() {
return $this->hasMany(Task::class, ['goal_id' => 'id']);
}

如何添加CASE WHEN…

CASE WHEN `task`.`type` = 'personal' and `task`.`user_author_id` != `user`.`id`
                          THEN `user`.`show_personal_task` = 0
                           ELSE true
                     END

和“任务”有很多关系吗？

php

来源：https://stackoverflow.com/questions/75810223/how-add-case-when-to-has-many-relation-in-yii2

1条答案

按热度按时间

3wabscal1#

当我测试andOnCondition时，我添加了[ ]：），所以不要

$itemQuery->andOnCondition("CASE WHEN `task`.`type` = 'personal' 
                              THEN `user`.`show_personal_task` = 1 and `task`.`user_author_id` != `user`.`id`
                               ELSE true
                         END");

并细请

$query = TaskGoal::find();

    $query->accessFilter('goal')->select(['task_goal.*','task_goal.type AS goal_type'])->andWhere(['task_goal.active'=>true])
        //->select(["milestone_item.* as milestonessss"])
        //->leftJoin(['milestone_item' => $milestone ],'milestone_item.goal_id = task_goal.id')
        ->joinWith(['milestone' => function(ActiveQuery $q) {
            $q->select('task_milestone.*');
            $q->accessFilter('milestone')->andWhere(['task_milestone.active'=>true]);
        }])
        ->joinWith('userAuthor')
        ->joinWith([
        'tasks'=>function(ActiveQuery  $itemQuery) {
            $itemQuery->andOnCondition(['task.active' => true]);
            $itemQuery->joinWith('userAuthor');
            if (!empty($this->user_responsible_id_array)) {
                $itemQuery->andOnCondition(['task.user_responsible_id'=>$this->user_responsible_id_array]);
            }
            if (!empty($this->status_array)) {
                $itemQuery->andOnCondition(['task.status'=>$this->status_array]);
            }
            if (!empty($this->urgently)) {
                $itemQuery->andOnCondition(['task.urgently' => $this->urgently]);
            }
            if (!empty($this->influence)) {
                $itemQuery->andOnCondition(['task.influence' => $this->influence]);
            }
            if (!empty($this->name)) {
                $itemQuery->andOnCondition(['like', 'task.name', $this->name]);
            }

            if (!empty($this->deadline_start) && !empty($this->deadline_end)) {
                $itemQuery->andOnCondition(['>=','task.deadline',strtotime($this->deadline_start . ' 00:00:00')])
                    ->andOnCondition(['<=','task.deadline',strtotime($this->deadline_end . ' 23:59:59')])
                    ->andOnCondition(['!=','task.deadline',null]);
            }

            if (!empty($this->completed_date_start) && !empty($this->completed_date_end)) {
                $itemQuery->andOnCondition(['>=','task.completed_date',strtotime($this->completed_date_start . ' 00:00:00')])
                    ->andOnCondition(['<=','task.completed_date',strtotime($this->completed_date_end . ' 23:59:59')])
                    ->andOnCondition(['!=','task.completed_date',null]);
            }

            $itemQuery->andOnCondition("CASE WHEN `task`.`type` = 'personal' 
                          THEN `user`.`show_personal_task` = 1 and `task`.`user_author_id` != `user`.`id`
                           ELSE true
                     END");
    }]);

赞(0）回复(0）举报 2023-03-28

我来回答

php 如何在Yii2中添加“CASE WHEN...”到有很多关系

1条答案

相关问题

热门标签

最新问答