Phalcon query builder can't get joined table data

Question

I have 2 table 'sanpham' and 'danhmuc'. I use phalcon query builder to get data from 2 tables.

$laytin = $this->modelsManager->createBuilder()
      ->from("sanpham")
      ->innerJoin('danhmuc','sanpham.danhmuc=danhmuc.sodanhmuc')
      ->where('sanpham.sosanpham = '.$id.'')
      ->getQuery()
      ->getSingleResult();
      $breadcrumbs = array('/' => Tool::getTranslation()->_('trangchu'),"/Loai-san-pham/".$laytin->tendep."/".$laytin->sodanhmuc => $laytin->tendanhmuc,'' => $laytin->tieudesanpham );

The query runs, but $laytin->tendep, $laytin->sodanhmuc, $laytin->tendanhmuc in 'danhmuc' table doesn't display. Every column in 'sanpham' table (such as: $laytin->tieudesanpham) displays properly.

Try something like this: $this->modelsManager->createBuilder()->columns('danhmuc.tendep, danhmuc.sodanhmuc'). docs: docs.phalconphp.com/en/3.0.2/api/… — Timothy
Thank you. It works !!! But i have to enter all columns in 'sanpham' manually (example: columns('danhmuc.tend‌ep, danhmuc.sodanhmuc, sanpham.tieudesanpham..') ). Why Phalcon doesn't make builder more easily . — chipheo83

Timothy Timothy · Accepted Answer · 2017-01-06T14:00:53

You can add specific columns with:

$this->modelsManager->createBuilder()->columns('danhmuc.tend‌ep, danhmuc.sodanhmuc')

With this method you will have to add each column you want in your output. QueryBuilder docs. Another method is to query the Sanpham model.

For example:

class Sanpham extends \Phalcon\Mvc\Model
{
    public static function findSomething($something)
    {
        // this is your actual query, it replaces the queryBuilder
        return self::query()
            ->where('sanpham.sosanpham = :id:', ['id' => $something])
            ->innerJoin('danhmuc', 'sanpham.danhmuc = danhmuc.sodanhmuc')
            ->execute()->getFirst();
    }

    public function initialize()
    {
        // define the relation to danhmuc
        $this->belongsTo('danhmuc', 'danhmuc', 'sodanhmuc');
    }
}

class Danhmuc extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        // there are other options besides "hasMany", like "hasOne".
        // this is your relation to sanpham
        $this->hasMany('sodanhmuc', 'sanpham', 'danhmuc');
    }
}

class YourController extends \Phalcon\Mvc\Controller
{
    public function testAction()
    {
        // get your first record in Sanpham matching "12345"
        $sanpham = Sanpham::findSomething(12345);

        // from your Sanpham object, get the related danhmuc object.
        // this works because we defined the relations (belongsTo and hasMany)
        $danhmuc = $sanpham->getRelated('danhmuc');

        // now you have access to the values of danhmuc via the $danhmuc variable
        $breadcrumbs = [
            '/'                                  => Tool::getTranslation()->_('trangchu'),
            "/Loai-san-pham/" . $danhmuc->tendep => $danhmuc->tendanhmuc,
            ''                                   => $danhmuc->tieudesanpham,
        ];
    }
}

Check the Phalcon model docs for more info on this.

Phalcon query builder can't get joined table data

1 Answers