Select with inner join with CakePHP

Question

Select with inner join with CakePHP

Navigation

#1 by (0 votes)
#2 by (0 votes)

3

I have a problem some time ago regarding a project I'm developing in cakephp , my view can not read a select with < in> 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 fetching " username " from table A to table B, and this table B my view is able to read normally. With this select my bank 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, returns an error that does not find the users object.

php mysql cakephp

asked by anonymous 24.11.2015 / 12:26

2 answers

0

With JOIN's CakePHP puts the values inside their respective objects.

Your username is probably within the User object:

$post->user->username

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

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

You need to query 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 have not created your models, check out this link

24.11.2015 / 12:39

Format the value of the ComboBox pulled from the SQL database (LINQ) in Project C # (WPF) localhost / phpmyadmin / error 1146

score 0 · Accepted Answer

If someone needs the resolution, you can do it by adding this code in 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',
            ]);
       }