How to get model data in resultset?

Question

How to get model data in resultset?

barell Sep '13

Created Sep '13	Last Reply Nov '17	Replies 2	Views 2918	Votes 1

barell 2.9k

Sep '13

I have "Pages" and "Widgets" which are in many-to-many relation. This is the Pages model definition:

namespace Acme\Models;
class Pages extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            'id',
            'Acme\Models\PagesWidgets',
            'pages_id',
            'widgets_id',
            'Acme\Models\Widgets',
            'id',
            array('alias' => 'widgets')
        );
    }
}

I'm trying to access to the Widgets in resultset:

$result = $modelsManager->executeQuery('SELECT Acme\Models\Pages.*, Acme\Models\Widgets.* FROM Acme\Models\Pages JOIN Acme\Models\Widgets');

foreach($result as $row) {
    echo $row->widgets->name; 
}

But with no luck, because I've just got an error:

Notice: Undefined property: Phalcon\Mvc\Model\Row::$widgets

So, is there any way to do this?

1

barell · Answer 1 · 2013-09-17T08:53:28-07:00

Ok, I've done it with query builder:

$result = $modelsManager->createBuilder()
            ->columns(array('page.*', 'relation.*', 'widget.*'))
            ->from(array('page' => 'Acme\Models\Pages'))
            ->leftJoin('Acme\Models\PagesWidgets', 'relation.pages_id=page.id', 'relation')
            ->leftJoin('Acme\Models\Widgets', 'relation.widgets_id=widget.id', 'widget')
            ->getQuery()->execute();

foreach($result as $row) {
    echo $row->widget->name;
}

And now I can access the data I need... But in this situation I don't see any sense to keep my model 'manyToMany' definition, because I still need to join my models manually. Have I right?

Phalcon · Answer 2 · 2013-09-17T09:12:34-07:00

If you don't pass the conditions to the join, they will be automatically created by the builder if an existing relationship is present between the model in the' from' clause and the model in the 'join' clause.

barell · Answer 3 · 2013-09-17T10:10:40-07:00

I have three tables: "pages" (id, title), "widgets" (id, name) "pages_widgets" (id, pages_id, widgets_id)

and there are my models definitions:

namespace Acme\Models;
class Pages extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            'id',
            'Acme\Models\PagesWidgets',
            'pages_id',
            'widgets_id',
            'Acme\Models\Widgets',
            'id',
            array('alias' => 'widgets')
        );
  }
}

namespace Acme\Models;
class Widgets extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            'id',
            'Acme\Models\PagesWidgets',
            'widgets_id',
            'pages_id',
            'Acme\Models\Pages',
            'id',
            array('alias' => 'pages')
        );
  }
}

namespace Acme\Models;
class PagesWidgets extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->belongsTo(
            'pages_id', 
            'Acme\Models\Pages',
            'id',
            array('alias' => 'page')
        );

        $this->belongsTo(
            'widgets_id', 
            'Acme\Models\Widgets',
            'id',
            array('alias' => 'widget')
        );
  }
}

But when I don't pass the conditions to the join, my query looks like this:

"SELECT ... FROM pages AS page LEFT JOIN pages_widgets AS relation LEFT JOIN widgets AS widget "

So there is no automatically created conditions...

sairio · Answer 4 · 2017-11-09T15:36:50-07:00

you can try this: $result = $modelsManager->createBuilder() ->columns(array('Acme\Models\page.', 'Acme\Models\relation.', 'Acme\Models\widget.*')) ->from(array('page' => 'Acme\Models\Pages')) ->leftJoin('Acme\Models\PagesWidgets', 'relation.pages_id=page.id', 'relation') ->leftJoin('Acme\Models\Widgets', 'relation.widgets_id=widget.id', 'widget') ->getQuery()->execute();

foreach($result as $row) { echo $row->{Acme\Models\widget}->name; }