Add multiselect for M2M relationships in forms

[rolisz]

It would be really nice if the forms to add/edit items would have support to include a field that is in a many to many relationship. The existing UI for foreign key relationships would mostly work, except that it would have to support selecting multiple items.

[dantownsend]

I agree, this would be nice. It's definitely something we should add at some point.

The work around at the moment is to register the joining table with Piccolo Admin:

# Where GenreToBand is the joining table:
create_admin(tables=[Band, Genre, GenreToBand])

This means you can edit the M2M relationships.

However, it would be slicker if it was all on the same page.

[dantownsend]

M2M widgets usually look something like this (taken from here):

We could do something similar (unless we can think up a better design).

[sinisaos]

@dantownsend After a lot of experimentation I was able to use multiselect to add and edit m2m columns on a single page. I used the Vue-Multiselect library (I also tried Vue-multiselect-listbox but couldn't get it to work). Currently it is possible to add multiple m2m columns per table, reverse lookup in PiccoloCRUD for all rows and single row. Changes are required in all three Piccolo repositories. The smallest change is in Pydantic utils in Piccolo ORM, and the bigger in Piccolo API and Piccolo Admin. If you like this I can make PRs in all three repositories so you can see what it's all about and because this is a big task it would be good if you could help me with this.

Screencast.2023-03-26.21.02.55.mp4

[dantownsend]

@sinisaos It looks promising. What changes are required on the Piccolo / Piccolo API side?

With the widget, is that doing a search on the backend? Or are all of the options being pulled into memory at once?

I wonder if we can reuse the array widget somehow, which means we don't have to introduce another library?

[sinisaos]

@dantownsend In Piccolo ORM, the change is only in Pydantic utils. I set the format and list type for the M2M columns (so we can use it as an array in Piccolo Admin later). Here's the change.

# in utils/pydantic.py
for column in table._meta.m2m_relationships:  # type: ignore
    column_name = column._meta.name
    field = pydantic.Field(format="m2m", extra=extra, **params)
    columns[column_name] = (t.List[t.Any], field)

Vue-multiselect uses an Piccolo Admin array under the hood and everything is loaded into the Vuex store from the new endpoint http://localhost:8000/api/tables/table_name/m2m/. Since the upload in Github comments is limited to 25 MB, do you agree that I send you a zipped file (instead making PR-s) via WeTransfer to the email on your website. Inside the zip file are all 3 Piccolo repos with changes so you can try and see the changes. There are a lot of comments in the code that describe what I meant with those changes.

[dantownsend]

@sinisaos It's quite a big change, so probably best to do it in stages.

I wonder if it's best to add M2M support to create_pydantic_model, or just pass the m2m column names into schema_extra?

You can send me the code if you want via email, but it's easier to see the diffs in GitHub.

[sinisaos]

@sinisaos It's quite a big change, so probably best to do it in stages.

@dantownsend That's exactly why I wanted to email the changes so that you can see what they're all about (I think it's easier than opening PRs that may not be good at all, and to make things work we first have to make changes in Piccolo ORM, then in Piccolo API and finally in Piccolo Admin. I hope it makes sense why I wanted to email you the changes in the first place).
I hope some of these changes can be used, if anything. I sent the files to your email so you can check when you have time. Link expires April 4, 2023.

[sinisaos]

@dantownsend Also branches links if that's easier for you on Github.
Piccolo ORM - https://github.com/sinisaos/piccolo/tree/m2m_to_create_pydantic_model
Piccolo API - https://github.com/sinisaos/piccolo_api/tree/m2m_to_PiccoloCRUD
Piccolo Admin - https://github.com/sinisaos/piccolo_admin/tree/m2m_multiselect

[zindy]

Hello @sinisaos

This (the code you demoed in your screencast) is exactly what I need to build a list of ForeignKeys when adding new rows to one of my table. Do you still have the code stashed somewhere and would you mind sharing it again? Fantastic work in any case, super useful functionality.

Cheers,
Egor

[sinisaos]

@zindy Thanks for the kind words. Unfortunately, I don't have that code anywhere, but even if I had it, it wouldn't be valid because it was made for Piccolo versions before v1 (when Piccolo used Pydantic v1, and now it uses Pydantic v2). Also Piccolo Admin now uses Vue3, not Vue2. I see that vue-multiselect has a version for Vue3, so I'll try to do something similar. If I manage to make anything, I'll be happy to share it here.

[zindy]

@sinisaos thank you for the quick reply! I'm very new to all this and hadn't realised so much work had been done on Piccolo (-admin) in the last 6 months.

It makes sense that the old code wouldn't probably even load. But don't worry, I'm not blocked by the absence of this feature:

If I must encode some examination result as determined by say "a junior pathologist alone" vs "a senior pathologist" vs "a junior + AI tools", I'll just use two foreign keys, reviewer1 and reviewer2 and then do some kind of combination of reviewer1+reviewer2 on row creation. For now, that's more than enough. Multiselect will be "good to have" because it'll be a lot more flexible, but I'm not greedy.

Anyway, I'll let everyone know what works and what doesn't for my application (building a clinical database of a research project) and if there is anything I can help with or interesting code I've developed, I'll share it in relevant threads!

Cheers,
Egor

[sinisaos]

@zindy Somehow I managed to get the same functionality with the latest versions of Piccolo (I left some comments in the code so you can use this more easily). Here are all the branches.
Piccolo ORM - https://github.com/sinisaos/piccolo/tree/m2m_multiselect
Piccolo API - https://github.com/sinisaos/piccolo_api/tree/m2m_multiselect
Piccolo Admin - https://github.com/sinisaos/piccolo_admin/tree/m2m_multiselect
I must mention that I did not write the tests (because this is not a candidate for PR). Everything works, but probably there are hidden problems that I have not encountered. At least I hope it helps or helps you find your own solutions. You will need to patch locally on all three Piccolo installations. This will also not be the best solution if you have large datasets in M2M tables, as the data is loaded into Vuex store (memory) from a single endpoint without pagination or any limit. A much better solution would be to load the data from the backend in smaller chunks, as in the case of the FK select widget, but I'll leave that to someone else. Good luck with all of this. Cheers.