Select com inner join com CakePHP

Question:

I've been having a little problem for some time regarding a project I'm developing in cakephp , my view can't read a select with an inner join from the table.

Controller:

           public function initialize()
       {

           $posts = $this->Posts->find("all",array(
              "joins" => array(
                  array(
                    "table" => "users",
                    "alias" => "User",
                    "type" => "INNER",
                    "conditions" => array("Post.user_id = User.id "),
                    "fields" => array('Post.*', 'User.username')
                   )
               )
            )
         );

model;

      public function initialize(array $config) {
          $this->addBehavior('Timestamp');
          $this->displayField('title');

         //join     
         $this->belongsTo('User');
}

View

<?= $post->username ?>

SQL code

SELECT posts.*,
       users.username
FROM   posts
       INNER JOIN users
               ON ( posts.user_id = users.id ) 

To explain better, this query is looking for the " username " from table A to table B, and this table B my view can read normally. With this select my database brings the query exactly what I need, but my view does not show the result and returns null. Or it says if I try to make the view like this: $post->users->username, it returns an error that doesn't find the users object.

Answer:

With JOIN's CakePHP puts values ​​into their respective objects.

Probably your username is inside the User object:

$post->user->username

Give your $post object a pr() so that it displays all attributes.

Instead of doing pure sql , you could use CakePHP's belongsTo() , hasMany() and hasOne() .

---

You need to do the query in your controller and set the variable so that it exists in your view.

controller

$posts = $this->loadModel('Posts');
$all = $posts->find('all');

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

Model Posts – Using belongsTo()

class PostsTable extends Table
{

    public function initialize(array $config)
    {
        $this->belongsTo('Users');
    }

}

If you haven't created your models, check this link