Issue: What is wrong about join method?
Author: AntmanTmp
State: closed
Created: 2020-01-06 11:09:10

I have two model and random insert some rows
```
class BaseModel(Model):
    created_time = DateTimeField(default=datetime.now)
    class Meta:
        database = db

class Supplier(BaseModel):
    name = CharField(max_length=100, index=True)
    address = CharField(max_length=100)
    phone = CharField(max_length=20)

    @classmethod
    def _bulk_create(cls,num:int):
        '''
        use bulk create, below 2.10.0 not has this attr
        :return:
        '''
        bulk=[]
        g=Generic(locales.EN)
        for _ in range(num):
            bulk.append(
                Supplier(
                    name=g.person.name(),
                    address=g.address.address(),
                    phone=g.person.telephone()
                )
            )
        with db.atomic():
            Supplier.bulk_create(bulk)

    @classmethod
    def _create_many(cls,num:int):
        '''
        use create many, insert num*100 count row
        :param num:
        :return:
        '''

        def gen_supplier():
            '''yield 100 count of supplier'''
            field=Field(locales.EN)
            supplier=(
                lambda : {
                    'name':field('name'),
                    'address':field('address'),
                    'phone':field('telephone')
                }
            )
            yield Schema(schema=supplier).create(iterations=100)

        for _ in range(num):
            supplier=next(gen_supplier())
            with db.atomic():
                Supplier.insert_many(supplier).execute()


    class Meta:
        database = db
        table_name = 'supplier'


class Goods(BaseModel):
    supplier = ForeignKeyField(Supplier, related_name='goods')
    name = CharField(max_length=100, index=True)
    click_num = IntegerField(default=0, )
    goods_num = IntegerField(default=0, )
    price = FloatField(default=0.00, )
    brief = TextField()

    @classmethod
    def _bulk_create(cls,num:int):
        query=Supplier.select()
        count=query.count()
        bulk=[]
        g=Generic(locales.EN)
        food_keys=['vegetables','drinks','fruits']
        for _ in range(num):
            bulk.append(
                Goods(
                    supplier=query[randint(0,count-1)],
                    name=g.food._choice_from(food_keys[randint(0,2)]),
                    click_num=randint(1,1000),
                    goods_num=randint(0,10000),
                    price=round(uniform(20.5,200.5),2),
                    brief=g.text.text()
                )
            )
        with db.atomic():
            Goods.bulk_create(bulk)

    @classmethod
    def _create_many(cls,num:int):
        field = Field(locales.EN)
        goods = (
            lambda: {
                'supplier_id': randint(1,1000), #supplier must hit db
                'name':field('food.vegetable'),
                'click_num':randint(1, 1000),
                'goods_num':randint(0, 10000),
                'price' : round(uniform(20.5, 200.5), 2),
                'brief':field('text')
            }
        )
        goods=Schema(schema=goods).create(iterations=num)
        with db.atomic():
            Goods.insert_many(goods).execute()

    class Meta:
        table_name = 'goods'
```
When I try to use join method, I found some doubts, so maybe there is wrong about my code or some flaws of this lib

```
def query_data():
    '''
    http://docs.peewee-orm.com/en/latest/peewee/query_examples.html#
    :return:
    '''
    print('-'*20+'case,limit,order,distinct'+'-'*20)
    cost = Case(None, [(Goods.price > 100, 'expensive')], 'cheap')
    query1=(Goods.select(Goods.name,cost.alias('cost')).order_by(Goods.name).limit(10).distinct())
    for item in query1:
        print(f'{item.name:>15}:{item.cost}')

    print('-' * 20 + 'join' + '-' * 20)
    query2=(Goods.select(
        Goods.name.alias('g_name'), Supplier.name.alias('s_name')
    ).join(Supplier).where(Goods.name.contains('am')).order_by(Goods.name).limit(10))
    for item in query2:
        print(item)
```
when i call query_data func, get this result
```
--------------------case,limit,order,distinct--------------------
Amaranth Leaves:expensive
      Artichoke:expensive
      Artichoke:cheap
        Arugula:expensive
        Arugula:cheap
      Asparagus:expensive
      Asparagus:cheap
      Aubergine:cheap
      Aubergine:expensive
        Avocado:expensive
--------------------join--------------------
None
None
None
None
None
None
None
None
None
None
```
But I print sql of query2, and it is ok when run script in mysql
```
SELECT `t1`.`name` AS `g_name`, `t2`.`name` AS `s_name` FROM `goods` AS `t1` INNER JOIN `supplier` AS `t2` ON (`t1`.`supplier_id` = `t2`.`id`) WHERE (`t1`.`name` LIKE '%am%') ORDER BY `t1`.`name` LIMIT 10;
```
result below:
![image](https://user-images.githubusercontent.com/45066722/71814585-d5108700-30b7-11ea-9943-90b5235f0d32.png)


__Q: why all the item in query2 is None?__







Don't alias the good or supplier name in query2. The aliases are confusing you. Query2 returns rows but the repr is empty (none) because of the way you aliases the good name.

I strongly suggest you check out the docs on relationships and joins:

http://docs.peewee-orm.com/en/latest/peewee/relationships.html

@coleifer Thanks all for you help, i made a mistake and not use the relationship
```
 query3 = (Goods.select(
        Goods.name.alias('g_name'), Supplier.name.alias('s_name')
    ).join(Supplier).where(Goods.name.contains('am')).order_by(Goods.name).limit(10))
    for item in query3:
        print(f'{item.g_name:>15}:{item.supplier.s_name}')
```

```
Amaranth Leaves:Joshua
Amaranth Leaves:Georgeann
Amaranth Leaves:Londa
Amaranth Leaves:Lee
  Bamboo Shoots:Hubert
  Bamboo Shoots:Seymour
  Bamboo Shoots:Delmar
  Bamboo Shoots:Buster
  Bamboo Shoots:Mirella
        Edamame:Delmy
```