Issue: Difficulty in accessing joined-on attribute
Author: esztermarton
State: closed
Created: 2019-10-22 12:02:59

Using version 3.9.2.
Attempting to create a query for the following usecase (quite similar to [an example with tweets in the documentation](http://docs.peewee-orm.com/en/latest/peewee/relationships.html#common-table-expressions)):

- Fetch a subset of users with their latest tweet (if it exists)
- Be able to access both user and tweet objects after the query for further `where` clauses in different functions (for example to query for the language of the tweet or the nationality of the user)

The User, Tweet and UserCollection objects look something like this:
```
class User(Model):
    name = CharField()

class Tweet(Model):
    user = ForeignKeyField(
        column_name="user_id",
        field="id",
        model=User,
        backref="tweets",
    )

class UserCollection(Model):
    users = ManyToManyField(User, backref="collections")

```

I tried two different approaches, and in both I have hit walls.
**First approach:**
- Groupby query to fetch latest tweet ids per user
- join this on to users
- join tweets on to this

Problems:
- I can't seem to access the tweet objects from the user.

Code:
```
def get_users_with_latest_tweets(self):
      Latest = Tweet.alias()
        latest_tweet_id = (
            Latest.select(
                Latest.user.alias("user"), fn.MAX(Latest.id).alias("id")
            )
            .group_by(Latest.user)
            .alias("latest_tweet_id")
        )

        return (
            self.users
                .join(
                latest_tweet_id,
                on=(latest_tweet_id.c.user == User.id),
                join_type=JOIN.LEFT_OUTER,
            )
            .switch(User)
            .join(
                Tweet,
                on=(Tweet.id == latest_tweet_id.c.id),
                join_type=JOIN.LEFT_OUTER,
                # attr="t",
            )
        )

users_with_latest_tweets = user_collection.get_users_with_latest_tweets()
```

- When I iterate through users_with_latest_tweets they are user objects, as expected. 
- However, if on the user object I access the tweets attribute, it seems to contain not just the ones that I joined on in the first join, but also all other tweets belonging to the user too. This is despite the fact that the query itself does work as expected, as shown by the fact that if I iterate through users_with_latest_tweets.tuples()
- When I explicitly set attr, it seemed to ignore it: when i iterate through the objects, they did not have the attribute that I would have wanted the tweets to be found in. On the other hand, from looking at source code I might have expected it to raise an error, but it also didn't do that. I wonder if it is because it was joining it on to not the User objects, but rather the Tweet alias object? I'm not sure.

**Second approach:**
- Groupby query to fetch latest tweet ids per user
- join tweets on to users and add a where clause to limit it to certain tweet ids plus those with no tweet id joined

Problems:
- On the where clause, it doesn't include those where tweet id is null

Code:
```
def get_users_with_latest_tweets(self):
        Latest = Tweet.alias()
        latest_tweet_id = (
            Latest.select(
                Latest.user.alias("user"), fn.MAX(Latest.id).alias("id")
            )
            .group_by(Latest.user)
        )
        tweet_ids = [l.id for l in latest_tweet_id]

        return (
            self.users.join(
                Tweet,
                on=(User.id == Tweet.user),
                join_type=JOIN.LEFT_OUTER,
            ).where(
                Tweet.id.is_null()
                | (Tweet.id.in_(tweet_ids))
            )
        )

users_with_latest_tweets = user_collection.get_users_with_latest_tweets()
```

I would expect users_with_latest_tweets to have the same length as self.users (since it is a left outer join). However, when there are users with no tweets, those users are not returned.

I tried the second approach to simplify the first syntactically, I'm not sure if there is significant difference in efficiency between the two.

Could you please help with either one or both of these?

You need to explicitly select the columns from Tweet which you want to retain.

Using `self.users` is a bit yuck. In fact using that many-to-many is extremely yuck because it serves no purpose and adds needless tables/joins. All you really need is a table containing two foreign keys to User to serve as your method of relating one user to another:

```python
class Relationship(BaseModel):
    from_user = ForeignKeyField(User, backref='relationships')
    to_user = ForeignKeyField(User, backref='related_to')
```

This is from the twitter example code, by the way: https://github.com/coleifer/peewee/blob/master/examples/twitter/app.py

Thanks for reading through my essay of an issue :)

> You need to explicitly select the columns from Tweet which you want to retain.

Very helpful thanks - I assume you mean this for the one that uses the .is_null()

> Using self.users is a bit yuck. In fact using that many-to-many is extremely yuck because it serves no purpose and adds needless tables/joins. All you really need is a table containing two foreign keys to User to serve as your method of relating one user to another:

Yes, I don't really like it that much either. Does it not add to efficiency though, because it is then joining things on to a handful of users instead of hundreds of thousands? Would be happy to join on User instead if self.users adds nothing but complexity.

I do really like your Relationship table solution to avoid the many-to-many. Unless I'm misunderstanding though, the many to many is kind of needed in my usecase, it is a pretty important object that allows me to do lots of things to those UserCollections and to each User within it (here I just implemented the same thing with the Twitter example and needed to include it in case the ManyToMany was the cause of my problems...)


The Relationship table is basically the "through table" of a many-to-many right?

I don't get the sense that you're very familiar with SQL, though I may be mistaken. However, at the end of the day you should keep in mind that everything gets translated into a sql query which returns rows of columns.

Documentation on selecting from multiple sources:

http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources

> Unless I'm misunderstanding though, the many to many is kind of needed in my usecase, it is a pretty important object that allows me to do lots of things to those UserCollections and to each User within it

If a given collection has a semantic meaning, then yeah, using the schema you've described does make sense. In your example, though, the table was empty except for the many-to-many relationship, which makes the whole construction inefficient and overly complex.

I'd suggest stackoverflow, but honestly, I really don't know how much I can help because you really need a firm grasp on SQL to be able to use it efficiently.

Hey @coleifer - just so you know, changing my code to the equivalent of the following (what I understood by "explicitly select columns from Tweets") didn't work:
```
def get_users_with_latest_tweets(self):
        Latest = Tweet.alias()
        latest_tweet_id = (
            Latest.select(
                Latest.user.alias("user"), fn.MAX(Latest.id).alias("id")
            )
            .group_by(Latest.user)
        )
        tweet_ids = [l.id for l in latest_tweet_id]

        return (
            self.users.join(
                Tweet,
                on=(User.id == Tweet.user),
                join_type=JOIN.LEFT_OUTER,
            ).select(User.id, User.name, Tweet.id)
            .where(
                Tweet.id.is_null()
                | (Tweet.id.in_(tweet_ids))
            )
        )

users_with_latest_tweets = user_collection.get_users_with_latest_tweets()
```

I also kind of think that if one is able to query like  `.where(Tweet.id.in_(tweet_ids))`, I'd also expect to be able to query like `.where(Tweet.id.is_null())` on a joined on column without having to specify columns specifically. I'm still not quite sure if this, and/or the other issue I had, are due to what you would classify as bugs or missing features, but if so and theres plans to change it, I'd maybe be happy to help out and have a look at it?

Otherwise I've worked around it in my code now. Thanks for all your help.