Query Builder - JOIN not working.

Question

Query Builder - JOIN not working.

Crank1d Jun '15

Created Jun '15	Last Reply Jun '15	Replies 2	Views 1102	Votes 0

Crank1d 2.6k

Jun '15

It could be because I just started using PhalconPHP so I am not that experienced yet, but I am unable to use results from joined table in views (passing variable to view). How to use single result and pass data to view? I have "main" Posts model and Articles, Images, Videos models, which depend (belongsTo) on Posts. Here is my code:

SQL code

CREATE TABLE IF NOT EXISTS `posts` (
`ID` int(11) NOT NULL,
`post_title` varchar(255) NOT NULL,
`post_slug` varchar(255) NOT NULL,
`post_uid` int(11) NOT NULL,
`post_category_id` int(11) NOT NULL,
`post_views` int(11) NOT NULL DEFAULT '0',
`post_rating` int(11) NOT NULL DEFAULT '0',
`post_status` tinyint(2) NOT NULL DEFAULT '0',
`post_created` datetime DEFAULT NULL,
`post_modified` datetime DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `post_uid` (`post_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `posts_type_articles` (
  `article_id` bigint(20) unsigned NOT NULL,
  `post_id` bigint(20) unsigned NOT NULL,
  `article_bannerimg` varchar(255) NOT NULL,
  `article_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Models

namespace App\Common\Models;

class BaseModel extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
    }   
}

----

namespace App\Common\Models;

class Posts extends BaseModel
{
    public function getSource()
    {
        return 'posts';
    }

    public function initialize()
    {
        $this->hasOne('ID', 'App\Common\Models\Articles', 'post_id', array('alias' => 'Posts'));
    }
}

----

namespace App\Common\Models;

class Articles extends BaseModel
{
    public function getSource()
    {
        return 'posts_type_articles';
    }

    public function initialize()
    {
        $this->belongsTo('post_id', 'App\Common\Models\Posts', 'ID', array('alias' => 'Articles'));
    }
}

Controller action

public function viewAction($id)
{
    //Getting the first row
    $post = $this->modelsManager->createBuilder()
            ->from("App\Common\Models\Posts")
            ->join('App\Common\Models\Articles', 'App\Common\Models\Posts.ID = App\Common\Models\Articles.post_id')
            ->where("App\Common\Models\Posts.ID = :id:",array("id" => $id))
            ->getQuery()
            ->execute()
            ->getFirst();

        $this->view->setVar('post', $post);
    }

...and view.phtml file

<article class="post">
<?php echo $this->tag->image($post->article_bannerimg); ?>
<h1><?php echo $post->post_title; ?></h1>
<div class="entry"><?php echo $post->article_content; ?></div>
</article>

PROBLEM: $post->post_title; is rendered fine while other 2 values from "posts_type_articles" table show:

Notice: Access to undefined property App\Common\Models\Posts::article_bannerimg and Notice: Access to undefined property App\Common\Models\Posts::article_content

Same thing also happens when using PHQL.

Shad Mickelberry · Answer 1 · 2015-06-02T12:12:33-07:00

Have you tried using the model relationship built into Phalcon? Once the relationship is established you have a lot of tools on hand.

public function viewAction($id)
{
    //Getting the first row
    $post = Posts::findFirst($id);

    //this can also be done in the view but if you want
    $article = $post->getArticle();

        $this->view->setVar('post', $post);
        $this->view->setVar('article', $article);
    }

Or in view

    $post->getArticle()->article_bannerimg;

Haven't been able to test but hopefully this will help a bit.

Crank1d · Answer 2 · 2015-06-02T12:37:25-07:00

Thanks for reply, but it is not working. Other solution i had tried is:

$post = $this->modelsManager->createBuilder()
    ->columns('Post.post_title, Article.article_content')
    ->addfrom("App\Common\Models\Posts","Post")
    ->join("App\Common\Models\Articles","Article.post_id = Post.id","Article")
    ->where("Post.id = :id:",array("id" => $id))
    ->getQuery()
    ->execute()
    ->getFirst();

..but, I cant use all data from both table, I need to define them 1 by 1 in "->columns()", for example. if its from Posts table (parent), then "Post.post_title, Post.post_views" or Articles (children) table "Articles.article_content, Article.article_bannerimg".

This method works, but as I said, i dont have access to all data from both tables until I define them 1 by 1, manually.

Andres Gutierrez · Answer 3 · 2015-06-06T09:24:36-07:00

You can do:

$rows = $this->modelsManager->createBuilder()
    ->columns('Post.*, Article.*')
    ->addfrom("App\Common\Models\Posts","Post")
    ->join("App\Common\Models\Articles","Article.post_id = Post.id","Article")
    ->where("Post.id = :id:",array("id" => $id))
    ->getQuery()
    ->execute()
    ->getFirst();
foreach ($rows as $row) {
    echo $row->post->post_title;
    echo $row->article->article_content;
}