Issue: Can't understand how pull data from joins
Author: karambaq
State: closed
Created: 2019-08-01 21:45:51

So I have two tables:

# fighters
```
        id               SERIAL PRIMARY KEY,
        left_fighter     text,
        right_fighter    text
```
# timestamp
```
        id          SERIAL PRIMARY KEY,
        timestamp   text,
        daytime     time
```

# Code
```
class BaseModel(Model):
    class Meta:
        database = db


class Fighter(BaseModel):
    id = PrimaryKeyField(null=False)
    left_fighter = CharField()
    right_fighter = CharField()

    class Meta:
        table_name = 'fighters'


class Timestamp(BaseModel):
    id = PrimaryKeyField(null=False)
    timestamp = TimestampField()
    daytime = TimeField()

    table_name = 'timestamp'


fighter = (Fighter.select().join(
    Timestamp, on=(Fighter.id == Timestamp.id)).first())

print(fighter.__dict__)
```

# Output:
```
{'__data__': {'id': 201322952, 'left_fighter': 'Кенши', 'right_fighter': 'Лю Кенг'}, '_dirty': set(), '__rel__': {}}
```

# Question
So I see there is data from first table, but I can't understand how to pull second table data.

You need to select it as well, then it will be available.

```
query = (Fighter
         .select(Fighter, Timestamp)
         .join(Timestamp, on=(Fighter.id == Timestamp.id)))
f = query.first()

print(f.left, f.right, f.timestamp.timestamp, f.timestamp.daytime)
```

http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources

@coleifer Yes, but there are duplicate field names (id), how can I manage this? Can I select all fields except id field?

EDIT: When I join two tables using SQL on id column it lefts only one id column, how can I do that using peewee?

Peewee handles this correctly.

In my example,

```
f.id  # the id of the fighter
f.timestamp.id  # the id of the timestamp
```

Also presumably since you're joining on the id then you can infer one from the other.

I'd not recommend joining an auto-generated id, by the way, as it would be too easy for them to become out-of sync. Better to use an explicit foreign-key.

@coleifer Oh, let me show that:

```
    games = (Fighter.select(Fighter, Timestamp).
             join(Timestamp, on=(Fighter.id == Timestamp.id))
             )
    print(games[0].timestamp.timestamp) # This prints fine
    for g in games.namedtuples(): # There is an error
        print(g)
```

Traceback:
```
 File "/usr/local/lib/python3.7/site-packages/peewee.py", line 4127, in next
    self.cursor_wrapper.iterate()
  File "/usr/local/lib/python3.7/site-packages/peewee.py", line 4043, in iterate
    self.initialize()  # Lazy initialization.
  File "/usr/local/lib/python3.7/site-packages/peewee.py", line 7065, in initialize
    self.tuple_class = collections.namedtuple('Row', attributes)
  File "/usr/local/Cellar/python/3.7.3/Frameworks/Python.framework/Versions/3.7/lib/python3.7/collections/__init__.py", line 373, in namedtuple
    raise ValueError(f'Encountered duplicate field name: {name!r}')
ValueError: Encountered duplicate field name: 'id'
```

Is it ok? If yes can you please explain it to me?

UPD: I'm not so good in database architecture, id fields are inserting manually, they not auto-generated, there is one-to-one relationship, should I make id column in the second table as foreign key?

UPD2: When I'm using `games.dicts()` instead of `games.namedtuples()` it works just as expected.

The behavior is what it is, as Peewee does not handle nesting or relationship-traversal when using the dicts/namedtuples/tuples methods. You can explicitly alias Timestamp.id in the .select() call if you wish, but you need to then explicitly specify all the columns on Timestamp which you intend to select.