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. Beginners

[SOLVED] Fetching Data from a many to many relationship

hi, i´m new to Phalcon and i´m not sure how to get data from a manytomany relationship. I have three tables: Clients, Tags and ClientsTags. mysql tables:

DROP TABLE IF EXISTS `clients`;
CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50),
  `preview_pic` VARCHAR(200),
  `short_description` VARCHAR(250),
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  DROP TABLE IF EXISTS `tags`;
CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) UNIQUE ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE If EXISTS  `clients_tags`;
CREATE TABLE IF NOT EXISTS `clients_tags` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `client_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`) ,
  FOREIGN KEY (`client_id`) REFERENCES clients (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
 FOREIGN KEY (`tag_id`) REFERENCES tags (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The Models are:

Clients:

public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'ClientsTags',
            'client_id', 'tag_id',
            'Tags',
            'id',
            array(
                'alias'=>'tags',
                'foreignKey' => array(
                    'action' => Relation::ACTION_CASCADE
                )
            )
        );
    }

Tags:

 public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'ClientsTags',
            'tag_id', 'client_id',
            'Clients',
            'id',
            array(
                'alias'=>'clients',
                'foreignKey' => array(
                    'action' => Relation::ACTION_CASCADE
                )
            )
        );
    }

ClientsTags:

public function initialize()
    {
        $this->belongsTo(
            'client_id',
            'App\Models\Clients',
            'id',
            array(
                'alias'=>'client',
                'foreignKey' => [
                    'message'   => 'client id does not exist or is currenty invalid'
                ]
            )
        );

        $this->belongsTo(
            'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tag',
                'foreignKey' => [
                    'message'   => 'tag id does not exist or is currenty invalid'
                ]
            )
        );
    }

Now i want all clients with a specifig tag. I´m not sure how to do. I would do something like that:

public function getClientsWithTag($tag)
    {
        try{
            $tags = Tags::find(
                [
                    'name = :tag:',
                    'bind' => ['tag' => $tag]
                ]);
            // what have i to do next??

        } catch(\PDOException $e) {

        }
    }

Thanks for your help



77.7k
edited Apr '18

Here's a similar question: https://forum.phalcon.io/discussion/545/many-to-many-relation

Hope it helps ;]

(TLDR:)

$client = Clients::findFIrst();
foreach($client->ClientsTags as $clientTag) {
    // todo
}


43.9k

Hi,

did you ever try your function getClientsWithTag($tag) ? If you have a valid resultset you can just cycle through it with a foreach($tags as $tag) ....



5.9k
edited Apr '18

this is my new getClientswithTag function. 

public function getClientsWithTag(array $clientsData)
    {
        try{

            $foundClients = "" ;

            $tags = Tags::find(
                [
                    "name = :tag:",
                    "bind" => ["tag" => $clientsData['tag']],
                ]);

            foreach($tags as $tag){
                $clients = $tag->clients;
                foreach ($clients as $client){
                    $foundClients = $client->name;
                    echo $foundClients;
                }
            }

            if (!$foundClients) {
                return [];
            }
            return $foundClients->toArray();

        } catch(\PDOException $e) {
            throw new ServiceException($e->getMessage(), $e->getCode(), $e);
        }
    }

If i return only tags its works very well, but if i try to find the clients with specific tag it doesn´t work. is the general coding of the foreach loops to get teh client name correct? Is the realtionship between my models (tables) correct? (please look first ppost?)



43.9k
edited Apr '18

Hi,

If i return only tags its works very well

Does that mean that with $tags = Tags::find( ... ) you've got a Tag Model resultset (with many tags in it) ? It looks strange to me, because I think imho that each tag should be unique and that you should fetch it with:


     $tag = Tags::findFirst(
                [
                    "name = :tag:",
                    "bind" => ["tag" => $clientsData['tag']],
                ]);

    // and then no more foreach($tags as $tag)
                $clients = $tag->clients; 
                foreach ($clients as $client){
                    $foundClients = $client->name;
                    echo $foundClients;
                }


5.9k
edited Apr '18

yes you´re right. findFirst() is the better one. And i find my mistake. At the model classes i wrote:

public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'ClientsTags',
            'client_id', 'tag_id',
            'Tags',
            'id',
            array(
                'alias'=>'tags',
                'foreignKey' => array(
                    'action' => Relation::ACTION_CASCADE
                )
            )
        );
    }

but it must be:

public function initialize()
    {
        $this->hasManytoMany(
            'id',
            'App\Models\ClientsTags',
            'client_id', 'tag_id',
            'App\Models\Tags',
            'id',
            array(
                'alias'=>'tags',
                'foreignKey' => array(
                    'action' => Relation::ACTION_CASCADE
                )
            )
        );
    }

thanks everyone for help.



43.9k

'App\Models\ClientsTags'

hahaha, a namespace problem: it's a classic one ;-)



5.9k

yep that was my thought too :)