Get the least number of a relation

Yii 1.1 General Discussion
1

Title is probably saying nothing at all but i have no idea how to describe it.

Say i have 2 models

User and Group

and to make not to complicated users can only be in 1 Group at the same time, so an n-1 relation

now i wanted to create scope or a relation in the Group model that would give me the id of the Group with the least number of Users

i just can’t figure it out,

first i tried it as a STAT relation with


'userCount'=>array(self::STAT, 'User', 'groupid'),

but i have no idea how to aproach that, because that will still give me all the groups and not just the one with the least users

at some point i was doing something with


'condition'=>'COUNT(users.*)==MIN(users.*)'

but that didn’t seem to work either.

am a bit stuck here, it’s rawtaz on irc who suggest scope or relation but there i’m even more clueless…

it might be the lack of sleep but i’ve tried about everything i can think of with to the docs and the class reference.

some explanation would be greatly apreciated

2

You don’t need a relation, keep it simple.

First of all, the staff you are going to do is not related to some specific user/group, so you need to create a static method, like this one:




public static function getSmallerGroups()

{

   $criteria=new CDbCriteria;

   $criteria->condition="id IN SELECT group_id FROM users WHERE COUNT(users.*)==MIN(users.*)";

   return Group::model()->findAll($condition);

}

You can access this method as:




Groups::smallerGroups;

Also keep in mind that is possible to receive more than one "smaller" group.

3

This one’s possible, but tricky. I’d suggest trying to use a normal relation, but grouping and order the results by count. I’ve got to go to work, so haven’t tested this, but something along the lines of the following may work.


'leastUsers'=>array(self::HAS_ONE, 'Group', 'groupid',

    'select'=>'Group.*, count(*) as users',

    'join'=>'LEFT JOIN User ON User.groupid=Group.groupid'

    'group'=>'Group.groupid',

    'order'=>'users',

),

I expect that will fail, I think Yii would alias the table to something other than Group. Run the query in test mode so you can see the query generated in the log. Then, change the contents of the join, select, group statements to use the right table alias.

Remember, a relation generally returns a model. They generally give a model of the type given as the second parameter. If you’re having trouble figuring out how to do what you want as a relation, you might be better-off just writing a function to do a normal find. You can use Group::model->findAllBySql(‘SELECT Group.* …’) to run pretty-much any SQL search. If you don’t know how to do the search you want, there are plenty of guides on the internet for how to build SQL statements to do XYZ.

4

I tend to use a pure DAO solution in cases like this. All you want is an id, right? To get that you have to issue a simple SQL statement. There’s no requirement to stuff this into the “AR way of doing queries”:




public querySmallestGroup() {

    $sql='SELECT g.id FROM Group g LEFT JOIN Users u ON g.id=u.groupid ORDER BY COUNT(g.id) DESC LIMIT 1';

    return $this->db->createCommand($sql)->queryScalar();

}

Usage:


$id=Group::model()->querySmallestGroup();
5

This is easier, but what about if there are 2 empty group? Wich one will be returned? We should forseen that there is the possibility of more than one answer.

6

Yeh, but that’s isn’t a problem because i will order by RAND(), if there is no real smallest group i pick them random, to balance the groups you see


  $sql='SELECT g.id FROM Group g LEFT JOIN Users u ON g.id=u.groupid ORDER BY COUNT(g.id) DESC, RANDOM() LIMIT 1';

oh and mike, it’s public function querySmallestGroup() {

7

the sql mike provided isn’t what it should be

mysql gives a warning but still continues

pgsql just fails on this and trows an error

if you have


id |  name  |   groupid 

------------------

1  |  A          |     1

2  |  B          |     1

3  |  C          |     2

4  |  D          |     2

and then try


SELECT groupid FROM "Player" ORDER BY COUNT(goupid) DESC, RANDOM() LIMIT 1;

that will give you an error in about any language

the correct way is


SELECT groupid FROM "Player" GROUP BY groupid ORDER BY COUNT(*), RANDOM() LIMIT 1;

just posing it if someone runs into the same problem i ran into

8

Pro tip: If you care about efficiency, never use “ORDER BY RANDOM()”. Even if you only need one result, ORDER BY RANDOM() will generate a random number for every table row. Generating a quasi-random number is actually fairly complex, I’d expect to see visible slowdown set in pretty quickly. You’d be much better is most cases to pull the whole set (or all groups with the minimum) and to select one from the list in PHP.

9

The SQL should be fine, at least according to the MySQL docs:

http://dev.mysql.com…#function_count

I never got a warning here, but i agree: using COUNT(*) should put you on the safe side.