Edit return the COUNT of a column

mauro218

I'm trying to return the count of ocurrences from a Left join table.

Entity table:
idEntity        EntityName
1                  ABC
2                  XYZ

Qualification table:
idEntity        State
1                  Florida
1                  California
2                  New York

Expected Result:
idEntity        EntityName        Total
1                  ABC                  2
2                  XYZ                  1

$editor = Editor::inst( $db, 'Entity', 'idEntity' )
    ->fields(
        Field::inst( 'Entity.idEntity' ),
        Field::inst( 'Entity.EntityName' ),
        Field::inst( 'COUNT(Qualification.idEntity) as Entity.Total' )
    )
    ->leftJoin( 'Qualification', 'Qualification.idEntity', '=', 'Entity.idEntity' );

However, it's returning only the first record with Total = 3

What am I doing wrong?

Thanks for the help

Jhonny

allan

Hi Jhonny,

Unfortunately, at the moment the Editor fields cannot accept SQL methods as parameter names, which is why this won't work. Longer term it is something I would like to add, but at the moment what you will need to do is use an array join and use the length property of the resulting array.

Allan

mauro218

Allan,

Is this the right way to get the array join? It's not returning anything at all

$editor = Editor::inst( $db, 'Entity', 'idEntity' )
    ->fields(
        Field::inst( 'Entity.idEntity' ),
        Field::inst( 'Entity.EntityName' )
    )
    ->join(
        Join::inst( 'Qualification', 'array' )
            ->join( 'idEntity', 'idEntity' )
            ->fields(
                Field::inst( 'idEntity' ),
                Field::inst( 'FilingNumber' )
            )
    );

Jhonny

mauro218

Nevermind Allan, it worked!!! Thanks!