How to remove the duplicates?

[Niroop4444]

I'm trying to remove the duplicate values from the database by using the delete method where-in I'm comparing the values of the ones in the database and the ones which are being inserted but both the original and the duplicate is getting deleted. I did try the upsertAll method also but getting an error as type 'Future' used in the 'for' loop must implement iterable.

[hhtokpinar]

Hi @Niroop4444 Have you solved it yet? You can try to select a grouped list on the table without filtering using that groupBy(...all fields of the table...) method and add them to a new table

[NiroopNife]

Hello @hhtokpinar, what i did was, i made the items in the list to be seleted individually through the FOR loop, then i just found if any duplicate is present of not by using

var dup = await Article().select().articleNumber.contains(articles[i]).toSingle(); //Finding the duplicates
   if (dup != null) {
       print(articleNumber + "is already present");
       return null;
   } else {
       print('add to db');
   }

Was my approach a right way? Or is there any much simpler way of acheiving it?

[hhtokpinar]

Why don’t you set the articleNumber field as a primaryKey? So it doesn’t allow duplicate articles. I guess It’s the best way for you

[NiroopNife]

Yeah thats the best way, but the parameters for that field are just PrimaryKeyType.integer_auto_incremental, PrimaryKeyType.integer_unique & PrimaryKeyType.text. So how to get make the articleNumber field as primaryKey?

[hhtokpinar]

Could you send a sample row as a json format?

[NiroopNife]

Yes certainly, this is how I'm saving the details to DB

const tableArticles = SqfEntityTable(
    tableName: 'articles',
    primaryKeyName: 'articleId',
    primaryKeyType: PrimaryKeyType.integer_auto_incremental,
    useSoftDeleting: true,
    fields: [
      SqfEntityField('articleNumber', DbType.text),
      SqfEntityField('remarkTime', DbType.text),
      SqfEntityField('currentLocation', DbType.text),
      SqfEntityField('deliveryStatus', DbType.text),
      SqfEntityField('addresseeType', DbType.text),
      SqfEntityField('deliveredTo', DbType.text),
      SqfEntityField('notDeliveredRemark', DbType.text),
    ]);

& this is how its getting saved

[{articleId: 1, articleNumber: RK591158945IN, remarkTime: 05-02-2021 12:54:11, currentLocation: 12.3069  76.6726, deliveryStatus: Delivered, addresseeType: Addressee, deliveredTo: hjghbhb, notDeliveredRemark: , isDeleted: 0}]

[hhtokpinar]

Ok. you should define it like this:

const tableArticles = SqfEntityTable(
    tableName: 'articles',
    primaryKeyName: 'articleNumber',
    primaryKeyType: PrimaryKeyType.text,
    useSoftDeleting: true,
    fields: [
      SqfEntityField('remarkTime', DbType.text),
      SqfEntityField('currentLocation', DbType.text),
      SqfEntityField('deliveryStatus', DbType.text),
      SqfEntityField('addresseeType', DbType.text),
      SqfEntityField('deliveredTo', DbType.text),
      SqfEntityField('notDeliveredRemark', DbType.text),
    ]);

inserting a new record:

final article = Articles();
article.articleNumber: 'RK591158945IN';
//  set the other values
await article.save();

and check saveResult if it's already exist

if(!article.saveResult.success)
{
// return some error message
}

to make changes on existing record:

final article = await Articles().getById('RK591158945IN');
// TO DO make some changes
article.save();

[NiroopNife]

This was too helpful, thank you very much

[hhtokpinar]

You're welcome...

[hhtokpinar]

or you can also try this way:

final article = await Articles().getById('RK591158945IN');
// create a new one if not exist
if(article == null) 
{ 
  article = Articles();
  article.articleNumber: 'RK591158945IN';}
}
  // set values
article.save();