Search best practice - export from moor db nested json for external REST API

[AlexKenbo]

INITIAL: We have 3 tables referring to each other.

RootTable - id
SecondTable - id, rootId
ThirdTable - id, secondId, thirdName

TARGET: We need to pass the nested json to the external API

{
    "root_id": 45,
    "seconds": [
        {
            "second_id": 1,
            "thirds": [
                {
                    "third_id": 1,
                    "third_name": "T-FIRST" 
                },
                {
                    "third_id": 2,
                    "third_name": "T-SECOND" 
                }
            ]
        },
        {
            "second_id": 2,
            "thirds": [
                {
                    "third_id": 3,
                    "third_name": "T-THIRD" 
                },
                {
                    "third_id": 4,
                    "third_name": "T-FOUR" 
                }
            ]
        }

    ]
}

Solution options

METHOD WITH OWN MODEL

1. Create a model for an external API from nested dataclasses (except for root)
2. We make requests to moore in creating instances for the external API (at least 2 cycles, 1 nested, if you need to get one root object)
3. Call the toJson method of the root object, which also calls toJson on the nested fields-objects Second, Third
4. We send the result from point 3 to the API

METHOD WITH TypeConverters
There are more questions than solutions. Since there was no practice with TypeConverters

1. Create a TypeConverter for 3 types, refer to moor table models?
2. It is also necessary to make 3 queries to the moore database. And create a 3-level object in loops (root> second> third)?
3. Call the toJson method of the root object, which also calls toJson on the nested fields-objects Second, Third
4. We send the result from point 3 to the API

ANOTHER WAY ABOUT WHICH YOU KNOW
...

[simolus3]

Type converters apply to individual columns, not to table relationships. If you want to use multiple tables, type converters are probably not the best approach here. I think I'd go into the direction of using an own model for the request. SQL can be a bit tricky when fetching nested data structures, but doing multiple selects isn't expensive with SQLite.
If you already have model classes for the request, you may be able to integrate them into moor by using custom row classes. But in general the "method with own model" suggestion you made sounds reasonable to me.

[AlexKenbo]

Type converters apply to individual columns, not to table relationships. If you want to use multiple tables, type converters are probably not the best approach here. I think I'd go into the direction of using an own model for the request. SQL can be a bit tricky when fetching nested data structures, but doing multiple selects isn't expensive with SQLite.
If you already have model classes for the request, you may be able to integrate them into moor by using custom row classes. But in general the "method with own model" suggestion you made sounds reasonable to me.

I found that the method with TypeConverters is inappropriate, as it is allowed to simply store my type as json in a table cell.
This will immediately prevent us from doing SQL to the data.

Yes, I, too, already see that Row Classe suits me more.

But here again you get mixed naming of fields - the same dataclass for moors and external api, it is possible, of course, to try using the JsonKey.

But then it will really be cleaner to have a separate model for the external API and pull out data from the moors with several requests to form it.

But I still study row classes.

Thanks for the quick response!

[simolus3]

One advantage of using row classes is that you're not bound to moor column names at all really. You can have something like this:

@UseRowClass(MyRow, constructor: 'fromDatabase')
class MyTable extends Table {
  TextColumn get myColumnName => text()();
}

class MyRow {
  final String foo;
  
  MyRow(this.foo):
  
  // moor column names only leak into this constructor
  factory MyRow.fromDatabase(String myColumnName): this(myColumnName);
}

As row classes are independent of moor, you can also use a more powerful serialization package like json_serializable or built_value.