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

Aliased Many-To-Many relation doesn't create correct JOIN query

I'm facing the same issue that HitoriSensei opened on GitHub. There's no answer still now.

You can use the models below to reproduce that issue:

<?php

class Post extends \Phalcon\Mvc\Model
{

    /**
     * @Primary
     * @Identity
     * @Column(type="integer", nullable=false)
     */
    public $id;

    /**
     * @Column(type="string", length=100, nullable=false)
     */
    public $title;

    protected function initialize()
    {
        $this->hasManyToMany(
            'id',
            'PostCategory',
            'post_id', 'category_id',
            'Category',
            'id',
            ['alias' => 'Categories']
        );
    }

}
<?php

class Category extends \Phalcon\Mvc\Model
{

    /**
     * @Primary
     * @Identity
     * @Column(type="integer", nullable=false)
     */
    public $id;

    /**
     * @Column(type="string", length=100, nullable=false)
     */
    public $name;

}
<?php

class PostCategory extends \Phalcon\Mvc\Model
{

    /**
     * @Column(type="integer", nullable=false)
     */
    public $post_id;

    /**
     * @Column(type="integer", nullable=false)
     */
    public $category_id;

}

Now, if you run the following PHQL...

SELECT * FROM Post JOIN Category

... raw SQL won't contain inner relationships:

SELECT ... FROM `post` INNER JOIN `category`

However, when you remove Post categories alias...

$this->hasManyToMany(
    'id',
    'PostCategory',
    'post_id', 'category_id',
    'Category',
    'id'/*,
    ['alias' => 'Categories']*/
);

... it works:

SELECT ... FROM `post` INNER JOIN `post_category` ON `post`.`id` = `post_category`.`post_id`  INNER JOIN `category` ON `post_category`.`category_id` = `category`.`id`

BTW: it works properly with 1:N relationships, aliased or not.



51.2k
class Post
{
    public function initialize()
    {
        $this->hasManyToMany(
            "id",
            "PostCategoryPost",
            "post_id",
            "category_id",
            "Category",
            "id",
            array(
                'alias' => 'categories'
            )
        );  
    }
}

class PostCategoryPost
{
    public function initialize()
    {
        $this->belongsTo('category_id', 'Category', 'id',
            array('alias' => 'category')
        );

        $this->belongsTo('post_id', 'Post', 'id',
            array('alias' => 'post')
        );
    }
}

class Category
{
    public function initialize()
    {
        $this->hasManyToMany(
            "id",
            "PostCategoryPost",
            "category_id",
            "post_id",
            "Post",
            "id",
            array('alias' => 'posts')
        );
    }
}