Tortoise-orm: Can't I use .values() with relationships?

[mojimi]

Take the following example :

class Task(Model):
  title = fields.CharField(255)

class Actions(Model):
  title = fields.CharField(255)
  task = fields.ManyToManyField('models.Task', related_name='actions')

async def main():
  tasks = await Task.all().prefetch_related('actions').values('actions')

I'm trying to achieve the following structure:

[
    {
        "id" : 1,
        "title" : "Task 1",
        "actions" : [
            {
                "id" : 1,
                "title" : "Action 1"
            }
        ]
    }
]

But it gives me Selecting relation "actions" is not possible, select concrete field on related model

Is this intended? Do I need to use a serializer to be able to achieve this structure?

How would I choose to return only specific fields from actions if not using values?

[abondar]

Hi

Currently, values and values_list only support fetching concrete fields, so that result looks like plain table. Still, you can request field from related models like this

tasks = await Task.all().values('id', 'title', 'actions__id', 'actions__title')

Which will result in separate row for each related "action"

In your case, indeed using serializer with such query will be the way
tasks = await Task.all().prefetch_related('actions')

Currently we are not supporting excluding fields from fetched models, because we think it is not intuitive enough, to have model instance with not all fields accessible (because of async enviroment we can't autoload requested fields, like django does when you access field that was excluded from query)

I think we will investigate possibility to add support for nested objects in values in future, right now I'll mark this issue as feature request

[mojimi]

Thanks,

In the end a to_json method would be sufficient for my case

[abondar]

Do you mean to_json method for model instances, that will serialise all fetched info into dict?

[mojimi]

I think so, but in my case it would be for a executed queryset since I'm looking for a list of model instances

[grigi]

Would ’.values('actions__name')’ work? I have done test cases like that.

[mojimi]

@grigi When you use that instead of adding a list of actions it just adds the name of one action, I think it's the first one

[abondar]

I have idea that it would be quite neat to have such api

ojb.values('name', Prefetch("childs", queryset=Child.all().values('id', 'name')))

That would result in such dict

[{
  'name': 'Res 1',
  'childs': [{
    'id': 1,
    'name': 'John'
  }]
}]

@grigi What do you think about such design?

[grigi]

Could we not make it use kwargs?
E.g. values('name', childs=Childs.all().values(...)) ?
And if you pass a queryset it would auto prefetch?

This would implement a useable deserialisation step :thinking:
And be performant

(Generally in favour)

[grigi]

Some of the groundwork for this is in #260 as the request here is basically a SerDes framework.
Possibly expose this though a .values_nested() method that works very similarly, but only for reads.

[grigi]

Can be done via the Pydantic serialisation support we have since v0.16.0
Closing this as equivalently to done.