Why join result is grouped by model/table names?

You might want to post your actual QueryBuilder to get help ;]

But you should probably do something like this:

$modelsManager->createBuilder()
    ->columns('products.*, brands.name brand')
    ->addFrom('Phalcon\Models\Products','products')
    ->leftJoin('Phalcon\Models\Brands','brands.id=products.brand_id','brands')

I didn't post the exact builder because i thought it was a little complicated but here it is

//this is inside a model

$this->_name = 'Phalcon\Models\Products';
$select = array(
    'columns' => array('Phalcon\Models\Products.*', 'Phalcon\Models\Brands.name as brand'),
    'joins' => array(
        array('Phalcon\Models\Brands', .'Phalcon\Models\Brands.id = '.$this->_name.'.brand_id', '', 'LEFT'),
    )
);

$items = $this->manager
                ->createBuilder($select)
                ->from($this->_name)
                ->getQuery()
                ->execute()
                ->setHydrateMode(Phalcon\Mvc\Model\Resultset::HYDRATE_ARRAYS);

Just my 5 cents:

Since you're using namespaced models, it could be beneficial to set up a namespace alias in the manager:

$di->setShared('modelsManager',function() {
    $manager = new ModelManager();
    $manager->registerNamespaceAlias('m','Phalcon\Models');
    return $manager;
});

(You probably shouldn't store custom models inside the Phalcon namespace, but that's up to you)

Then you can use it like this:

$this->_name = 'Products';
$select = array(
    'columns' => array('m:Products.*', 'brand'=>'m:Brands.name'),
    'joins' => array(
        array('m:Brands', 'm:Brands.id = m:'.$this->_name.'.brand_id', '', 'LEFT'),
    )
);
$this->manager
    ->createBuilder($select)
    ->from('m:'.$this->_name)
    ->getQuery()
    ->execute()
    ->setHydrateMode(Phalcon\Mvc\Model\Resultset::HYDRATE_ARRAYS);

Also, you probably shouldn't mix parameter and method building, but I'm not sure about that.

Try it like this:

$this->_name = 'Products';
$modelsManager->createBuilder()
    ->columns('products.*, brands.name brand')
    ->addFrom('m:'.$this->_name,'products')
    ->leftJoin('m:Brands','brands.id=products.brand_id','brands')
    ->getQuery()
    ->execute()
    ->setHydrateMode(Phalcon\Mvc\Model\Resultset::HYDRATE_ARRAYS);

Hey Lajos, Thanks for replying. I tried what you said in your first reply and the result is the same, grouped by table name

$items = $this->manager->createBuilder()
                        ->columns('products.*, brands.name brand')
                        ->addFrom('Phalcon\Models\Products','products')
                        ->leftJoin('Phalcon\Models\Brands','brands.id = products.brand_id','brands')
                        ->getQuery()
                        ->execute()
                        ->setHydrateMode(\Phalcon\Mvc\Model\Resultset::HYDRATE_ARRAYS)
                        ->toArray();

The result:

array(1) {
  [0]=>
  array(2) {
    ["products"]=>
    array(10) {
      ["id"]=>
      string(1) "1"
      ["brand_id"]=>
      string(1) "5"
      ["name"]=>
      string(22) "Product Name"
      ["description"]=>
      NULL
      ["created_at"]=>
      NULL
    }
    ["brand"]=>
    string(10) "Brand name"
  }
 }

It's like I suspected, a Phalcon thing, not having anything to do with aliasing or other things that I did

I haven't really used hydration so far, how about leaving it out and converting the result with ->toArray()?

So nobody knows?

Why HYDRATE_ARRAY, then toArray()? Leave the hydration out, toArray() will then return a single level array with your aliases.

without hydrate the result is Phalcon\Mvc\Model\Resultset\Complex which contains an array of Phalcon\Mvc\Model\Row so hidrate turns Phalcon\Mvc\Model\Resultset\Complex into array and toArray() turns Phalcon\Mvc\Model\Row into array

again, the join grouping happens in every case there is a join, no matter how you choose to do it (query builder, phql, or other way). But nevermind I'm over this, I guess I have to do a foreach to loop the items into the array form i want. I do however have another issue with join. I have the 2 tables joined so i want to select where id = 1. Of course this will give an error saying "id is ambiguous". So then I select where table1.id = 1 (I made an alias for my tables) but this gives me this error "Scanner: Unknown opcode 58". Why? That error is in reference to " AND Admin\Models\Orders.id = :Admin\Models\Orders.id:" . What am I doing wrong? Binding? Condtioning?

Thanks Lajos for being the only active person on this forum

Thank you for the clarification about aliasing