Akelos Framework v1 forum archive. This forum is no longer maintained. To report bugs please visit https://github.com/akelos/akelos/issues

General: count with group by condition

Bottom of Page

1 to 6 of 6

- CommentAuthordpinte
Hi,

What is the correct way to generate the following information ? I have a model of course session (Session) and I want to create a report with the number of sessions per language.

This is easily done in sql "select language, count(*) from sessions group by language; with the following output

+----------+----------+
| language | count(*) |
+----------+----------+
| English | 11 |
| French | 2 |
+----------+----------+

I can do the following in Akelos :

$sessions_per_languages = $this->Session->count(array('group' => 'language'));

but this gives only the count(*) column and not the language column.

Is there a way to do that directly in Akelos ?

Thanks

Didrik
- CommentAuthorinsanet
hi didrik, i dont know how to do it with an akelos method, but if it is ok to you doing it with sql you can do it like this:
```
$sessions =& $this->Session->findBySql('select language, count(*) as column1 from sessions group by language');
```
important thing: as you can see i used an alias for count. i do this because the name of the columns on the select statement will be the properties of the object, and this properties need to match the name of the columns of the table (sorry for the redundancy) or it will fail. so you can access what you want like this:
```
      foreach ($sessions as $session){
                $session->column1           //count(*)
                $session->language
        }
```
Extension:

in case you want an alias that is not a column name of the table, you can use any alias, if you declare it as a propertie in the model class.
```
class Session extends AKActiveRecord{
             var $random_alias;
```
and now is ok to use "count(*) as random_alias"
- CommentAuthordpinte
Hi Daniel,

The world is small ;-)

Thanks for the answer. I'll do that

Didrik
- CommentAuthorGKSR
What if the result is fetched from two tables, for example,

$this->plists = $this->Location->_db->execute("SELECT l.property_id, count(*) as count FROM bedrooms b, locations l WHERE l.city = '$this->city' AND b.property_id = l.property_id GROUP BY b.property_id HAVING count = $this->bedroomno");

I am unable to get the property_id ($plist->property_id). when I execute the above query in mysql, i am able to get both the property_id and count. I tried using foreach() instead of {loop plists}, but no result. Can i know where I went wrong?
- CommentAuthorinsanet
hi, like i said above:
```
important thing: as you can see i used an alias for count. i do this because the name of the columns on the select statement 
will be the properties of the object, and this properties need to match the name of the columns of the table (sorry for the redundancy)
or it will fail. so you can access what you want like this:
```
i will add that you need alias for the selected columns you want to retrieve.

so you should make an alias for 'l.property_id' too. and these alias need to be known properties for the model.(like columns name from the model your executing the sql or declared properties in the model , see Extension above)

And by the way, i dont think execute and findBySql are the same. so maybe that could be the problem.
- CommentAuthorGKSR
Thanx a lot. The last line of your comment worked for me.

1 to 6 of 6

Back to Discussions Top of Page

Akelos PHP Framework Forum

General: count with group by condition