Issue: Issue with `Meta.table_alias` (or `Meta.alias`)
Author: ibushong
State: closed
Created: 2019-01-14 05:45:55

I'm encountering this issue when trying to migrate from v2 to v3.

Let's say I have a DB table called `tag2` that I want to reference with a peewee model call `Tag`. If I set `Meta.table_name = 'tag2'`, everything works fine. But if I add `Meta.table_alias = 'tag_alias'`, then UPDATE queries come out like this:
```
UPDATE `tag2` SET `name` = 'something else' WHERE (`tag_alias`.`id` = 3)
```
Interestingly, If I set `Meta.alias = 'tag_alias'`, then UPDATE queries comes out correct, but then SELECT/JOIN queries do not use the alias.


I ran into this issue with peewee 3.8.0/3.8.1. 3.7.1 (< 3.8.0) works as expected.

Thanks for reporting. I will look into this.

This seems like it will not be very easy to resolve, given the architecture of the query-builder and the way I've chosen to implement the current `table_alias` functionality.

The reason for the breakage is due to the switch to using fully-qualified column names in UPDATE queries (and in some other places as well).

I think the best fix for now is to actually remove support for `table_alias`, as it is quite broken in its current form.

I see. Just for some background, the reason I needed to use `table_alias` in v2 was because I has some raw SQL expressions where I needed to reference the table names, which was difficult using the default "t1", "t2", etc. names since they change depending on the order of joins. e.g. I couldn't just do `.order_by(SQL("t1.field"))` because "t1" could correspond to different tables, depending on how the query was generated. By using `table_alias = "tag"`, I could do `.order_by(SQL("tag.field"))` (using over-simplified expressions here).

Is there a way in v3 for me to reference the table name in a raw expression?

Or also, is there a way to configure peewee to use the plain table names in queries, rather than aliasing them to the "t1", "t2", etc names (I'm curious on why it does this?)


Do you have an example of where you need to use plain SQL expressions (i.e., via `SQL()`)? Peewee's query-builder should hopefully be able to handle generating the correct SQL.

You can still call `ModelClass.alias()` and provide an alias. For example:

```python
In [4]: class Tag(Model):
   ...:     entry = ForeignKeyField(Entry)
   ...:     tag = TextField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [5]: TX = Tag.alias('tx')

In [6]: TX.select(TX.tag).sql()
Out[6]: ('SELECT "tx"."tag" FROM "tag" AS "tx"', [])
```

I needed to use a `CASE` expression in `order_by()`, so had to do raw SQL since (AFAIK) v2  doesn't support `CASE`. But now I see that v3 does, so that might solve my problem. I will try re-writing it.

Your example should also work too though.

Thanks!

Update: I was able to fix up the CASE statement by implementing with the new `Case()` operator. 

However I also have some raw MATCH/AGAINST statements that also reference static table names, e.g. `.order_by(SQL("MATCH(venue.name) AGAINST(%s IN BOOLEAN MODE)", [name_like + "*"]))`.

It doesn't look like v3 has a native `Match()`operator, and the aliasing example above actually won't work for me because I use query chaining a lot, so if the base query uses `VenueAlias = Venue.alias('venue')` (in order for the match expression to work), then chaining onto the query at a higher-level (e.g. `q = q.where(Venue.name == "something")`) doesn't work (I would prefer not to have to modify all these top-level queries to account for the aliasing).

Is there a better way to write this MATCH expression so that it gets the correct table name?

I feel like all of this would be resolved if the query generator used the actual table names instead of "t1", "t2"... I actually tried this small patch and it solved a lot of my issues:

```python
class AliasManager(object):
    ...
    def add(self, source):
        if source not in self.mapping:
            self._counter += 1
            # self[source] = 't%d' % self._counter  # ORIG
            self[source] = '%s' % source._path  # Use actual table name
        return self.mapping[source]
```

Thoughts?

> I feel like all of this would be resolved if the query generator used the actual table names instead of "t1", "t2"... I actually tried this small patch and it solved a lot of my issues:

Peewee does use the actual table names for queries besides `SELECT`, but since a `SELECT` query may reference the same table twice in different contexts (e.g. a self-join, or joining the same table twice), it's easier to just use computed aliases.

Plus, one only really worries about the aliases when trying to mix the query-builder with hand-written SQL, which as I said should usually be avoidable.

For `MATCH...AGAINST` I'd suggest wrapping it up in the query-builder primitives like `NodeList`:

```python
# MATCH(venue.name) AGAINST(%s IN BOOLEAN MODE)
def Match(field, value):
    match = fn.MATCH(field)
    val_in_boolean_mode = NodeList((value, SQL('IN BOOLEAN MODE')))
    against = fn.AGAINST(val_in_boolean_mode)
    return NodeList((match, against))
```

You can then:

```python
Venue.select().order_by(Match(Venue.name, 'foo*'))
```

Ah, right.

This `Match()` implementation is definitely the way to go. Thanks so much for the help! (and for peewee in general)


Glad to help!