I have a database with a table named friends. That table has two columns, "user_id" and "friend_id". Those are foreign keys from the Users table.

My friends table right now:

               user_id               |              friend_id
------------------------------------- -------------------------------------
 google-oauth2|11539665289********** | google-oauth2|11746442253**********
 google-oauth2|11746442253********** | google-oauth2|11539665289**********
 google-oauth2|11746442253********** | google-oauth2|11111111111**********

The first two rows are the same IDs but flipped. Those Users I want to retrieve, because they added eachother. The third row only added another guy, that one shouldn't be retrieved.

My SQLModels (models.py):

class Friends(SQLModel, table=True):
    __tablename__ = "friends"
    user_id: str = Field(sa_column=Column('user_id', VARCHAR(length=50), primary_key=True), foreign_key="users.id")
    friend_id: str = Field(sa_column=Column('friend_id', VARCHAR(length=50), primary_key=True), foreign_key="users.id")

class UserBase(SQLModel):
    id: str
    username: Optional[str]
    country_code: Optional[str]
    phone: Optional[str]
    picture: Optional[str]

    class Config:
        allow_population_by_field_name = True


class User(UserBase, table=True):
    __tablename__ = 'users'
    id: str = Field(primary_key=True)
    username: Optional[str] = Field(sa_column=Column('username', VARCHAR(length=50), unique=True, default=None))
    phone: Optional[str] = Field(sa_column=Column('phone', VARCHAR(length=20), unique=True, default=None))
    picture: Optional[str] = Field(sa_column=Column('picture', VARCHAR(length=255), default=None))

My fastapi endpoint:

@router.get("", status_code=status.HTTP_200_OK, response_model=models.FriendsList, name="Get Friends for ID",
            tags=["friends"])
async def get_friends(
        user_id: str = Query(default=None, description="The user_id that you want to retrieve friends for"),
        session: Session = Depends(get_session)
):
    stm = select(models.User, models.Friends).where(models.User.id == models.Friends.friend_id, models.Friends.user_id == user_id)
    res = session.exec(stm).all()
    if not res:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND,
                            detail="There are no friendships associated with this id.")
    users = []
    for item in res:
        users.append(item[0])
    return models.FriendsList(users=users)

My code works perfectly fine, only the query needs to be replaced.

stm = select(models.User, models.Friends).where(models.User.id == models.Friends.friend_id, models.Friends.user_id == user_id)
res = session.exec(stm).all()

This query returns every User that has the given ID as user_id, but doesn't check if there is an entry the other way around.

Example for what I want to get:

I make a GET request to my endpoint with the id google-oauth2|11746442253**********. I would get the User google-oauth2|11539665289**********. (The User google-oauth2|11111111111********** would not be retrieved because there is no entry the other way arround)

I hope you guys understand my problem. If there are any questions feel free to ask.

Best regards, Colin

CodePudding user response：

are you able to add another column called "accepted" with values like 0 or 1?

           user_id                   |          friend_id                  | accepted
------------------------------------- -----------------------------------------------
 google-oauth2|11539665289********** | google-oauth2|11746442253********** | 1
 google-oauth2|11746442253********** | google-oauth2|11539665289********** | 1
 google-oauth2|11746442253********** | google-oauth2|11111111111********** | 0

then you have two options:

1. you could make a relationship on the user table called "friends" and set the lazy parameter to "dynamic" (lazy='dynamic') and then query: user.friends.filter_by(accepted=1).all()

2. or you could write a query like:

   query = Friends.query.filter(Friends.user_id==user_id).filter(Friends.accepted == 1).all()

generally relational databases aren't the best solution for these types of scenarios - if you're very flexible and not too far in you could checkout a NoSQL solution like MongoDB

CodePudding user response：

As I said in the comment, without a simple example I can't actually try myself, but I did just have an idea. You might need to modify the subquery syntax a little bit, but I reckon theoretically this could work:

stmt = select(Friends.user_id, Friends.friend_id).where(
    tuple_(Friends.user_id, Friends.friend_id).in_(select(Friends.friend_id, Friends.user_id)) 
)

Basically it's just checking every (user_id, friend_id) if there is a matching (friend_id, user_id).