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

Filtering many-to-many relation based on column in linking table

I am currently working with a database that stores regions in a hierarchy based on the depth between the parent and the child.

Database

CREATE TABLE regions (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `chain` TINYTEXT NOT NULL,
);

CREATE TABLE regions_tree (
    `parent_id` INT NOT NULL,
    `child_id` INT NOT NULL,
    `depth` INT NOT NULL DEFAULT 0
);

Table: regions

id  name                chain
1   All                 All
2   Asia Pacific        All-Asia Pacific
3   Australia           All-Asia Pacific-Australia
4   Queensland          All-Asia Pacific-Australia-Queensland
5   New South Wales     All-Asia Pacific-Australia-New South Wales

Table: regions_tree

parent_id   child_id    depth
1           1           0
2           2           0
2           1           1
3           3           0
3           2           1
3           1           2
4           4           0
4           3           1
4           2           2
4           1           3
5           5           0
5           3           1
5           2           2
5           1           3

I am able to return a resultset of an individual region's parents by defining a hasManyToMany() relationship in the Regions model:

Models

class RegionsTree extends \Phalcon\Mvc\Model
{
    public function getSource()
    {
        return 'regions_tree';
    }
}

class Regions extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManytoMany('id', 'RegionsTree', 'child_id', 'parent_id', 'Regions', 'id', array('alias' => 'parents'));
    }
}

Controller

$this->view->regions = Regions::find();

View

{% for region in regions %}
    <p>{{ region.name }} - {{ region.parents.getFirst().name }}</p>
{% endfor %}

Output

All - All
Asia Pacific - Asia Pacific
Australia - Australia
Queensland - Queensland
New South Wales - New South Wales

I would like to return just one parent based on the depth column in the linking table (regions_tree.depth). Normally, I would use the following SQL to find this parent:

SELECT a.`id`
FROM `regions` AS a
LEFT JOIN `regions_tree` ON `regions_tree`.`parent_id` = a.`id`
LEFT JOIN `regions` AS b ON b.`id` = `regions_tree`.`child_id`
WHERE b.`id` = $child_id AND `regions_tree`.`depth` = 1;

Is there a way of filtering a hasManyToMany() relationship (preferrably inside initialize())? If not, what would be the best way to achieve the following result:

{% for region in regions %}
    <p>{{ region.name }} - {{ region.parent.name }}</p>
{% endfor %}
All - 
Asia Pacific - All
Australia - Asia Pacific
Queensland - Australia
New South Wales - Australia

Thanks in advance. I appreciate it!



58.4k
edited Apr '15

Hey man

The first to use hasManyToMany you need to 3 models, Here I'm see you have to 2 models so you can't use it. To the solution you try method belongTo or hasMany

$this->belongsTo('parent_id', 'Regions',  'id', array('alias' => 'parent'));

The next in conroller you set variable to view

$this->view->regions =  Regions::find()

Then in volt

{% for region in regions %}
    <p>{{ region.name }} - {{ region.parent.name }}</p>
{% endfor %}


1.6k

Thanks for your reply.

The reason there are only two models is because both the parent and child are regions, so the linking table (regions_tree) really just holds ids from the regions table. As mentioned in my original post, I have no trouble returning a resultset of all the parents of an individual region using a hasManyToMany() relationship. What I need is to select or filter out a specific record from within that resultset based on a column in the linking table (or RegionsTree model).

Your suggestion may work (although I believe the belongsTo() relationship should be defined in the RegionsTree model), but it does not take into consideration the depth column, which is required to filter the parents.

If I were to use the tables from the Phalcon documentation, as an example, it would be like trying to select all Parts of a Robot that have a specific created_at date in the robots_parts table and store the results as a property that's accessible from within a for loop in the view. Is this possible?

CREATE TABLE `robots` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(70) NOT NULL,
    `type` varchar(32) NOT NULL,
    `year` int(11) NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `robots_parts` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `robots_id` int(10) NOT NULL,
    `parts_id` int(10) NOT NULL,
    `created_at` DATE NOT NULL,
    PRIMARY KEY (`id`),
    KEY `robots_id` (`robots_id`),
    KEY `parts_id` (`parts_id`)
);

CREATE TABLE `parts` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(70) NOT NULL,
    PRIMARY KEY (`id`)
);


58.4k
edited Apr '15

Hi

I think it possible, you can take example here https://phalcontip.com/discussion/32/how-to-use-hasmanytomany-in-phalcon



1.6k
edited Apr '15

I think you have misunderstood my question. I know how to set up the many-to-many relationship; I have done this, and it returns the results as expected.

Moving away from the many-to-many relationship, I can almost achieve what I need using the code below, but I'm stuck with a hard-coded child_id value. As the region.parent property will be accessed inside a for loop in the view, it's impossible to know what the child_id value will be so it should be dynamically set.

class Regions extends \Phalcon\Mvc\Model
{
    public $parent;

    public function initialize()
    {
        $phql = "SELECT a.id
        FROM Regions AS a
        LEFT JOIN RegionsTree ON RegionsTree.parent_id = a.id
        LEFT JOIN Regions AS b ON b.id = RegionsTree.child_id
        WHERE b.id = :child_id: AND RegionsTree.depth = 1";

        $query = new \Phalcon\Mvc\Model\Query($phql, $this->getDI());
        $result = $query->execute(array('child_id' => 3)); // This value should be dynamic (eg. $this->id)

        $parent_id = $result->getFirst()->id;
        $this->parent = Regions::find($parent_id);
    }
}


34.6k