1
votes

I'm having difficulty understanding joins in Phalcon. I'm not sure if I even need to use joins or if related tables are somehow mapped automatically. I've always avoided working with ORMs in the past, so the concept is all a bit new to me!

I have two tables - pages and sections

 class Pages extends \Phalcon\Mvc\Model {

    public $page_id;
    public $page_section_id;
    public $page_title;
    public $page_url;
    public $page_description;
    public $page_tags;
    public $page_content;
    public $page_time;
    public $page_author;
    public $page_views;

    public function initialize() {
        $this->belongsTo('page_author', 'users', 'id');
        $this->belongsTo('page_section_id', 'sections', 'section_id');
    }

}

and

class Sections extends \Phalcon\Mvc\Model{

    public $section_id;
    public $section_title;
    public $section_url;
    public $section_description;
    public $section_tags;

    public function initialize(){
        $this->hasMany('section_id', 'pages', 'page_section_id');
    }
}

Then in my controller I have

$pages = $this->modelsManager->createBuilder()
        ->from('Baseapp\Backend\Models\Pages')
        ->leftJoin('Baseapp\Backend\Models\Sections', 'section.section_id = Baseapp\Backend\Models\Pages.page_section_id', 'section')
        ->orderBy('page_time DESC')
        ->getQuery()
        ->execute()
        ->toArray();
    print_r($pages);exit; // <- let's see what we get here

I'm using $this->modelsManager->createBuilder() as I don't seem to be able to do joins with out it!

In the view I would use

{% for page in pages %}
    <div class="item">
        <div class="content">
            <div class="header">
                {{ page.page_title }} - {{ page.section_title }}
            </div>
        </div>
    </div>
{% endfor %}

The big issue here though, is that it is only returning page data and not the related section data. In other words the join just doesn't seem to be working.

So how do I do simple joins, or is there some better method that I'm overlooking that makes joins redundant?

1
you can use PHQL also you can take advantage of relationships without using joins by simply {{ page.sections.section_title }} - Talal

1 Answers

1
votes

In model you need to create a method, for example

/**
 * Returns users which doesn't belong to any organization.
 *
 * @return mixed
 */
public static function getSomething()
{
    $query = User::query()
        ->columns(__NAMESPACE__ . '\Pages.*')
        ->leftJoin(__NAMESPACE__ . '\Sections', __NAMESPACE__ . '\Pages.page_section_id = ps.section_id', 'ps')
        ->where('do something')
        ->execute();

    if ($query->count()) {
        return $query;
    }

    return false;
}