CODEWITHSUNDEEP
cakephpcakephp-2.0
jazibobs
jazibobs

Reputation: 422

Returning values with a foreign key CakePHP

I'm currently adding to the blog example from the CakePHP documentation and have created a users table in order to assign posts to a certain user and I have added a user_id field to my posts table

I wrote the following function in my posts controller:

public function viewuser($user_id = null){

    if (!$this->Post->exists($user_id)) {
        throw new NotFoundException(__('Invalid user'));
    }
    $options = array('conditions' => array('Post.user_id' => $user_id));
    $this->set('posts', $this->Post->find('all', $options));
}

but it returns all posts by all users when I access both http://localhost/cake-app/posts/viewuser/1 and http://localhost/cake-app/posts/viewuser/2 rather than only posts by a the user with the posted $user_id value.

Could someone help me with this problem? I'm new to CakePHP so it's probably a simple fix.

EDIT - SQL generated is as follows:

1   SELECT COUNT(*) AS `count` FROM `jaredisalie`.`posts` AS `Post` WHERE `Post`.`id` = 1   
2   SELECT `Post`.`id`, `Post`.`title`, `Post`.`user_id`, `Post`.`imageurl`, `Post`.`category_id`, `Post`.`summary`, `Post`.`content`, `Post`.`created`, `Post`.`modified`, `Category`.`id`, `Category`.`title`, `User`.`id`, `User`.`username`, `User`.`name`, `User`.`password`, `User`.`role`, `User`.`created`, `User`.`modified` FROM `jaredisalie`.`posts` AS `Post` LEFT JOIN `jaredisalie`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) LEFT JOIN `jaredisalie`.`users` AS `User` ON (`Post`.`user_id` = `User`.`id`) WHERE `Post`.`user_id` = 1      
3   SELECT `Category`.`id`, `Category`.`title` FROM `jaredisalie`.`categories` AS `Category` WHERE 1 = 1        
4   SELECT `Posts`.`id`, `Posts`.`title`, `Posts`.`user_id`, `Posts`.`imageurl`, `Posts`.`category_id`, `Posts`.`summary`, `Posts`.`content`, `Posts`.`created`, `Posts`.`modified` FROM `jaredisalie`.`posts` AS `Posts` WHERE `Posts`.`category_id` IN (1, 2, 3)      
5   SELECT `Post`.`id`, `Post`.`title`, `Post`.`user_id`, `Post`.`imageurl`, `Post`.`category_id`, `Post`.`summary`, `Post`.`content`, `Post`.`created`, `Post`.`modified`, `Category`.`id`, `Category`.`title`, `User`.`id`, `User`.`username`, `User`.`name`, `User`.`password`, `User`.`role`, `User`.`created`, `User`.`modified` FROM `jaredisalie`.`posts` AS `Post` LEFT JOIN `jaredisalie`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) LEFT JOIN `jaredisalie`.`users` AS `User` ON (`Post`.`user_id` = `User`.`id`) WHERE 1 = 1 ORDER BY `Post`.`modified` desc LIMIT 20        
6   SELECT COUNT(*) AS `count` FROM `jaredisalie`.`posts` AS `Post` LEFT JOIN `jaredisalie`.`categories` AS `Category` ON (`Post`.`category_id` = `Category`.`id`) LEFT JOIN `jaredisalie`.`users` AS `User` ON (`Post`.`user_id` = `User`.`id`) WHERE 1 = 1

Upvotes: 0

Views: 207

Answers (2)

AD7six
AD7six

Reputation: 66358

You are overwriting your variables

This chunk of code:

$options = array('conditions' => array('Post.user_id' => $user_id));
$this->set('posts', $this->Post->find('all', $options));

Is responsible for query 2:

SELECT ... FROM `jaredisalie`.`posts` ... WHERE `Post`.`user_id` = 1

That is doing exactly what you're expecting. That's the last query relevant to the code in the question, but there are 4 more queries.

It's obvious/evident that there are subsequent finds on the post model after this in the same request from queries 5 and 6:

SELECT ... FROM `jaredisalie`.`posts` AS `Post` ... WHERE 1 = 1 ORDER BY `Post`.`modified` desc LIMIT 20        
SELECT COUNT(*) AS `count` FROM `jaredisalie`.`posts` AS `Post` ... WHERE 1 = 1

As such in the code that you're using but is not in the question, there's probably a line like this:

$this->set('posts', $this->paginate());

Or similar - either use a different variable name or if it's unnecessary just remove it.

You have a logic error

This code:

if (!$this->Post->exists($user_id)) {
    throw new NotFoundException(__('Invalid user'));
}

And the first query:

SELECT COUNT(*) AS `count` FROM `jaredisalie`.`posts` AS `Post` WHERE `Post`.`id` = 1

Are illogical. The variable is (allegedly) a user id - but you're checking if a post exists with that id.

Upvotes: 1

Christoph Paster
Christoph Paster

Reputation: 36

first of all, checking $this->Post->exists() with $user_id won't give you the wanted exception. I guess you want to check, if the User exists, use $this->Post->User->exists() instead.

Second - check your Model namings and your relationships between them (in Category,Post,User - Model) You've somehow got a Posts and a Post Model in your SQL. I guess, you've added a 's' where it shouldn't be ;)

Upvotes: 2

Related Questions