Join accross multiple tables

[brnosouza]

Is there any way of joining multiple tables today? I was trying to connect to a table at 3 relations ahead and I couldn't found a easy way to do that or any way, actually

[dantownsend]

Yes, it's possible.

If you try piccolo playground run it uses this schema:

class Manager(Table):
    id = PrimaryKey()
    name = Varchar()



class Band(Table):
    id = PrimaryKey()
    name = Varchar()
    manager = ForeignKey(references=Manager)
    popularity = Integer()



class Venue(Table):
    id = PrimaryKey()
    name = Varchar()
    capacity = Integer()



class Concert(Table):
    id = PrimaryKey()
    band_1 = ForeignKey(references=Band)
    band_2 = ForeignKey(references=Band)
    venue = ForeignKey(references=Venue)
    starts = Timestamp()
    duration = Interval()

So Concert has a ForeignKey key to Band, which as a ForeignKey to Manager.

You can then do this:

Concert.select(Concert.id, Concert.band_1.manager.name).run_sync()
>>> [{'id': 1, 'band_1.manager.name': 'Guido'}]

It only works with select and not objects. Does that help?

[brnosouza]

yeah, kinda, I was trying to do this in a where clause, but my problem is that the joinable collumn is on the second table because my relation is a many-to-many.

[dantownsend]

You can use a similar syntax in where clauses.

Concert.select().where(Concert.band_1.manager.name == 'Guido').run_sync()

I think I understand your issue though. Can you share your schema, or something similar, and I'll try and figure it out.

[brnosouza]

of course... here it is:

class Permission(BaseModel, tablename="permissions", tags=['active']):
    name = columns.Varchar(length=255, null=False)
    code = columns.Varchar(length=255, null=False)

    def __str__(self):
        return self.code


class Group(OrganizationMixin, BaseModel, tablename="groups", tags=['active']):
    name = columns.Varchar(length=255, null=False)
    global_group = columns.Boolean(default=False)

    def __str__(self):
        return self.name


class GroupPermission(IdentityMixin, TimestampMixin, Table, tablename="group_permission", tags=['active']):
    group_identity = columns.UUID(unique=False)
    group = columns.ForeignKey(
        references=Group,
        on_delete=columns.OnDelete.cascade
    )

    permission_identity = columns.UUID(unique=False)
    permission = columns.ForeignKey(
        references=Permission,
        on_delete=columns.OnDelete.cascade
    )


class GroupUser(UserMixin, IdentityMixin, TimestampMixin, Table, tablename="group_user", tags=['active']):
    group_identity = columns.UUID(unique=False)
    group = columns.ForeignKey(
        references=Group,
        on_delete=columns.OnDelete.cascade
    )

    user_identity = columns.UUID(unique=False)

I'm currently using raw SQL, but yeah, I didn't want to :sweat_smile:

And I was holping to accomplish this:

query = await Permission.select(
            Permission.name,
            Permission.code,
            Permission.identity,
        ).where(
            Permission.group_permissions.group.group_user.user_identity == pk
        )

[dantownsend]

The many to many support in Piccolo is a bit limited at the moment, for queries like this.

I think the best way to achieve this is with two queries. Something like this:

permission_ids = await GroupPermission.select(
    GroupPermission.permission.id
).distinct().where(
    GroupPermission.group.group_user.user_identity == pk
).output(as_list=True)


permissions = await Permission.select(
    Permission.name,
    Permission.code,
    Permission.identity,
).where(
    Permission.id.is_in(permission_ids)
)

[brnosouza]

I see, cool, I'll take a look on that. The many-to-many is on the roadmap, right? And is there anything I can help with? Because I loved the project and I feel I can do more than opening issues :sweat_smile:

[dantownsend]

There was a discussion about adding Many-To-Many fields:

https://github.com/piccolo-orm/piccolo/discussions/44

The conclusion was that yeah, they should be added. It's a tricky problem though, which is why the progress has been slow. If you'd like to give it a go, that would be great.