CODEWITHSUNDEEP
phpyii2
Luciano Nascimento
Luciano Nascimento

Reputation: 2600

Yii2 MySQL Order By Relation Count

Table 1 - User:

ID Name
1 Jonh
2 Mark
3 King

Table 2 - Book:

ID user_idstatus ...
1 1 1 ...
2 1 1 ...
3 1 1 ...
4 2 1 ...
5 1 0 ...
6 1 0 ...

Code: 

$query = User::find();
$query->joinWith('books');
$query->select(['user.*', 'COUNT(book.id) AS booksCount']);
$query->andWhere(['book.status' => 1]); // Problem Here!
$query->groupBy(['user.id']);
$query->orderBy(['booksCount' => SORT_DESC]);

Problem:

The query is working properly, but it's not returning the user with id = 3. If I remove the line $query->andWhere(['book.status' => 1]); it works fine and return all users.

What should I change to list all users, even those who do not have a related book with status = 1?

Upvotes: 1

Views: 931

Answers (2)

Luciano Nascimento
Luciano Nascimento

Reputation: 2600

I found the answer:

$query = User::find();

$query->joinWith(['books' => function ($subquery) {
    $subquery->onCondition(['book.status' => 1]);
}]);

$query->select(['user.*', 'COUNT(book.id) AS booksCount']);
$query->groupBy(['user.id']);
$query->orderBy(['booksCount' => SORT_DESC]);

Upvotes: 2

shamsup
shamsup

Reputation: 2022

Instead of using COUNT(book.id), if the status of the book is either 0 or 1, you can use SUM(book.status) to get the number of books the user has. Then you can remove your WHERE book.status = 1 clause, and it will return all the users with the number of books they have, even in user 3's case where they have 0 books.

The Problem

The real problem is in your where clause. Because WHERE is processed before grouping and user 3 doesn't have any rows where book.status = 1, then the user has no rows which are included in the base query. Therefor the user isn't present during/after the grouping.

If you want a pretty good idea of a catch-all case where you can count rows based on a condition, using COUNT(CASE WHEN book.status IS NULL THEN NULL ELSE NULLIF(0,book.status) END) will also give you the result you're looking for. Because COUNT() will not count rows where the expression is NULL, this would allow the book.status to be -1, 1, 2, and any other number as long as it isn't 0 (or NULL in user 3's case), and still be included in the count.

Upvotes: -1

Related Questions