CODEWITHSUNDEEP
mysqlsqlyii2
Jichao
Jichao

Reputation: 41805

Yii2 how to translate cascade select in where clause to activequery?

Two tables question, answer, I need to dump all the unanswered questions for specific user. In sql

select * from question where question.user_id = user_id
and (select count(*) from answer where answer.question_id = question.id) = 0

But how could I turn this command to activequery?

Question::find()->where(['user_id' => user_id]) ....

Upvotes: 0

Views: 66

Answers (2)

Pedro del Sol
Pedro del Sol

Reputation: 2841

An active query to find each question for a given user with no answer. 

Question::find()->leftJoin('answer', 'answer.question_id=question.id')
                ->where(['question.user_id' => $user_id])
                ->andWhere(['answer.id' => null)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Your query could be write this way 

  select * from question 
  inner join answer on answer.question_id = question.id
  where question.user_id = user_id
  group by question.id 
  having count(*) > 0

and in activeRecored you could use 

 Question::find()->innerJoin('answer', answer.question_id = question.id)->
        where(['user_id' => user_id])->
        groupBy('id')->
        having('having count(*) > 0')->

Upvotes: 1

Related Questions