Issue: Select specific columns from Model alias
Author: conqp
State: closed
Created: 2022-03-16 10:21:12

I have a use case where I need to join two foreign keys with the same table:

```python
class Configuration(CustomerModel):
    """Customer configuration for charts."""

    name = HTMLCharField(255)
    description = HTMLCharField(255, null=True)
   ...
    logo = ForeignKeyField(
        File, column_name='logo', null=True, lazy_load=False)
    dummy_picture = ForeignKeyField(
        File, column_name='dummy_picture', null=True, lazy_load=False)

    @classmethod
    def select(cls, *args, cascade: bool = False) -> Select:
        """Selects records."""
        if not cascade:
            return super().select(*args)

        logo_file = File.alias()
        logo_file_db_file = FileDBFile.alias()
        dummy_picture_file = File.alias()
        dummy_picture_file_db_file = FileDBFile.alias()
        return super().select(
            cls, Colors, logo_file, *logo_file_db_file.meta_fields(),
            dummy_picture_file, *dummy_picture_file_db_file.meta_fields(),
            *args, cascade=cascade
        ).join_from(
            cls, Colors
        ).join_from(
            cls, logo_file, on=cls.logo == logo_file.id,
            join_type=JOIN.LEFT_OUTER
        ).join(
            logo_file_db_file, join_type=JOIN.LEFT_OUTER).join_from(
            cls, dummy_picture_file,
            on=cls.dummy_picture == dummy_picture_file.id,
            join_type=JOIN.LEFT_OUTER
        ).join(
            dummy_picture_file_db_file, join_type=JOIN.LEFT_OUTER
        )
```
However, I do only want to select certain fields of the respective aliases. In this case it is because I reference a file database, which also contains a BLOB field with the data, that I don't need and just want the metadata. Here's the appropriate model:
```python
class File(FileDBModel):
    """A file entry."""

    bytes = BlobField()
    mimetype = CharField(255)
    sha256sum = FixedCharField(64)
    size = BigIntegerField()   # File size in bytes.
    created = DateTimeField(default=datetime.now)
    last_access = DateTimeField(null=True, default=None)
    accessed = IntegerField(default=0)

    ...

    @classmethod
    def meta_fields(cls) -> Iterable[Field]:
        """Returns an iterable of metadata fields."""
        return (
            cls.id, cls.mimetype, cls.sha256sum, cls.size, cls.created,
            cls.last_access, cls.accessed
        )
```

However, when I do a select on the respective fields, I get an error:
```python
>>> File.select(*File.meta_fields()).get()
<File: 61805ba3947f7bb245084540d05bf025709ee26678c5a069fcc8f37d8a2fb51f>
>>> alias = File.alias()
>>> alias.select(*alias.meta_fields()).get()
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, "Unknown column 't1.id' in 'field list'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 6933, in get
    return clone.execute(database)[0]
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 1911, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 1982, in execute
    return self._execute(database)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 2155, in _execute
    cursor = database.execute(self)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3173, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/local/lib/python3.9/dist-packages/peeweeplus-1.19.dev28+g607d1d2-py3.9.egg/peeweeplus/database.py", line 26, in execute_sql
    return super().execute_sql(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3167, in execute_sql
    self.commit()
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 2933, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1054, "Unknown column 't1.id' in 'field list'")
>>> alias.select(*File.meta_fields()).get()
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, "Unknown column 't1.id' in 'field list'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 6933, in get
    return clone.execute(database)[0]
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 1911, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 1982, in execute
    return self._execute(database)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 2155, in _execute
    cursor = database.execute(self)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3173, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/local/lib/python3.9/dist-packages/peeweeplus-1.19.dev28+g607d1d2-py3.9.egg/peeweeplus/database.py", line 26, in execute_sql
    return super().execute_sql(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3167, in execute_sql
    self.commit()
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 2933, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.9/dist-packages/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1054, "Unknown column 't1.id' in 'field list'")
>>> 
```
Am I doing something wrong here, or is this a bug?

**Update**  
I just noticed that everything works, if I select the fields manually:
```python3
>>> alias.select(alias.id, alias.sha256sum).get()
<File: 000003349b2482b0c96176f8bcf8eb6111f2f78b1c17f8078003475635bd1061>
```
And the ModelAlias does not seem to resolve the fields to field aliases:
```python
>>> alias.meta_fields()
(<AutoField: File.id>, <CharField: File.mimetype>, <FixedCharField: File.sha256sum>, <BigIntegerField: File.size>, <DateTimeField: File.created>, <DateTimeField: File.last_access>, <IntegerField: File.accessed>)
>>> 
```
Manually specifying the field aliases everywhere in the code would blow up my code significantly and create code duplication as the meta fields are always the same field.
Is there a way to get the appropriate field aliases programmatically from the ModelAlias?

**Workaround**  
I managed to fix the issue by subclassing `ModelAlias`:
```python
class FileModelAlias(ModelAlias):
    """Alias type for File model."""

    def meta_fields(self) -> Iterable[FieldAlias]:
        """Returns an iterable of metadata field aliases."""
        return (
            self.id, self.mimetype, self.sha256sum, self.size, self.created,
            self.last_access, self.accessed
        )
```
```python
class File(FileDBModel):
    """A file entry."""

    bytes = BlobField()
    mimetype = CharField(255)
    sha256sum = FixedCharField(64)
    size = BigIntegerField()   # File size in bytes.
    created = DateTimeField(default=datetime.now)
    last_access = DateTimeField(null=True, default=None)
    accessed = IntegerField(default=0)

    ....

    @classmethod
    def alias(cls, alias: Optional[str] = None) -> FileModelAlias:
        """Return a file model alias."""
        return FileModelAlias(cls, alias)
```
However, it does not feel too comfortable digging that deep into peewee's internals to get something this basic done.

I think the issue lies with the `__getattr__` delegation of `ModelAlias` to the wrapped model. Maybe it should bind classmethods of the model not to the model, but to itself. Otoh this may create circular recursions in `__getattr__`. I'm going to investigate this further.

Are you able to produce a small or concise demonstration of the issue, as you see it? Also note that it's probably a bad idea to override the `select()` method on a model class as that method is used by other functions -- better to call it something else that doesn't conflict with such a common method.

I think I understand after typing out my own implementation. Here is my example, which I'll be referring to:

```python
class File(Base):
    filename = TextField()
    data = BlobField(default=b'')

class User(Base):
    username = TextField()
    profile = ForeignKeyField(File, null=True, lazy_load=False, backref='user_profiles')
    backup = ForeignKeyField(File, null=True, lazy_load=False, backref='user_backups')

db.create_tables([User, File])

f1 = File.create(filename='profile-pic.jpg')
f2 = File.create(filename='backup.json')
user = User.create(username='u1', profile=f1, backup=f2)

ProfileFile = File.alias()
BackupFile = File.alias()

query = (User
         .select(User, ProfileFile.filename, BackupFile.filename)
         .join_from(User, ProfileFile, on=(User.profile == ProfileFile.id))
         .join_from(User, BackupFile, on=(User.backup == BackupFile.id))
         .order_by(User.username))

for user in query:
    print(user.username, user.profile.filename, user.backup.filename)
```

The issue, as I understand it, is that you want to encapsulate the list of fields selected from the file aliases. The issue is that those `meta_fields()` returns references to the fields on the `File` - not the columns on the specific alias.

Workarounds:

```python
class File(Base):
    filename = TextField()
    data = BlobField(default=b'')

    def meta_fields(self):
        return [getattr(self, f) for f in ['id', 'filename']]

...

query = (User
         .select(
             User,
             *ProfileFile.meta_fields(ProfileFile),
             *BackupFile.meta_fields(BackupFile))
         .join_from(User, ProfileFile, on=(User.profile == ProfileFile.id))
         .join_from(User, BackupFile, on=(User.backup == BackupFile.id))
         .order_by(User.username))
```

The above ensures that the fields are referenced to the alias.

Or alternatively you can use your specific model alias class, as you've done.

But if you are returning the field instances from the model itself, there's no way for peewee to know which alias they are intended to reference.

Fair enough.

> But if you are returning the field instances from the model itself, there's no way for peewee to know which alias they are intended to reference.

Not with the current implementation, indeed. That's why the `ModelAlias` may better be off as a subclass of the given model, that converts its fields into appropriate `FieldAlias`es. This way, the `ModelAlias` could preserve all functionality of the encapsulated model.