Issue: there is two foreign keys in table A related to table B，how can I get table B's columns within one query?
Author: linxi-1214
State: closed
Created: 2017-06-15 08:26:57

TABLE contract: 

column | type | constraint
------- | -----|  ------
id | int | primary key
contract_no | int | unique
customer| int | Foreign key to Table User
salesman | int | Foreign key to Table User

Table User:

column | type | constraint
------- | ---- | ------
id | int | Primary key
name| varchar | 

I want to get contract_no, customer's name , salesman's name in one query?

I have saw the section "avoid N+1 querying", so my expression is \:

```python
Customer = User.alias()
Salesman = User.alias()

contracts = (Contract
           .select(Contract, Customer.name, Salesman.name)
           .join(Customer, on=(Contract.customer == Customer.id))
           .join(Salesman, JOIN.LEFT_OUTER, on=(Contract.salesman == Salesman.id))
           .where(Contract.customer == 1)
           )
``` 

I wonder whether it's right or not?

Yeah actually that looks good! You can verify by turning query logging on:

```python
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```

I got the query log as the way you told me, and the log are as below
__Note: This happened when I fetch both contract.customer.name and contract.salesman.name .  And just the first query executed if I only fetch the contract.customer.name .__

```sql
--------------------------------------------------------------------------------
('SELECT `t1`.`id`, `t1`.`contract_no`, `t1`.`customer_id`, `t1`.`salesman_id`, `t1`.`total_price`, `t1`.`is_internal`, `t1`.`company`, `t1`.`project`, `t1`.`sign_time`, `t1`.`effective_time`, `t1`.`expired_time`, `t1`.`notes`, `t1`.`timestamp`, `t2`.`username`, `t2`.`mphone`, `t3`.`username` FROM `t_contract` AS t1 INNER JOIN `t_user` AS t2 ON (`t1`.`customer_id` = `t2`.`id`) LEFT OUTER JOIN `t_user` AS t3 ON (`t1`.`salesman_id` = `t3`.`id`) WHERE (`t1`.`customer_id` = %s)', [u'1'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'346'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
('SELECT `t1`.`id`, `t1`.`username`, `t1`.`role_id`, `t1`.`email`, `t1`.`mphone`, `t1`.`is_disable`, `t1`.`is_admin`, `t1`.`is_enterprise`, `t1`.`credits`, `t1`.`created_time`, `t1`.`updated_time` FROM `t_user` AS t1 WHERE (`t1`.`id` = %s) LIMIT 1', [u'345'])
```

I got N+1 queries

Finally I resolved this problem by Calling the SelectQuery instance's method `dicts()`.

``` python
        Customer = User.alias()
        Salesman = User.alias()

        contracts = (Contract
                     .select(Contract.id,
                             Contract.contract_no,
                             Contract.total_price,
                             Contract.company,
                             Contract.project,
                             Contract.sign_time,
                             Contract.is_internal,
                             Customer.mphone,
                             Customer.name.alias('customer_name'),
                             Salesman.name.alias('salesman_name')
                             )
                     .join(Customer, on=(Contract.customer == Customer.id))
                     .join(Salesman, JOIN.LEFT_OUTER, on=(Contract.salesman == Salesman.id))
                     .where(Contract.customer == user_id)
                     )
       for contract in contracts.dicts():
             contract.get('salesman_name')
             contract.get('customer_name')
```

Is there a cool way ?

Ahh shit I missed something in your initial query. In order to access related models, you will need to assign an alias to the join predicate. Additionally, after joining on Customer you need to switch the join context back to Contract so that when you join on Salesman peewee knows how to reconstruct the model graph. This code works as expected:

```python
contracts = (Contract
             .select(Contract, Customer.name, Salesman.name)
             .join(Customer,
                   on=(Contract.customer == Customer.id).alias('customer'))
             .switch(Contract)
             .join(Salesman,
                   on=(Contract.salesman == Salesman.id).alias('salesman'))
             .order_by(Contract.contract_no))
for contract in contracts:
    print contract.contract_no, contract.customer.name, contract.salesman.name
```