Issue: Using `Case().alias` with join
Author: cc00010
State: closed
Created: 2022-08-07 13:32:53

Let's say I have the following models:
```python3
class AttachedFile(Model):
  article = ForeignKeyField(Article)
  file_name = StringField() # this is public
  file_url = StringField() # but this field requires users to be subscribed

class Article(Model):
  # ...
  attached_file = ForeignKeyField(AttachedFile)
  # ...
  
class User(Model):
  # ...

class Subscription(Model):
  # ...
  user = ForeignKeyField(User)
  # ...

```
So what I'm trying to do is to "hide" the `article.attached_file.file_url` field if the user doesn't have a subscription. So:
```python3
user_id = 3 # dummy id
sub_cte = (Subscription.select()
  .where(Subscription.user == user_id).cte('sub_cte'))

article_fields = [
  AttachedFile.id,
  AttachedFile.file_name,
  Case(fn.EXISTS(sub_cte.select()), [(False, None)], AttachedFile.file_url).alias("file_url") # <- HERE IS THE PROBLEM
]

target_article = (Article.select(*article_fields)
  .join_from(Article, User)
  .join_from(Article, AttachedFile)
  .with_cte(sub_cte)
  .get_or_none())

if target_article is not None:
  print(target_article.attached_file.file_url)
```
Now the `file_id` belongs to `target_article` not to the `target_article.attached_file`, and the structure of `target_article` is not what I want. 

I even tried this:

```python3
Case(...).alias(AttachedFile.file_url) 
# or 
Case(...).alias('"t1"."file_url"')
```
But it's raising an error.

What I CANNOT do:
- not using cte's
- using subqueries
- manually setting `target_article.attached_file.file_url`

What I'm expecting is this:
```json
{
  "attached_file": { "file_name": "test.jpg", "file_url": null }
}
```
not this:
```json
{
  "attached_file": { "file_name": "test.jpg" }
  "file_url": null
}
```

I think Peewee should automatically handle such "calls":
```python3
Case(...).alias(AttachedFile.file_url)
```

I note one strange thing about your example -- why is there a circular foreign-key between AttachedFile and Article? Also there seems to be no fk between User and Article, though that's referenced in your example. Overall I think everything is behaving very normally in the following example:

```python
class AttachedFile(Base):
    file_name = TextField() # this is public
    file_url = TextField() # but this field requires users to be subscribed

class Article(Base):
    attached_file = ForeignKeyField(AttachedFile)

class User(Base):
    username = TextField

class Subscription(Base):
    user = ForeignKeyField(User)

db.create_tables([AttachedFile, Article, User, Subscription])

u1, u2, u3 = [User.create(username=u) for u in ('u1', 'u2', 'u3')]
Subscription.create(user=u3)

af1 = AttachedFile.create(file_name='af1', file_url='https://af1')
af2 = AttachedFile.create(file_name='af2', file_url='https://af2')

a1 = Article.create(attached_file=af1)
a2 = Article.create(attached_file=af2)

def show_attached(uid):
    sub_cte = Subscription.select().where(Subscription.user == uid).cte('sub_cte')
    sel = (AttachedFile.id,
           AttachedFile.file_name,
           Case(fn.EXISTS(sub_cte.select()),
               [(False, None)],
               AttachedFile.file_url).alias('file_url'))
    q = (Article
         .select(*sel)
         .join_from(Article, AttachedFile)
         .with_cte(sub_cte))
    return q

# For user-2 there is no subscription, so file url is None:
for row in show_attached(u2.id):
    print(row.attached_file.file_name, row.file_url)

# For user-3 there is a subscription, so file url is shown:
for row in show_attached(u3.id):
    print(row.attached_file.file_name, row.file_url)
```

It sounds like the crux of the issue is that you want the `file_url` patched onto the related `AttachedFile` instance? Unfortunately, Peewee does not have a way to specify which model in the model-graph the aliased `Case` should be applied-to. Why not select from `AttachedFile`, though, since that seems to be the only data you're interested in? Alternatively, you can handle setting the attributes yourself when serializing or iterating. Lastly, you can use the `.objects()` query-chaining method to apply all selected columns onto the `Article`, or use `.dicts()` to get a flat dictionary of the selected data.

Here's how I would do it, though:

```python
    q = (AttachedFile
         .select(*sel)
         .join_from(AttachedFile, Article)
         .with_cte(sub_cte))

...

for row in show_attached(u3.id):
    print(row.file_name, row.file_url)
```

I do agree it would be nice to support a way to specify which model an arbitrary expression gets bound-to, but that would require some new APIs as I believe a great many existing implementations rely on the current behavior, and in 12 years this is the first time this has been requested. So I am going to hold-off for now, but will give it some thought and post here if anything should change.

>I note one strange thing about your example -- why is there a circular foreign-key between AttachedFile and Article? Also there seems to be no fk between User and Article, though that's referenced in your example. Overall I think everything is behaving very normally in the following example:

Yeah, that piece of code is just to give some context.

Thanks for the detailed answer! I'll try that.

Added a new little helper in `master` eeff376be5af089e7d3849284a1c1d71b5df215f that allows you to specify the target model for a column-like. So your example:

```python
def show_attached(uid):
    sub_cte = Subscription.select().where(Subscription.user == uid).cte('sub_cte')
    sel = (AttachedFile.id,
           AttachedFile.file_name,
           Case(fn.EXISTS(sub_cte.select()),
               [(False, None)],
               AttachedFile.file_url).alias('file_url').bind_to(AttachedFile))  # Note the .bind_to() here.
    q = (Article
         .select(*sel)
         .join_from(Article, AttachedFile)
         .with_cte(sub_cte))
    return q

# Note the file_url is now on attached_file!
for row in show_attached(u2.id):
    print(row.attached_file.file_name, row.attached_file.file_url)

# For user-3 there is a subscription, so file url is shown:
for row in show_attached(u3.id):
    print(row.attached_file.file_name, row.attached_file.file_url)
```

@coleifer That's what I need, but shouldn't it just be like:

```python3 
Case(...).alias(AttachedFile.file_url) # conditional `file_url`
# and 
Value('https://...').alias(AttachedFile.file_url) # replace the value of `file_url`
# and
(AttachedFile.file_size / 1000).alias(AttachedFile.file_size) # convert KB to MB
```
it just looks more compact and dynamic. I think it doesn't break anything, right?

`to_bind` would be extra here as `AttachedFile` already tells Peewee the model we want to bind.

And also we will be able to pass model columns into the `alias` method:
```python3
Article.select(Value('it is a value').alias(Article.title))
```
instead of "hard coding" the column names:
```python3
Article.select(Value('it is a value').alias('title'))
```
or it can be used like this (It looks a bit strange, but it's very useful in some cases):
```python3
Article.select(AttachedFile.file_name.alias(Article.title))
```

Let me think about it for a bit this morning.

So the benefit of using `.bind_to()` is that you can specify the destination model in a multi-model query *regardless* of whether the alias corresponds to a field. For example, from the tests:

https://github.com/coleifer/peewee/commit/eeff376be5af089e7d3849284a1c1d71b5df215f#diff-969c0712020e8472d8d78ae32190692cc1e8a3e16dc17f6977c8f87a1328adb7R4896-R4904

In the above, we alias a Case expression to a name that does not correspond to a field, and also specify that it should be applied on the related `User` instance.

If `Alias()` is extended to support a field/column as the destination, then we lose this functionality.

I think I'm going to leave it as-is for now, even though it is more verbose in your particular examples.