title: how to save nested complex JSON data in a sqlite database on flutter
body: <p>I have <strong>nested complex JSON</strong> data.
  I made a model for them and I can serialize and deserialize.</p>
  
  <p>I read the document of flutter and examples of saving data in SQLite
  but all of the examples are for simple JSON data, not complex data.</p>
  
  <p>I created the two tables for this (order_table, order_item_table) and saves the order_id key in order_item_table</p>
  
  <p>But it's a little strange to query that gives me a nested map. </p>
  
  <p>How can I do it?</p>
  
  <pre><code>class Order {
    int id;
    int userId;
    OrderStatus status;
    List&lt;OrderItem&gt; orderItems;
    DateTime createdAt;
    DateTime updatedAt;
  
    Order({
      this.id,
      this.userId,
      this.status,
      this.orderItems,
      this.createdAt,
      this.updatedAt,
    });
  
    factory Order.fromJson(Map&lt;String, dynamic&gt; json) =&gt; new Order(
          id: json["id"],
          userId: json["user_id"],
          status: OrderStatusValues.map[json["status"]],
          orderItems: new List&lt;OrderItem&gt;.from(
              json["order_items"].map((x) =&gt; OrderItem.fromJson(x))),
          createdAt: DateTime.parse(json["created_at"]),
          updatedAt: DateTime.parse(json["updated_at"]),
        );
  
    Map&lt;String, dynamic&gt; toJson() =&gt; {
          "id": id,
          "user_id": userId,
          "status": OrderStatusValues.reverse[status],
          "order_items":
              new List&lt;dynamic&gt;.from(orderItems.map((x) =&gt; x.toJson())),
          "created_at": createdAt.toIso8601String(),
          "updated_at": updatedAt.toIso8601String(),
        };
  }
  
  class OrderItem {
    int id;
    int orderId;
    int productId;
    int qty;
    Product product;
    OrderItemStatus status;
    String comment;
    DateTime createdAt;
    DateTime updatedAt;
  
    OrderItem({
      this.id,
      this.orderId,
      this.productId,
      this.qty,
      this.product,
      this.status,
      this.comment,
      this.createdAt,
      this.updatedAt,
    });
  
    factory OrderItem.fromJson(Map&lt;String, dynamic&gt; json) =&gt; new OrderItem(
          id: json["id"],
          orderId: json["order_id"],
          productId: json["product_id"],
          qty: json["qty"],
          product: Product.fromJson(json['product']),
          status: orderItemStatusValues.map[json["status"]],
          comment: json["comment"] == null ? null : json["comment"],
          createdAt: DateTime.parse(json["created_at"]),
          updatedAt: DateTime.parse(json["updated_at"]),
        );
  
  
  
    Map&lt;String, dynamic&gt; toJson() =&gt; {
          "id": id,
          "order_id": orderId,
          "product_id": productId,
          "qty": qty,
          'product': product.toJson(),
          "status": orderItemStatusValues.reverse[status],
          "comment": comment == null ? null : comment,
          "created_at": createdAt.toIso8601String(),
          "updated_at": updatedAt.toIso8601String(),
        };
  }
  </code></pre>
  
  <p>I created the relational table like this:</p>
  
  <pre><code>Future&lt;Database&gt; getDatabase() async {
      var databasesPath = await getDatabasesPath();
      String path = join(databasesPath, 'stock.db');
  
      String sql = '''
      CREATE TABLE $_tableName (
        order_id INTEGER PRYMARY KEY,
        user_id INTEGER,
        order_status TEXT,
        created_at TEXT,
        updated_at TEXT
      )
      ''';
  
      String sql2 = '''
        CREATE TABLE $_order_item_table (
          order_item_id INTEGER PRYMARY KEY,
          order_id INTEGER,
          product_id INTEGER,
          qty INTEGER,
          order_item_status TEXT,
          comment TEXT,
          order_item_created_at TEXT,
          order_item_updated_at TEXT
        )
      ''';
      database = await openDatabase(path, version: 1,
          onCreate: (Database db, int version) async {
        await db.execute(sql);
        await db.execute(sql2);
      });
  
      return database;
    }
  </code></pre>