Nested API Routes

[motey]

Hi there, thanks for this cool project. Just exploring the possibilities and i love it ❤️
As far as i understand there is no concept of nested routes native to Piccolo API or am i wrong?
What i am trying to achieve is to create a piccolo API endpoint like:
/movie/{movie_id}/actor
and
/movie/{movie_id}/actor/{actor_id}
I am aware of the concept of nested fields in __visible_fields. i could do /movie/?__visible_fields=name,actor.name but i could not get it to work for M2M relations. Also the concept is neat but does not feel very "REST" for my personal taste (which does not mean anything, no front :) )
I tried to achieve by implementing a custom FastAPI endpoint. Lets have a look at small sample ShoppingList app (With the weird feature of having one item on multiple lists, but lets ignore logic here :) ):

from piccolo.columns import (
    Varchar,
    Boolean,
    ForeignKey,
    LazyTableReference,
    M2M,
    Integer,
)
from piccolo.table import Table


# Picollo MODEL
class ShoppingList(Table):
    title: str = Varchar(length=255)
    items = M2M(LazyTableReference("ListHasItem", module_path=__name__))


class ListItem(Table):
    text_value: str = Varchar(1024)
    checked: bool = Boolean()
    lists = M2M(LazyTableReference("ListHasItem", module_path=__name__))


class ListHasItem(Table):
    item: ListItem = ForeignKey(ListItem)
    list: ShoppingList = ForeignKey(ShoppingList)


# Picocolo_api + FastApi
from fastapi import FastAPI
from piccolo_api.fastapi.endpoints import FastAPIWrapper, FastAPIKwargs
from piccolo_api.crud.endpoints import PiccoloCRUD

app = FastAPI()

FastAPIWrapper(
    root_url="/list",
    fastapi_app=app,
    piccolo_crud=PiccoloCRUD(table=ShoppingList),
    fastapi_kwargs=FastAPIKwargs(all_routes={"tags": ["List"]}),
)


# NESTED MODEL ROUTE
import pydantic
from typing import Type, List
from piccolo.utils.pydantic import create_pydantic_model

ListItemOut: Type[pydantic.BaseModel] = create_pydantic_model(
    table=ListItem, model_name="ListItemOut"
)


@app.get("/list/{list_id}/item", tags=["List"], response_model=List[ListItemOut])
async def list_get_items(list_id: int):
    return await ShoppingList.select(ShoppingList.items()).where(
        ShoppingList.id == list_id
    )


# Start app

import uvicorn


uvicorn.run(app, reload=False)

this does work. Lets look at the endpoints:

We now have Item endpoint only nested in a certain list. But the huge caveat is, we lose all the neat Piccolo API extras.

Does anyone have an idea how to have nested paths and Piccolo API magic together?

[motey]

Another solution i tried is to create a custom route:

from piccolo.columns import (
    Varchar,
    Boolean,
    ForeignKey,
    LazyTableReference,
    M2M,
)
from piccolo.table import Table


# Picollo MODEL
class ShoppingList(Table):
    title: str = Varchar(length=255)
    items = M2M(LazyTableReference("ListHasItem", module_path=__name__))


class ListItem(Table):
    text_value: str = Varchar(1024)
    checked: bool = Boolean()
    lists = M2M(LazyTableReference("ListHasItem", module_path=__name__))


class ListHasItem(Table):
    item: ListItem = ForeignKey(ListItem)
    list: ShoppingList = ForeignKey(ShoppingList)


# Picocolo_api + FastApi
from fastapi import FastAPI, APIRouter
import pydantic
from piccolo_api.fastapi.endpoints import FastAPIWrapper, FastAPIKwargs
from piccolo_api.crud.endpoints import PiccoloCRUD
from piccolo.utils.pydantic import create_pydantic_model
from typing import Type

app = FastAPI()


## SOLUTION SECOND TRY
list_router = APIRouter(prefix="/list", tags=["List"])
item_router = APIRouter(prefix="/item", tags=["List"])

FastAPIWrapper(
    root_url="/",
    fastapi_app=list_router,
    piccolo_crud=PiccoloCRUD(table=ShoppingList),
    fastapi_kwargs=FastAPIKwargs(all_routes={"tags": ["List"]}),
)
FastAPIWrapper(
    root_url="/",
    fastapi_app=item_router,
    piccolo_crud=PiccoloCRUD(table=ListItem),
    fastapi_kwargs=FastAPIKwargs(all_routes={"tags": ["List"]}),
)


# Define ListItemOut using create_pydantic_model
ListItemOut: Type[pydantic.BaseModel] = create_pydantic_model(
    table=ListItem, model_name="ListItemOut"
)

# Add nested path '/list/{list_id}/item' for ListItem CRUD operations
list_router.include_router(item_router, prefix="/{list_id:str}", tags=["List"])
app.include_router(list_router)
app.include_router(item_router)

# Start app

import uvicorn


uvicorn.run(app, reload=False)

Which i could not get to run because there is no way of providing an argument for {list_id} in the FastPIWrapper.

It looks somehow functional in the SwaggerAPI but it will crash as soon as we use one endpoint because of the missing argument:

[sinisaos]

@motey I'm sorry if I missed your point and if I don't understand your request well, but there is currently no support for M2M within Pydantic. You can try something like this to get M2M records in nested routes

@app.get("/list/{list_id}/items", tags=["List"])
async def list_get_items(list_id: int) -> List[ListItemOut]:
    """
    Get all ShoppingList items of the single ShoppingList row by ShoppingList.id
    Example Request URL: http://localhost:8000/list/1/items
    Result:
    [
      {
        "text_value": "ListItem 1",
        "checked": false
      },
      {
        "text_value": "ListItem 2",
        "checked": true
      }
    ]
    """
    try:
        data = await ShoppingList.objects().get(ShoppingList.id == list_id)
        results = await data.get_m2m(ShoppingList.items)
        return [i.to_dict() for i in results]
    except AttributeError:
        return []


@app.get("/list/{list_id}/items/{item_id}", tags=["List"])
async def list_get_item(list_id: int, item_id: int) -> ListItemOut:
    """
    Get single ShoppingList item by item_id
    Example Request URL: http://localhost:8000/list/1/items/1
    Result:
    {
        "text_value": "ListItem 1",
        "checked": false
    }
    """
    try:
        data = await ShoppingList.objects().get(ShoppingList.id == list_id)
        result = await data.get_m2m(ShoppingList.items)
        for i in result:
            if i.id == item_id:
                return i.to_dict()
    except AttributeError:
        return {}

[motey]

@sinisaos Thanks for the example and sorry if i wasnt precise enough. Maybe i mixed together 2 thoughts/questions here.
Lets leave M2M out of the equation and i'll try to focus my question on the nested path thing:

Your example makes great use of Picollo ORM and FastApi but does not use Piccolo API.
The thing i am asking myself if we can also achieve nested Paths and make use of Piccolo API. So having Piccolo default endpoints /list/{list_id}/items/ids, /list/{list_id}/items/new, /list/{list_id}/items/count,.../list/{list_id}/items/count/{row_id} without the need to implement them by hand.

[sinisaos]

@motey I don't think you can do something like that just using FastAPIWrapper (which is based on PiccoloCRUD). PiccoloCRUD use query params for filtering, pagination etc. As you already wrote you can use __visible fields (which can be nested), but that's not path params. For what you want, I think you must write your own endpoints like in example I wrote (same can be applied to Foreign keys also) because PiccoloCRUD has set of predefined routes for single Piccolo table.

[motey]

Understood. We currently can't supply arguments to the path except for {row_id} (check here:

piccolo_api/piccolo_api/crud/endpoints.py

Line 282 in 8b5cc86

Route(

). Do you know if this restriction aligns with Piccolo API's philosophy, or would it make sense to propose a PR to include this as a feature?

[sinisaos]

@motey You have to wait @dantownsend to propose a PR. PiccoloCRUD is intended for single Piccolo table. FK nesting is enabled with __visible_fields query parameter. FastAPIWrapper and PiccoloCRUD are also the building blocks for the Piccolo Admin backend, and all result filtering use query parameters. I think it's much easier to write your own endpoints for nested routing. It may take more time, but this gives you complete control over your DB queries.

[dantownsend]

@sinisaos Thanks for helping with this.

@motey I think I see what you're trying to do - add an extra route underneath a FastAPIWrapper. But these extra routes don't contain any of the special GET params which FastAPWrapper adds.

So in the second example, you mounted a FastAPIWrapper underneath another FastAPIWrapper, but this caused some weirdness due to URL params.

I can't really think of a perfect solution. I think mounting an endpoint under a FastAPIWrapper should be OK, but then you'll have to implement all of the GET param filtering by hand.

If you can find an elegant solution let us know.