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

hasManyToMany not working as I expected

not sure If I am doing something wrong, or if there is an issue with phalcon (1.2.0 -

docblock from the devtools ide stubs

        /*
         * @param string $fields
         * @param string $intermediateModel
         * @param string $intermediateFields
         * @param string $intermediateReferencedFields
         * @param string $referencedModel
         * @param   array $options
         * @return  \Phalcon\Mvc\Model\Relation
         */

Models, Gallery and Article joined with GalleryToArticle

        $this->hasManyToMany(
            'galleryId' // $fields
            ,'GalleryToArticle' // $intermediateModel
            ,'galleryId' // $intermediateFields
            ,'articleId' // $intermediateReferencedFields
            ,'Article' // $referencedModel
            ,'articleId' // seems to be needed, and has to be a string, is array in docblock
        );

and the generated query is (fields replaced with * to make post shorter)

SELECT * FROM `Article` INNER JOIN `Gallery_to_Article` ON `Gallery_to_Article`.`articleId` = `Article`.`articleId` WHERE `Gallery_to_Article`.`articleId` = :0

when I think the WHERE should be on galleryId, indeed the value that is in the bindParams is the expected value for galleryId, however no combination of swapping the values around seems to work

when I put in placeholders:

        $this->hasManyToMany(
            'galleryId' // $fields
            ,'GalleryToArticle' // $intermediateModel
            ,'intermediateFields' // $intermediateFields
            ,'intermediateReferencedFields' // $intermediateReferencedFields
            ,'Article' // $referencedModel
            ,'unknownId' // ?
        );

I get this:

SELECT * FROM `Article` INNER JOIN `Gallery_to_Article` ON `Gallery_to_Article`.`intermediateReferencedFields` = `Article`.`unknownId` WHERE `Gallery_to_Article`.`intermediateReferencedFields` = :0

when I would expect

SELECT * FROM `Article` INNER JOIN `Gallery_to_Article` ON `Gallery_to_Article`.`intermediateReferencedFields` = `Article`.`intermediateReferencedFields` WHERE `Gallery_to_Article`.`intermediateFields` = :0

Your "unknownId" is the "id" of the [edit] target model, not the intermediate table. So it probably should be "id" (or whatever primary keys you are using).

I just put in unknownId as a placeholder for that example

Gallery primary key = galleryId Article primary key = articleId linking model - GalleryToArticle has galleryId and articleId fields

this is what I was doing

        $this->hasManyToMany(
            'galleryId' // 0 = id of the current model?
            ,'GalleryToArticle' // 1 = model just for the purpose of joining?
            ,'galleryId' //  2 = field that identifies the current (Gallery) model?
            ,'articleId' // 3 = field that identifies the target model (Article)?
            ,'Article' // 4 = the target model to pull in?
            ,'articleId' // 5 = id for the target model ?
        );

it appears param 2 is not used... and if I put the key of the current model in param 5 as you suggest it uses it with param 4 which is the other model/table which I can't see how that's right

You are completely right with what you are doing, except with the assumption of the "WHERE" condition. You have set an ID in your model, that is the placeholder ":0" The "galleryId" field is only used in insertions. What do you want to query anyway?

Edit: Just to clarify:

SELECT * FROM Article

INNER JOIN Gallery_to_Article ON Gallery_to_Article.articleId = Article.articleId This is the automagically done part of the "hasManyToMany" feature.

WHERE Gallery_to_Article.articleId = :0 This could be the same as WHERE Article.id = :0, it just selects once article.

[Edit2] Following structure:

//Table: articles [id_a, name, ...]
//Table: galleries [id_g, name, ...]
//Table: articles_galleries [id_a_g, article_id, gallery_id] (note the id_a_g is optional but good practice)

In the Gallery-Model I would write now the following:

$this->hasManyToMany(
    "id_g", // Current Model primary key
    "ArticlesGalleries", // Intermediary Model name, maps to the table 'articles_tables'
    "gallery_id", // Current Model key in the intermediary table
    "article_id", // Linking Model key in the intermediary  table
    "Article", // Linked Model name
    "id_a" // Linked Model primary key
);

Vice-versa, in the Article model:

$this->hasManyToMany(
    "id_a", // Current Model primary key
    "ArticlesGalleries", // Intermediary Model name, maps to the table 'articles_tables'
    "article_id", // Current Model key in the intermediary table
    "gallery_id", // Linking Model key in the intermediary  table
    "Gallery", // Linked Model name
    "id_g" // Linked Model primary key
);

This results in the correct joins created on creates, reads, updates and deletes. Note that this assumes that linking a to b means also b is linked to a, which is not always the case in all datastructures and problems...

The goal is to have the models linked like $gallery->articles , and in my example those queries were the automagically generated ones

Thanks for your help : ) I am having a nightmare getting my head around this. I can't see where your code is different from mine! :(

Does phalcon expect the fields to have different names? (as far as I can tell I am doing the code the way you said, but id_g is called galleryId and the equivalent field in the linking table is the same name)

I can see from my sql logger that the value bound to :0 is the value of galleryId from the current instance Gallery Model :-) however if I change param0 in the hasManyToMany method to say galleryTitle then the boundparam has the value of the title, so param 0 is being used in the where, just with the wrong field! :(