Select with Inner Join with Cakephp

Question

I have a little problem for some time regarding a project that I am developing in cakephp, mine view can’t read a select with Inner Join 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 ) 

Explaining better, this query is seeking the "username" from table A to table B, and this table B of mine view can do the reading normally. With this select my bank brings the consultation exactly what I need, but my view does not display the result and returns null. Or it says if I try to do the view like this: $post->users->username, returns an error that does not find the object users.

Answer 1

With JOIN's Cakephp places values inside their respective objects.

Probably your username is inside the object User:

$post->user->username

Give a pr() in its object $post so that it displays all the attributes.

Instead of doing sql pure, you could use the properties belongsTo(), hasMany() and hasOne() cakephp.

---

You need to query your controller and set the variable to exist 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 one link

Answer 2

If someone needs the resolution, you can do it by adding this code to the Postscontroller.

           public function index()
               {   
           $posts = $this->Posts->find('all', [
              'conditions' => ['Posts.online !=' => -1]])->order(['Posts.publication' => 'DESC'])->contain(['Users']);
           $this->set(compact(['posts']));
               }

Model

        public function initialize(array $config) {
            //funcao para trazer username do inner join
            $this->addBehavior('Timestamp');
            $this->displayField('title');
            $this->belongsTo('Users', [
                'foreignKey' => 'user_id',
                'joinType' => 'INNER',
            ]);
       }