Moor: How to store a object in Table moor ?

[tony123S]

How do we store this type of JSON to Moor?

{"id":123,"ABC":{"id":1,"name":"ONE"}}

We can implement column id like
TextColumn get id => text().named('id')();

But how about object ABC?

Thanks.

[simolus3]

You have multiple options here. They all have some pros and cons, so choosing the best one depends on what you want to do with the data.

1. You could create another table for the nested object, and only store the id in the first table. So something like

class Entries extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get abcId => integer(); // should reference an id from ABCs
}
class ABCs extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

From a relational viewpoint, I'd say that this is the cleanest approach. It works well if you want to run query on the abc table, like loading all entries with a specific name in the abc field. The downside is that queries are harder to write (you need to use joins) and inserts/updates/deletes become more tedious as well (you'll always need two of them). Another downside is that this works poorly if the abc object contains dynamic keys.

1. To make stuff a bit easier, you could put the properties from abc into the main table:

class Entries extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get abcId => integer();
  TextColumn get abcName => text()();
}

This can simplify queries, but you'll still run into problems for dynamic keys.

1. You can make queries much easier by using type converters, which will also work well for dynamic keys in the abc object.

import 'dart:convert';

class JsonConverter extends TypeConverter<Map<String, dynamic>, String> {
  const JsonConverter();

  Map<String, dynamic> mapToDart(String fromDb) => fromDb == null ? null : json.decode(fromDb);
  String mapToSql(Map<String, dynamic> dart) => json.encode(dart);
}

class Entries {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get abc => text().map(const JsonConverter())();
}

Here, the generated code for the entries class won't have a String abc field, it will have a Map<String, dynamic> abc field that contains the json data from abc. The downside with this approach is that querying for fields in the abc column is hard (so you can't filter entries by their name in abc), but if you only need to filter by their main id this can work really well.

I'll close this issue, but feel free to reply if you have any more questions.

[DevonJerothe]

the way I went about this was to encode the nested JSON object and store it into a TextColumn. then decode it when I get it back from the db. However, you won't be able to query any data within the nested JSON.

[tony123S]

Thanks guys for your response, but I not sure which options are actually work in my case.

Assume this is my JSON which returned from server
{"item_A":[{"id":"111","ABC":{"id":"1","name":"ONE"}}]}

In my bloc class, it will call repo class. From repo class, it will call service and finally service will return JSON output .

BLoc class

 Future getList() async {
    List<TableA> list;
    try {
      list = await _repository.getList();
      listSink.add(list);  // store list to sink
    } catch (e) {
      print('Caught $e');
    }
  }

Repo class (getList function)

 Response response;
 var list;

try {
      response = await _service.getList();   // get output as JSON 
      var response = Response.fromJson(response.body);

      for (var listItem in response.item_A) {
        try {
          await _dao.insert(
              TableA(id: listItem.id,
                     abc: ???? ));     // how to store object ABC ?
        } catch (e) {
          print(e.toString());
        }
      }
      list = await _dao.getList();     // after stored, get all list from database
      return list;  // return to bloc class

In my pageA, I have a listView, where it will get the listStream as snapshot.data. Once the row of item is pressed, it suppose to go to EditPageA and display the ABC id.

I hope my question is clear.

[simolus3]

Ok, so it looks like you're not querying for properties in the json. That makes it possible to use type converters. I'm assuming you either use json_serializable or built_value for ABC, right?
This let's you write something like this:

@DataClassName('TableA')
class FirstTable {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get abc => text().map(const AbcToSqlConverter())();
}

class AbcToSqlConverter extends TypeConverter<Abc, String> {
  const AbcToSqlConverter();

  Abc mapToDart(String fromDb) {
    if (fromDb == null) return null;
    return Abc.fromJson(fromDb);
  }

  String mapToSql(Abc abc) => abc.toJson();
}

You can then finish your method above:

await _dao.insert(TableA(id: listItem.id, abc: listItem.abc));

[tony123S]

So I need to create a Abc class like how TableA created?

[simolus3]

I assumed you already had an Abc class, but Abc shouldn't be a table. In the line var response = Response.fromJson(response.body);, what does Response.fromJson return? A Map<String, dynamic> or a Dart class you wrote?

[tony123S]

I get error here

ABC mapToDart(String fromDb) {
    if (fromDb == null) return null;
    return ABC.fromJson(fromDb);  // The argument type 'String' can't be assigned to the parameter type 'Map<String, dynamic>'.d
  }

Here my ABC class

import 'package:json_annotation/json_annotation.dart';
part 'abc.g.dart';

@JsonSerializable()
class ABC {
  ABC();
  var name ;
  var id ;

  factory ABC.fromJson(Map<String, dynamic> json) =>
      _$ABCFromJson(json);
  Map<String, dynamic> toJason() => _$ABCToJson(this);
}

[simolus3]

Ah, I see. Then just add a json to string wrapper in the encoder:

import 'dart:convert' show json;

class AbcToSqlConverter extends TypeConverter<Abc, String> {
  const AbcToSqlConverter();

  Abc mapToDart(String fromDb) {
    if (fromDb == null) return null;
    return Abc.fromJson(json.decode(fromDb));
  }

  String mapToSql(Abc abc) => json.encode(abc.toJson());
}

[tony123S]

Perfect! Thanks a lot

[biklas7]

@simolus3 Hi! I have a similar use case, what differs is that Abc is a moor generated DataClass. The problem that I'm having is that the generated TableA DataClass is assuming that Abc is dynamic instead of assigning the proper type.

[simolus3]

In Dart's build system, a builder can't see its own outputs. This means that moor can't see generated data classes without a special setup using build targets.

I'm wondering why you would store a row from one in table in another table though. You could store a reference to its primary key and then use joins instead. If you tell me more about your use case I can try to come up with something simpler.

[biklas7]

No, you're right. I was just being lazy and didn't want to lead with joins 😅 thanks for the answer anyway and for the awesome package!

[jotheeswari]

@simolus3 Hi, I tried lot but after type conversion i cannot insert data in moor database.

[tony123S]

@simolus3 Hi, I tried lot but after type conversion i cannot insert data in moor database.

Please post your code

[jotheeswari]

@tony1235 Hi,
Thanks for your response.I was done insertion i have trouble in deletion
part.

This is my typeconverter class:

class AbcToSqlConverter extends TypeConverter {
const AbcToSqlConverter();

UserResponse mapToDart(String fromDb) {
if (fromDb == null) return null;
return UserResponse.fromJson(json.decode(fromDb));

}
String mapToSql(UserResponse abc) => json.encode(abc.toJson());
}

Moor Database class:

class Users extends Table {
IntColumn get id => integer().autoIncrement()();
TextColumn get abc => text().map(const AbcToSqlConverter())();

}
Json serialization class:

import 'package:flutter/cupertino.dart';
import 'package:json_annotation/json_annotation.dart';
part 'user.g.dart';

@JsonSerializable()
class UserResponse {

String login;
int id;

UserResponse();

factory UserResponse.fromJson(Map json) =>
_$UserResponseFromJson(json);
Map toJson() => _$UserResponseToJson(this);

}

*Insertion *: _database.insert(User(abc: users[0]));

*Deletion *: _database.deleteUser(User(id: users[0].id,abc: users[0]));

I got this error in deletion,

• " I/flutter (12795): Moor: Sent DELETE FROM users WHERE id = ?; with
  args [1] E/SQLiteLog(12795): (1) no such column: id E/flutter (12795):
  [ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception:
  DatabaseException(no such column: id (code 1 SQLITE_ERROR): , while
  compiling: DELETE FROM users WHERE id = ?;) sql 'DELETE FROM
  users WHERE id = ?;' args [1]}"*

On Thu, Nov 26, 2020 at 4:12 PM Tony [e-mail] wrote:

@simolus3 https://github.com/simolus3 Hi, I tried lot but after type
conversion i cannot insert data in moor database.
Please post your code

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/simolus3/moor/issues/251#issuecomment-734221367, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AJ3XJJOHJMMWIVCIPFAFDN3SRYWHXANCNFSM4JQCAPIA
.

[simolus3]

Did you add the id column later? You'd have to write a migration in that case. If you haven't released your app yet, you can also try to clear your apps data locally and see if that helps.

[jotheeswari]

Yes correct now its working.Thanks for your response.

On Thu, Nov 26, 2020 at 9:15 PM Simon Binder [e-mail]
wrote:

Did you add the id column later? You'd have to write a migration in that
case. If you haven't released your app yet, you can also try to clear your
apps data locally and see if that helps.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/simolus3/moor/issues/251#issuecomment-734369261, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AJ3XJJMUA5E4JUMZGBE53QDSRZZZ7ANCNFSM4JQCAPIA
.