FastAPI + Tortoise ORM + FastAPI Users (Python) - Relationship - Many To Many

I'm using FastAPI, Tortoise ORM and FastAPI Users to make an API to learn. Basically I get cities weather data from OpenWeather API and store in a SQLite database with Tortoise ORM. I have authentication with FastAPI Users.

I was using the foreign key and the relations worked fine. But now I want some change (an improvement). I want each user to have their items, and after logged and access the endpoint (@router.get("/me/onlyitems/", response_model=List[schemas.Card_Pydantic])), it receives only their items.

It was working fine, I receive only the user items (using owner_id), but my item table uses an external unique ID (the item is a city and each city have an unique ID) and it is the primary key. But, when another user try to add the same city, it gives an error because it cannot have the same city in the table, even with another owner. Well, it is how should be... but I want to have other users accessing the same cities (they have the same info, they are not customized). Then, I want many users having access to the same item (and each user can access multiple items).

I don't think it is ideal to repeat the item info (using another ID) for each user, would be better different user access the same item.

I think it would be a Many To Many relation, but I don't know how to make the relationship and get it with FastAPI.

I thought about create an intermediary table, with UserID and CityID only (and an autoincrement ID) and use it to get the related data, but I don't know if it is the best way or it is possible.

I tried to learn from Tortoise documentation, but I couldn't find the part I need or understand some complex examples and parts of the documentation.

I'm using updated versions of the packages and Python 3.9.4

Well, let me show some code:

models.py

class UserModel(TortoiseBaseUserModel):  # From FastAPI Users
    cards: fields.ReverseRelation["Card"]


class OAuthAccountModel(TortoiseBaseOAuthAccountModel): # From FastAPI Users
    user = fields.ForeignKeyField("models.UserModel", related_name="oauth_accounts")


class User(models.BaseUser, models.BaseOAuthAccountMixin): # From FastAPI Users
    pass


class UserCreate(models.BaseUserCreate): # From FastAPI Users
    pass


class UserUpdate(User, models.BaseUserUpdate): # From FastAPI Users
    pass


class UserDB(User, models.BaseUserDB, PydanticModel): # From FastAPI Users
    class Config:
        orm_mode = True
        orig_model = UserModel


user_db = TortoiseUserDatabase(UserDB, UserModel, OAuthAccountModel) # From FastAPI Users


class Card(tmodels.Model):
    """
    The Card model, related to the user.
    """
    id = fields.IntField(pk=True)
    city_name = fields.CharField(max_length=30)
    temperature = fields.FloatField()
    state = fields.CharField(max_length=30, null=True)
    icon = fields.CharField(max_length=3, null=True)
    period = fields.CharField(max_length=20, null=True)
    created_at = fields.DatetimeField(auto_now_add=True)
    updated_at = fields.DatetimeField(auto_now=True)
    owner: fields.ForeignKeyRelation[UserModel] = fields.ForeignKeyField(
        "models.UserModel", related_name="cards")

    class PydanticMeta:
        exclude = ["owner"]

I tried to change my owner field and the their relationship to UserModel:

class Card(tmodels.Model):
owner = fields.ManyToManyField("models.UserModel", related_name="cards")

class UserModel(TortoiseBaseUserModel):
    cards: fields.ManyToManyRelation["Card"]

I don't know if it is right, I couldn't make it works, but the problem could be in other places.

My endpoint for the user create a item:

@router.post("/", response_model=Card_Pydantic)
async def create_item_for_current_user(
        card: CardCreate, user: UserDB = Depends(fastapi_users.get_current_active_user)):
    card_obj = await crud.create_user_card(card=card, user=user)
    if card_obj is None:
        raise HTTPException(status_code=404, detail=f"City '{card.city_name}' not found")
    return await card_obj

The CRUD function used (to create an item) here:

async def create_user_card(
        card: schemas.CardCreate, user: UserDB = Depends(fastapi_users.get_current_active_user)):
    card_json = await weather_api.get_api_info_by_name(card.city_name)
    if card_json is None:
        return None
    card_obj = await Card.create(**card_json,
                                 owner_id=user.id)  # ** is used to pass the keys values as kwargs.
    return await card_obj

My endpoint to get the items owned by the user:

@router.get("/me/onlyitems/", response_model=List[schemas.Card_Pydantic])
async def read_current_user_items(user: UserDB = Depends(fastapi_users.get_current_active_user)):
    user_cards = await schemas.Card_Pydantic.from_queryset(Card.filter(owner_id=user.id).all())
    return await crud.update_only_card_for(user_cards, user.id)

My crud function is:

async def update_only_card_for(user_cards, owner_id):
    for city in user_cards:
        await update_card(city.id, owner_id)
    return await schemas.Card_Pydantic.from_queryset(Card.filter(owner_id=owner_id).all())

# The update function (it will check if it need to update or not)
async def update_card(city_id: int, owner_id: UUID = UserDB):
    card_obj = await Card.get(id=city_id)
    card_time = card_obj.updated_at
    timezone = card_time.tzinfo
    now = datetime.now(timezone)
    time_to_update = now - timedelta(minutes=15)

    if card_time < time_to_update:
        card_json = await weather_api.get_api_info_by_id(city_id)
        await card_obj.update_from_dict(card_json).save()  # with save will update the update_at field
    card_updated = await schemas.Card_Pydantic.from_queryset_single(Card.get(id=city_id))
    return card_updated

How I could change it to get the cards the user need to had access? I think I can't use the filter by owner_id, (well, if I could create my intermediary table, I could, but don't know how do it and fetch de data) but can't think how to make the relation to link the user to the items he added and at same time, other users access the same items.

This code works fine (I'm using a nuxt.js front end), until there is a duplicated city...

What is the best way to handle it and how?

Thanks, Diego.

I solved it. I think the Tortoise documentation is a little confuse, but I found how to create the relationships and retrieve the data.

The models: (simplified version)

class Card(tmodels.Model):
    id = fields.IntField(pk=True)
    city_name = fields.CharField(max_length=30)
    owners: fields.ManyToManyRelation["UserModel"] = fields.ManyToManyField(
        "models.UserModel", related_name="cards", through="card_usermodel")

class UserModel(TortoiseBaseUserModel):
    cards: fields.ManyToManyRelation[Card]

through="card_usermodel" will create an intermediary table with the Card id and the owner id, it will define the relationship.

To make the data operations:

async def create_user_card(
        card: schemas.CardCreate, user: UserDB = Depends(fastapi_users.get_current_active_user)):
    card_json = await weather_api.get_api_info_by_name(card.city_name)
    if card_json is None:
        return None
    owner = await UserModel.get_or_none(id=user.id)
    card_obj = await Card.update_or_create(**card_json)
    await card_obj[0].owners.add(owner)
    return await schemas.Card_Response_Pydantic.from_queryset_single(Card.get(id=card_json.get("id"), owners=user.id))

Basic is it. I changed some Pydantic models too, to validate the responses.