CODEWITHSUNDEEP
mysqlsqlyii2
AceVez
AceVez

Reputation: 301

Yii2 - Retrieving model based on Count in Related Table

This is part of my db schema

Db's schema

As you can see based on the schema a Vacancy can appear in one or more Faculties. I want to retrieve Vacancies that:

  1. Appear only in one Faculty and that Faculty only.
  2. Appear in more than one Faculty.

This way I can display Vacancies to their respective Faculty Admin (with the exception for Vacancies that appears on more than one Faculty will be displayed to a special Admin).

I tried using Vacancy::find()->where() clause but it seems that where() doesn't take COUNT() as an argument.

Upvotes: 1

Views: 39

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

for filter an aggregated result in a query you should use 

Vacancy::find()
  ->leftJoin('vacancy.id = facultyvacancy.vacancy_id')
  ->having('count(*) = 1')->groupBy('your_column_for_grouping')

Upvotes: 1

Related Questions