Fields specified in relationships are ignored

[HandsomeStrife]

Hi,

I am trying to get a count of a related table with my query - however I do not know the correct way to do this. I tried specifying the field manually in the find:: method, but that returns only the field. I then thought I should do it using the relationships and specify the field in the relationship. This is a link to the example code I used:

http://pastium.org/view/06085ba7423faa13f5316cd8a1eb33f2

However doing that, the 'fields' variable is ignored - as can be seen in the generated SQL. I tried recreating the SQL statement and adding the field in manually, but it seems lithium doesn't then know how to bind the data to a model:

http://pastium.org/view/06085ba7423faa13f5316cd8a1eb133d

If someone is able to help me with how to get an associated tables count, that would be fantastic.

Thanks,

Dan

[nateabele]

Code:

public $hasMany = array(
    'Comment'=>array(
        'key'=>array('id'=>'post_id'),
        'order'=>array('created'=>'DESC')
    ),
    'Favourite'=>array(
        'key'=>array('id'=>'post_id'),
        'order'=>array('created'=>'DESC')
    ),
    'Like'=>array(
        'key'=>array('id'=>'post_id'),
        'order'=>array('created'=>'DESC')
    ),
    'Dislike'=>array(
        'key'=>array('id'=>'post_id'),
        'order'=>array('created'=>'DESC')
    ),
    'CommentCount'=>array(
        'key'=>array('id'=>'post_id'),
        'order'=>array('created'=>'DESC'),
        'fields'=>array('count(Comment.id) as "count"'),
        'source'=>'comment',
        'to'=>'app\models\Comment'
    )
);

public function recent() {
    return Post::find('all',array(
             'conditions'=>array('Post.published'=>1), 
             "order"=>array('Post.created'=>'DESC'), 
             "group"=>"Post.id", 
             "with"=>array('User','Locations','Like','Dislike','CommentCount')));
}

// SQL generated

'SELECT * FROM `posts` AS `Post` LEFT JOIN `users` AS `User` ON `Post`.`author` = `User`.`id` LEFT JOIN `locations` AS `Locations` ON `Post`.`location` = `Locations`.`id` LEFT JOIN `likes` AS `Like` ON `Post`.`id` = `Like`.`post_id` LEFT JOIN `dislikes` AS `Dislike` ON `Post`.`id` = `Dislike`.`post_id` LEFT JOIN `comments` AS `CommentCount` ON `Post`.`id` = `CommentCount`.`post_id` WHERE Post.published = 1 GROUP BY Post.id ORDER BY Post.created DESC; '

Result:

$result = Post::find('all',array(
'fields'=>array('count(Comment.id)'),
'conditions'=>array('Post.published'=>1), 
"order"=>array('Post.created'=>'DESC'),
 "group"=>"Post.id", "with"=>array('User','Locations','Like','Dislike','Comment')
));
$result->to('array')

// returns this
array(3) { 
[0]=> array(1) { 
  ["id)"]=> string(1) "0" 
} 
[1]=> array(1) { 
  ["id)"]=> string(1) "8" 
} 
[2]=> array(1) { 
  ["id)"]=> string(1) "0" 
}
 }

$result = Post::find('all',array(
'fields'=>array('Post.*','count(Comment.id)'),
'conditions'=>array('Post.published'=>1), 
"order"=>array('Post.created'=>'DESC'), 
"group"=>"Post.id", 
"with"=>array('User','Locations','Like','Dislike','Comment')
));
$result->to('array')

// returns this
array(3) { [0]=> array(2) { [""]=> array(1) { ["id)"]=> string(1) "3" } ["*"]=> string(1) "0" } [1]=> array(2) { [""]=> array(1) { ["id)"]=> string(1) "2" } ["*"]=> string(1) "8" } [2]=> array(2) { [""]=> array(1) { ["id)"]=> string(1) "1" } ["*"]=> string(1) "0" } }

[mariuswilms]

@jails Are you working on this? Just asking as you've assigned yourself to the issue.

[jails]

nope !

[mariuswilms]

aha! :p

[mariuswilms]

Given an invoice hasMany positions, this is how you can get the number of positions an invoice has as 'positions_count' for each Invoice. 'group' does the trick here.

        $results = Invoices::find('all', [
            'fields' => [
                'Invoices.number',
                'COUNT(Positions.id) AS positions_count',
            ],
            'group' => ['Invoices.id'],
            'with' => ['Positions']
        ]);

[mariuswilms]

Fields in relationship definitions are not taken into account as described, I assume the problem lies within Database::$_strategies['joined']. That strategy does not seem to copy the relationships fields into the query.