M2M field not showing in return result

[chad-szymarek]

I'm trying to get a many to many field to work, and for some reason it doesn't seem to be working.

Here are my tables-

class ActivitiesLearningactivity(Table):
    type_id = ForeignKey(references="ActivitiesActivitytype")
    title = Varchar(length=75)
    story = Text()
    development_guidance = Text()
    days = M2M(LazyTableReference("LearningActivityToDay", module_path=__name__))
    justification = Text()
    sync_id = UUID()
    

class ProgramsDay(Table):
    day_number = SmallInt()
    title = Varchar(length=50)
    description = Varchar(length=300)
    story = Text()
    week_id = ForeignKey(references="ProgramsWeek", on_delete=OnDelete.cascade)
    self_study = Boolean(default=False)
    from_time = Time()
    end_time = Time()

  
class LearningActivityToDay(Table):
    learning_activity = ForeignKey(ActivitiesLearningactivity)
    day = ForeignKey(ProgramsDay)

Here's my route-

@app.get(
    "/api/learningactivities",
    response_model=t.List[LearningActivityModelOut],
    tags=["Learning Activities"],
)
async def get_all_learning_activities():
    return await ActivitiesLearningactivity.select(
        ActivitiesLearningactivity.title,
        ActivitiesLearningactivity.days(),
    ).order_by(ActivitiesLearningactivity.id)

I'm trying to have the ActivitiesLearningactivities table, have a days m2m field with the ProgramsDay table. I made the joining table, LearningActivityToDay like how it was in the docs I think. But when I try to do my select statement, to show the days in the ActivitiesLearningactivity table, that column just doesn't show up at all. No errors or anything. So I'm not quite sure what I did wrong.

[dantownsend]

Strange. I created this demo script to make sure it works OK:

import asyncio
import random

from piccolo.columns import Varchar, LazyTableReference, ForeignKey
from piccolo.columns.m2m import M2M
from piccolo.engine import SQLiteEngine
from piccolo.table import Table, create_db_tables, drop_db_tables


DB = SQLiteEngine()


class Band(Table, db=DB):
    name = Varchar()
    genres = M2M(joining_table=LazyTableReference(table_class_name='BandToGenre', module_path=__name__))


class Genre(Table, db=DB):
    name = Varchar()


class BandToGenre(Table, db=DB):
    band = ForeignKey(Band)
    genre = ForeignKey(Genre)


async def main():
    await drop_db_tables(Band, Genre, BandToGenre)
    await create_db_tables(Band, Genre, BandToGenre)

    genres = []
    for genre_name in ['Rock', 'Country', 'Electronic']:
        genre = await Genre.objects().create(name=genre_name)
        genres.append(genre)

    for band_name in ['Band_1', 'Band_2', 'Band_3']:
        band = await Band.objects().create(name=band_name)
        await band.add_m2m(random.choice(genres), m2m=Band.genres)

    print(
        await Band.select(Band.name, Band.genres())
    )


if __name__ == '__main__':
    asyncio.run(main())

Is there definitely data in the joining table?

[chad-szymarek]

Alright, cool. I'll give that a try! Thank you!

[chad-szymarek]

LearningModel = create_pydantic_model(ActivitiesLearningactivity)

class CustomLearningModel(LearningModel):
    days: t.List[ProgramsDay]

@app.get(
    "/api/learningactivities",
    response_model=CustomLearningModel,
    tags=["Learning Activities"],
)
async def get_all_learning_activities():
    learning = await ActivitiesLearningactivity.select(
        ActivitiesLearningactivity.type_id,
        ActivitiesLearningactivity.title,
        ActivitiesLearningactivity.days(),
    ).order_by(ActivitiesLearningactivity.id)
    return learning

I'm probably using it wrong, but is this how you would subclass to add the m2m field? I assumed that my_m2m could be called whatever I wanted, and that SomeOtherModel was the model that I wanted to return in the m2m field. Though I wasn't sure if that's supposed to be the LearningActivityToDay table that got created for the m2m relationship, or what.

I still get an error with what I currently have
Invalid args for response field! Hint: check that class ProgramsDay(Table, tablename='programs_day') ... is a valid pydantic field type

I'm starting to get a bit out of my element here, and I really do appreciate the help you've provided thus far!

[dantownsend]

It's OK - I think you're almost there.

You can't use ProgramsDay directly, as that's the Piccolo Table. You need to do one of the following:

LearningModel = create_pydantic_model(ActivitiesLearningactivity)
ProgramsDayModel = create_pydantic_model(ProgramsDayModel)

class CustomLearningModel(LearningModel):
    days: List[ProgramsDayModel]

Or just manually specify the ProgramsDayModel:

LearningModel = create_pydantic_model(ActivitiesLearningactivity)

class ProgramsDayModel(BaseModel):
    title: str

class CustomLearningModel(LearningModel):
    days: List[ProgramsDayModel]

[chad-szymarek]

Thanks a ton for this, this has helped me get even closer. Still getting an error about Validation, but it's closer. The Schema is at least looking right in the swagger docs.

{
  "type_id": 0,
  "title": "string",
  "story": "string",
  "development_guidance": "string",
  "justification": "string",
  "sync_id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
  "days": [
    {
      "day_number": 0,
      "title": "string",
      "description": "string",
      "story": "string",
      "week_id": 0,
      "self_study": true,
      "from_time": "string",
      "end_time": "string"
    }
  ]
}

But still getting an error of:

pydantic.error_wrappers.ValidationError: 1 validation error for CustomLearningModel
response
  value is not a valid dict (type=type_error.dict)

Maybe I'll see something in the morning.

I did have another question though. The database I'm working with was brought over from another version of the project that was made with Django. Is there anything special that needs to be done for the many to many relationships because of that? Basically migrating stuff over to FastAPI and Piccolo. I did follow the docs for migrating from a django project. But just wanted to make sure there wasn't anything else that really needed to be done specifically for many to many fields.

[dantownsend]

The error must be because the data doesn't match the Pydantic model for some reason. Make sure the data is what you expect, and ProgramsDayModel has the correct fields.

I did have another question though. The database I'm working with was brought over from another version of the project that was made with Django. Is there anything special that needs to be done for the many to many relationships because of that? Basically migrating stuff over to FastAPI and Piccolo. I did follow the docs for migrating from a django project. But just wanted to make sure there wasn't anything else that really needed to be done specifically for many to many fields.

If using M2M with an existing Django database, it should work OK. Look at the name of the joining tables in the database, and make sure the Piccolo joining Table has the same tablename.

class LearningActivityToDay(Table, tablename='whatever_the_django_joining_table_is_called'):
    ...