Skip to content
This repository

Fields specified in relationships are ignored #635

Open
danives opened this Issue · 1 comment

3 participants

Dan Ives Nate Abele Simon JAILLET
Dan Ives

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

Nate Abele
Owner

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" } }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.