We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How do i view the list from 2 tables under parent

I'm hard for my idea to solve

 // Create table
CREATE TABLE `Table1` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(70) NOT NULL
    PRIMARY KEY (`id`)
); 

CREATE TABLE `Table2` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` int(10) NOT NULL,
    `table1_id` int(10) NOT NULL,
    PRIMARY KEY (`id`)
); 

//Model Table1
class Table1 extends \Phalcon\Mvc\Model
{
    public $id;    
    public $name;
}
//Model Table2
class Table1 extends \Phalcon\Mvc\Model
{
    public $id;    
    public $name;
    public $table1_id;
}

Controllers.... ?

Volt ?

How to get results in images https://colien.vn/anh.jpg

Thank you very much

edited Apr '17

The easiest way is to simply JOIN the tables with PHQL:

SELECT t1.id AS id1, t1.name AS name1, t2.id AS id2, t2.name AS name2
FROM Table2 AS t2
LEFT JOIN Table1 AS t1 ON t1.id=t2.table1_id

The other solution is to set up proper ORM:

//Model Table1
class Table1 extends \Phalcon\Mvc\Model
{
    public $id;    
    public $name;
    public function initialize() {
        $this->hasMany('id', 'Table2', 'table1_id', ['alias'=>'Table2Items']);
    }
}
//Model Table2
class Table2 extends \Phalcon\Mvc\Model
{
    public $id;    
    public $name;
    public $table1_id;
    public function initialize() {
        $this->belongsTo('table1_id', 'Table1', 'id', ['alias'=>'Table1Parent']);
    }
}

And to use them:

$parent = Table1::findFirst();
$items = $parent->Table2Items; // This is how you access related models, by alias name set up in the options parameter
foreach($item as $item) {
    // $item is an instance of Table2
    $parent = $item->Table1Parent;
    // $parent is an instance of Table1
    echo 'Parent name: ', $parent->name, PHP_EOL;
    echo 'Item name: ', $item->name, PHP_EOL;
}

For more info, check the docs: https://docs.phalcon.io/en/latest/reference/model-relationships.html



3.0k

Thanks Lajos Bencz for the suggestion.

I am quite satisfied with the results of this solution

         $parent = Table1::find();
          foreach($parent as $item1)
          {
                echo '<br><br>Parent name: ', $item1->name, PHP_EOL.'<br>';                    
                $items = $item1->Table2Items; // This is how you access related models, by alias name set up in the options parameter
                foreach($items as $item2)
                {
                    // $item is an instance of Table2
                    $parent = $item2->Table1Parent;
                    // $parent is an instance of Table1                        
                    echo 'Item name: ', $item2->name, PHP_EOL.'<br>';
                }
          }

However, due to the large number of table1 records, I want to merge the smaller table into many pages, in controller -> view .volt.

indexAction - Controller:

             $parent = Table1::find();              
             $paginator = new Paginator([
              "data" => $parent,
              "limit" => 20,
              "page" => $currentPage]);

              $this->view->page = $paginator->getPaginate(); 

view.volt

            {% for data in page.items %}        

            <div class="Table1">
                {{ data.name }} //Name Table1

                        {% for data2 in data.Table1Parent %}        
                        <div class="Table2">
                            {{ data2.name }} //Name Table2
                        </div>
                        {% endfor %} 

             </div>

            {% endfor %} 

             ..link number page

How does it working?



3.0k

Most amazing because it was working well

           {% for data in page.items %}        

            <div class="Table1">
                {{ data.name }} //Name Table1

                        {% for data2 in data.Table2Items %}    
                          <div class="Table2">
                             ---  {{ data2.name }} //Name Table2
                          </div>
                        {% endfor %} 

             </div>
             <br>
            {% endfor %} 

         ..link number page