Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Why join result is grouped by model/table names?

Hello, I have a simple left join between 2 tables products and brands. The basic mysql query would be this:

SELECT products.*, brands.name as brand FROM products
LEFT JOIN brands ON brands.id = products.brand_id

The query is built via query builder, hydration mode is set to array. My problem is that the result looks like this

array(1) {
  ["Phalcon\Models\Products"]=>
  array(5) {
    ["id"]=>
    string(1) "2"
    ["brand_id"]=>
    string(1) "3"
    ["name"]=>
    string(17) "Product's name"
    ["description"]=>
    NULL
    ["created_at"]=>
    NULL
  }
  ["brand"]=>
  string(10) "Brand name"
}

Why is join grouping the results by table names? This only happens with join. My desired result would be this:

array(6) {
    ["id"]=>
    string(1) "2"
    ["brand_id"]=>
    string(1) "3"
    ["name"]=>
    string(17) "Product's name"
    ["description"]=>
    NULL
    ["created_at"]=>
    NULL
     ["brand"]=>
    string(10) "Brand name"
}

Thank you!

PS: Edit: fixed the formatting

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')
edited Sep '15

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



40.5k
Accepted
answer

The grouping doesn't happen because of joins... it happens because you use multiple models in the columns then hydrate. And if you want your final results as arrays not models, why hydrate in the first place? I don't see your logic there.

:Admin\Models\Orders.id: is most definetaly invalid, colons delimit placeholders and you use a model name there.

$items = $this->manager->createBuilder()
                        ->columns('products.*, brands.name brand')
                        ->addFrom('Phalcon\Models\Products','products')
                        ->leftJoin('Phalcon\Models\Brands','brands.id = products.brand_id','brands')
                        ->where('brands.id=:brandId:',['brandId'=>1]) // use it like this
                        ->getQuery()
                        ->execute()
                        ->toArray();

Thank you for the clarification about aliasing