How to store a list of objects in sqflite?

Question

I want to use the sqflite package to store my objects. I read some articles, tutorials and examples about it. So far I understand everything. But none of them covers my use case:
I have a class named Foo. This class holds beside some primitive fields an object of the class Bar. Bar has only primitive fields. Additionally Foo has a list of objects of the type Baz. Baz has also only primitive fields.
class Foo {
  // simple fields

String id;

Bar bar;
  List<Baz> bazs;
}

class Bar {
  // only primitive fields
  
  String id;
}

class Baz {
  // only primitive fields

String id;
}

I want to connect the bar object with the foo object in the database and the same with the bazs objects. How do I do it? Whats the best way?
I'm not really experienced wit SQL. I know what SELECT, WHERE, ORDERBY and so on means but my knowledge does not get much further.
I found this qustion in Stack Overflow but I wonder if it is actually efficient to store all the values as a JSON. What happens if the JSON gets really big?
Currently my only idea is to store the id of the bar object as a field in the foo object or vica versa. foo could also hold a list of the ids of the bazs objects. Or each baz objects holds the id of the foo object. Is this a good solution?
Is there a possibility to let sqflite handle the relations between the objects?

Nour Shobier · Answer

You need to create 3 tables:

Foos:
[fields]:

id

Bars:
[fields]:

foo_id: a foreign key that points to the id field of Foo
bar_id: which is the bar's id

Bazs:
[fields]:

foo_id: a foreign key that points to the id field of Foo
baz_id: which is the baz's id

Now lets say that you want fetch a Foo record with and Id = 3 along with the bar and bazs

fetch the foo record:

SELECT * FROM foo WHERE id = 3;

fetch bar where foo_id = 3

SELECT bar_id
FROM bars
INNER JOIN Foo on B.id = bars.foo_id;

fetch bazs where foo_id = 3

SELECT baz_id
FROM bazs
INNER JOIN Foo on B.id = bazs.foo_id;

It would be easier if you use moor package to implement a sqllite DB

How to store a list of objects in sqflite?

One Answer

Add your own answers!

Ask a Question