Issue: Selecting multiple instances of the same model per row
Author: the-vindicar
State: closed
Created: 2021-03-04 20:00:32

Here is a minimal example of what I'm trying to do.
### Model
We have a model in many-to-many relation to itself.
```python
class BaseModel(peewee.Model):
    class Meta:
        database = peewee.SqliteDatabase(':memory:')

class Person(BaseModel):
    id = peewee.AutoField()
    name = peewee.TextField()

class Like(BaseModel):
    likes = peewee.ForeignKeyField(Person)
    liked = peewee.ForeignKeyField(Person)
    class Meta:
        primary_key = peewee.CompositeKey('likes', 'liked')
```
### Query
I generate some sample data, and then try to select both sides of the relationship in one row.
```python
john = Person.create(name='John')
jane = Person.create(name='Jane')
Like.create(likes=john, liked=jane)

Likes = Person.alias('likes')
Liked = Person.alias('liked')

query = (Likes.select(Likes, Liked)
    .join(Like, on=Like.likes)
    .join(Liked, on=Like.liked)
)

print(query.tuples()[:]) # [(1, 'John', 2, 'Jane')]
print(query[:]) # [<Person: 1>] ???
```
As you can see, it works fine if I return raw tuples. But I attempt to return models, only the model I'm select()'ing on is returned.
Is there a way around that? Can I receive tuples, akin to `(john, jane)`? Or do I have to provide a proxy constructor that will construct the appropriate model objects from the raw tuples?

Please keep in mind that this is a simplified example. In the real case, the relationship has multiple "steps" (intermediary tables), even though the ends are still both the same Model in many-to-many relationship. Thus simply performing the select on Like instead is not as easy as it sounds.

In SQL this is natural to return as a tuple, but what you're expressing is represented in Peewee (by default) as a model graph.

This hopefully makes it more clear:

```python

Liked = Person.alias('liked')

# Here we are selecting the source person, joining through the Like
# model, and then also selecting the liked person, aliased as "Liked".
query = (Person
         .select(Person, Like, Liked)
         .join(Like, on=(Like.likes == Person.id))
         .join(Liked, on=(Like.liked == Liked.id)))

# Peewee converts this row-tuple into a graph of the objects
# and relations.
for person in query:
    # To access the "Liked"-person's name, we go from the source
    # person -> the likes intermediary table -> the liked person instance.
    # This does not incur any hidden queries since we selected all the
    # data we needed up-front.
    print(person.name, person.likes.liked.name)
```

You can control the attributes peewee patches the joined instances onto to make it more natural:

```python

query = (Person
         .select(Person, Like, Liked)
         .join(Like, on=(Like.likes == Person.id), attr='liked')
         .join(Liked, on=(Like.liked == Liked.id), attr='person'))

for person in query:
    print(person.name, person.liked.person.name)
```

If you want to skip the whole graph thing, you can use the ".objects()" method on the query - but you have to alias your columns to avoid name collisions:

```python
query = (Person
         .select(Person, Liked.name.alias('liked_name'))
         .join(Like, on=(Like.likes == Person.id), attr='liked')
         .join(Liked, on=(Like.liked == Liked.id), attr='person')
         .objects())

for person in query:
    print(person.name, person.liked_name)
```

You may find these documents useful:

* http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources
* http://docs.peewee-orm.com/en/latest/peewee/relationships.html#implementing-many-to-many