Issue: Is it possible to select table data when doing a multiple-join without mentioning every selected table?
Author: perplexedfingers
State: closed
Created: 2015-12-08 06:22:16

The following query does _**not**_ work.

``` python
(PermissionInGroup
      .select(PermissionInGroup, Permission, Group)
      .join(Group)
      .where(Group.app_account == app_account_id,
                Group.company_id == company_id)
      .join(UserInGroup)
      .join(User)
      .where(User.ID == user_id)
       )
```

SQL statement would be like

``` sql
SELECT "t1"."id", "t1"."permission_id", "t1"."group_id", "t5"."name", "t4"."primary_key", "t4"."name", "t4"."company_id", "t4"."app_account_id"
FROM "permissioningroup" AS t1
INNER JOIN "group" AS t4 ON ("t1"."group_id" = "t4"."primary_key")
INNER JOIN "useringroup" AS t2 ON ("t4"."primary_key" = "t2"."group_id")
INNER JOIN "user" AS t3 ON ("t2"."user_id" = "t3"."ID")
WHERE ((("t4"."app_account_id" = ?) AND ("t4"."company_id" = ?)) AND ("t3"."ID" = ?))
[722, 25, 32]
```

I will have this exception while trying to iterate over it

``` python
peewee.OperationalError: no such column: t5.name
```

But this one works.

``` python
(PermissionInGroup
           .select(PermissionInGroup, Permission, Group)
           .join(Group)
           .where(Group.app_account == app_account_id,
                      Group.company_id == company_id)
           .join(UserInGroup)
           .join(User)
           .where(User.ID == user_id)
           ####### mark the difference
           .switch(PermissionInGroup)
           .join(Permission)
           .order_by(Permission.name)
           #######
           )
```

SQL statement

``` sql
SELECT "t1"."id", "t1"."permission_id", "t1"."group_id", "t5"."name", "t4"."primary_key", "t4"."name", "t4"."company_id", "t4"."app_account_id"
FROM "permissioningroup" AS t1
INNER JOIN "group" AS t4 ON ("t1"."group_id" = "t4"."primary_key")
####### mark the difference
INNER JOIN "permission" AS t5 ON ("t1"."permission_id" = "t5"."name")
#######
INNER JOIN "useringroup" AS t2 ON ("t4"."primary_key" = "t2"."group_id")
INNER JOIN "user" AS t3 ON ("t2"."user_id" = "t3"."ID")
WHERE ((("t4"."app_account_id" = ?) AND ("t4"."company_id" = ?)) AND ("t3"."ID" = ?))
####### mark the difference
ORDER BY "t5"."name"
#######
[722, 25, 32]
```

Can I have a better way to do this query?
I don't need to do `order_by`.


You have to join on permission since you're selecting from it. That will never change.

If you don't need the order by, why are you calling it? I'm confused why that's an issue.


It requires an action after `join()`.


I figure it out. Sorry about wasting your time.

``` python
(PermissionInGroup.select(Permission.name,
                          Group.name,
                          PermissionInGroup)
                                .join(Permission)
                                .switch(PermissionInGroup)
                                .join(Group)
                                .join(UserInGroup)
                                .join(User)
                                .where(User.ID == user_id,
                                       Group.app_account_id == app_account_id,
                                       Group.company_id == company_id)
)
```