How to group by in a ManyToMany relationship

[guilhermeaiolfi]

Hi,

I've been away from using CycleORM for a while, so I am a little bit slow at its concepts and how to use it properly. So the problem I am facing is the following: imagine the user -> tag scenario that we have in the documentation, but with another relationship, something like company_id. So that many-to-many table would have: id, tag_id, user_id, company_id. If I use this schema for the Company relations to get all tags used in a company:

'company' => [
    (...)
    Schema::RELATIONS   => [
        'tags' => [
                        Relation::TYPE   => Relation::MANY_TO_MANY,
                        Relation::TARGET => 'tag',
                        Relation::SCHEMA => [
                            Relation::CASCADE           => true,
                            Relation::THROUGH_ENTITY    => 'user_tag_company',
                            Relation::INNER_KEY         => 'id',
                            Relation::OUTER_KEY         => 'id',
                            Relation::THROUGH_INNER_KEY => 'company_id',
                            Relation::THROUGH_OUTER_KEY => 'tag_id',
                       ]
         ]
    ]
]

It works, but it brings duplication for every entry that is repeated through users. I would need some way to use distinct or group by in that relationship.

BTW, I am using cycle 1.x. Thank you for any help.

[roxblnfk]

I see no other way out of this situation than to normalize database tables.
If the fat cross-table makes problem then you should explode it.

But if user_id column is nullable and user_id = null means only relation tag-company then
in this case you would can determinate a "group" tag-company using where condition. But i am not sure that ORM v1 supports this