Moor: Filter duplicate Insertion

[KaungZawHtet]

Is there moor way to automatically filter duplicate data insertion , especially at moor files ?

[simolus3]

You could add a unique constraint and then use INSERT OR IGNORE INTO ... to avoid inserting data twice.

[KaungZawHtet]

Companion is cleaner way. Can I get like this INSERT OR IGNORE INTO user VALUES :userCompanion ?

[simolus3]

I mean you can write into(user).insert(userCompanion, mode: InsertMode.insertOrIgnore) in Dart. I can take a look at supporting companions inside moor files too, but it'll probably be some work.

[KaungZawHtet]

This is clean too. Thx for ur help. This issue can be closed now

[KaungZawHtet]

Wait! sadly this does not filter duplication here. How insertOrIgnore works?

[KaungZawHtet]

How can I detect the duplication by a column ? Example, detect email column of incoming data for filtering.

[simolus3]

How can I detect the duplication by a column

If you want that column to be unique, you can add a unique column constraint. That should work together with INSERT OR IGNORE.

Or do you want to allow duplicate values in general, but avoid them when inserting data in a batch?

[KaungZawHtet]

Cool if I can define table like this in moor file and InsertMode.insertOrIgnore automatically detect it

CREATE TABLE user (
  id INTEGER PRIMARY KEY NOT NULL,
  name TEXT,
  email TEXT,
  UNIQUE(id,email)
);

[simolus3]

The UNIQUE(id, email) key won't do much - id is the sole primary key, so it's unique on its own. You can just use a UNIQUE (email) constraint if you want them to be unique.

[knaeckeKami]

if you also insert the primary key (id), you can do that on the table level:

CREATE TABLE user (
  id INTEGER,
  name TEXT,
  email TEXT,
  primary key ("id") on conflict replace
);