[Solved] Code to check for duplicates in database and only displaying the newest on repeater (based on updated date)

Ask the community
,
1

Hello,

I have a repeater linked to a database and displaying the values.
The database itself could have duplicates, where only the new one should be displayed on the repeater (based on the newest updated date).

To prove that duplicates are given, I would like to check in 2 fields (columns in the database) for the value in parallel. If the 2 fields values of one row matches with with the 2 field values of other rows, then only the newest should be displayed on the repeater.

Does anyone has an Idea how the code could look like here?

Thanks
Cheers Albert

2

https://www.wix.com/velo/reference/wix-data/wixdataquery/distinct

Or maybe you will find here your answer…

3

Thx for the hint. I am trying to add the distinct function into my code, but unfortunantely it is not working as expected. It seems not to differenciate the Users anymore.

So my idea was to check at first that the current user is equal to the Owner in my database and this should be shown in the repeater. (that works fine with the find() function)

Next I want just to show the restaurants based on their names, which this user was at. But if the user was several times in the same restaurant, this specific should just be shown once.

In my code above it is now showing again all results, without checking for the User and without specific result of the restaurant.

4

Albert, this is going to take some extensive coding anyway that you choose to do it, but it is doable. Below is an example that isolates the duplicate entries utilizing the aggregate function, and then re-queries the collection to return all of the records with those emails, so a further process can easily find the newer record and display that. The newer record displays first in results2.

Perhaps, you could run a function like this on your data prior to loading the repeater and write to a field called “dupl” that would flag the older record. The repeater gathering query would then have a condition like:

 .ne("dupl",true).

Hopefully, these are some ideas that get you further down the road …

export async function FindDupls(){
    let having = wixData.filter().gt("count", 1);
    let results,results2;
    results = await   wixData.aggregate("collection1")
    .group("email")
    .count()
    .ascending("email")
    .having(having)
    .run();
    if (results) {
     console.log("results:", results);
     let emailArray = results.items.map(a => a.email);
     console.log("emailArray", emailArray);
     results2 = await wixData.query("collection1")
        .hasSome("email", emailArray)
        .ascending("email")
        .find();
        if (results2){
            console.log("results2", results2);
        }
    }
}
5

I must be honest, i did not invest much time to analyse your issue.
It was just the first what was on my mind.
But you always will be able to find a solution, if you really serach for it.

There are 100 of ways, how to solve a problem, with different methods.
Without to dive to deep into your issue, maybe this one also could be interesting for you…

Showing UNIQUE-VALUES is almost nothing else then remove dublicates, right?

As you can see, i also struggled with similar issues, but i have created my own QUERY-SYSTEM, which can do all the magic.

Good coding-results —> needs time.

Take your time and create your own solution.

Back to your topic…
As you can see, in this example, you do not need a → DISTINCT() <—, which means, that you can use FIND() again, right?

async functioncreate_UniqueDropdown(items,DBFIELDS,dropdown){
    console.log(items);
    constuniqueTitles=awaitgetUniqueTitles(items);
    console.log("Unique-Titles: ",uniqueTitles);
               
    $w(`#${dropdown}`).options=buildOptions(uniqueTitles);
               
  //console.log("OPTIONS: ", buildOptions(uniqueTitles));
  
   async functiongetUniqueTitles(items){             
     lettitlesOnly=awaititems.map(item=>item[DBFIELDS]);
     //console.log("Titles-Only: ", titlesOnly);
     return[...newSet(titlesOnly)];
   }
   functionbuildOptions(uniqueList){
     returnuniqueList.map(curr=>     
       {
         return{label:curr,value:curr};
       });
   }
}

Good luck!

6

Hi Anthony,

that is pretty close to what I need, but it gets even further.
It should not just group based on a value in one field, but in 2.
So if field value 1 and field value 2 (of one row) is equal to field value 1 and field value 2 (of other rows in my data collection) then choose the newest based on “created date”.

So my data collection looks like that:

754×136 23.9 KB

The first 4 rows have the same owner. 3 of those with the same location.
The repeater for this owner should only show 2 containers. (see below)
The last one has another owner and should not be shown. (which works - Code below)

Live it looks like that:

755×832 58.2 KB

So in this example the location for the first 3 is the same and also the Owner is the same. Only the Points are different. The newest is the one on top and I want the repeater only to show this one. (green) As the others are outdated.

The last one is also correct. I only have one in my database for this owner. (yellow)
If I would have more for the same owner & location I also just would want to show the newest .

The (red) is not given as it has another Owner. (which works fine)

Also the idea with flagging duplicates in an additional dupl field sounds interessting, but also here the code need to check for 2 fields (location & Owner), which makes it tricky to identify upfront.

Do you have a suggestion?
Thanks

If you are interested here is my Code to check for the correct User data:
import wixUsers from ‘wix-users’ ;
import wixData from ‘wix-data’ ;
$w . onReady ( function () {
$w ( ‘#PunkteKonto’ ). onReady (() => {

let  user  =  wixUsers.currentUser ; 
let  userId  =  user.id ; 

let  itemUser  =  $w ( '#PrivateMember' ). getCurrentItem (). _id ; 

if  ( userId  ===  itemUser ) { 
    $w ( "#listRepeater" ). onItemReady (( $item ,  itemData ,  index ) => { 
    $item ( "#Name" ). text  =  itemData.Name ; //(location) 
    $item ( "#Level" ). text  =  itemData.level ; 
    $item ( "#Punkte" ). text  =  itemData.punkte ; 
    $item ( "#image" ). src  =  itemData.Image ;

});
wixData . query ( ‘Konto’ )
. eq ( “copyOwner” , userId )
. find ()
. then (( results ) => {
if ( results.totalCount > 0 ) {
$w ( ‘#listRepeater’ ). data = results.items ;
}
else {
$w ( ‘#listRepeater’ ). hide ();
$w ( ‘#search’ ). hide ();
$w ( ‘#PunkteSeiten’ ). hide ();
}
})
. catch (( error ) => {
console . error ( error );
});

This works fine. I want to add the described condition to that code.

7

This sounds interesting, but I´m not sure if it fits to my goal explained below.
In my case a lot of fields wouldn´t be unique, only the date would be different. Thanks

8

Busy day today. It will be a while before I take a good look at it.

For now, I wanted to relate this: The group function takes multiple fields for the aggregate part of it:

.group("field1","field2")
9

So i tried to implement your suggested code to site and this is how it looks:
export async function FindDupls (){
let having = wixData . filter (). gt ( “count” , 1 );
let results,results2 ;
results = await wixData . aggregate ( “PunkteKonto” )
. group ( “copyOwner”,“location)” )
. count ()
. ascending ( “location” )
. having ( having )
. run ();
if ( results ) {
console . log ( “results:” , results );
let locationArray = results.items . map ( a => a.location );
console . log ( “location” , locationArray );
results2 = await wixData . query ( “PunkteKonto” )
. hasSome ( “location” , locationArray )
. ascending ( “location” )
. find ();
if ( results2 ){
console . log ( “results2” , results2 );
}
}
}

I added the code below the above mentioned code and it seems not to work.
The idea is to identify the currentUser first and display the locations he was at. If the same owner was more then once in the same location, it just shows the newest. (details in my last post)

Do I have to implement your suggest code into my code above or is something else incorrect?

As you can see I tried to group two fields (copyOwner&Location) to the code.

Thanks

10

When you say “it seems not to work”, are you basing it on examining the results of the aggregate and the query in the console?

There’s so much I don’t know about what you’re doing. My reason for posting was simply to introduce an approach for identifying the duplicates and showing the newest. The second query should return all of the records in that collection where owners were multiple times in one location, and that would include the newest one. Your code would have to loop through the results and figure out which ones are the older ones that you don’t want to show on the page in question.

I think that flagging the “old records” that you don’t want to show would be the way that I would choose to approach it because then when the page loads it’s just a simple matter of filtering those out based on that field’s value. If you do it that way, then the challenge is to figure out what point in your code would be the ideal place to fire the code that flags those records? If you can catch it at the point when you’re creating a new record for a particular owner at a particular location, that would be ideal. If you could somehow flag all the older records of that same owner at that same location at that point, that might be the easiest. Again, this is a suggestion based on not knowing everything involved.

11

Hi anthony,

yes I tested the code live and it did not filtered out the old once, just showed all in the repeater.
Flagging the old once upfront would really be a solution, but I don’t know how the code would look like to flag all old once with the same owner and location upfront.
Do you have an example of that?

Thank you

12

Albert, how are new records added to the PunkteKonto collection. Let’s start there. Are you using a dataset or the wixData API?

13

A very important question! :wink:

14

I use a Form the user needs to fill out to state his location. out of that I get the owner id as well. I send this data to googlesheets via zapier and there I use a pivot table to sum up all informations and points and send this to my PunkteKonto collection thru Zapier via Jason backend function. Whenever a new column is created or modfied in googlesheets, Zapier post the data to the wix database.

510×538 23 KB

I thought about solving this in googlesheets directly, but I use a pivot table there to sum up the Users points. So its only one row for the same CopyOwner & Location.

15

Have you considered using a hook? Specifically the Before Insert hook would allow you to get in there and flag the records already in the collection with the same CopyOwner and Location values before inserting the new record.

Of course, this would require you to first manually run a one-time routine on the collection to flag the “old records” to get it up-to-date. Make sense?

16

Hi Anthony thanks for your support I guess I owe you a mulled wine.
I checked your hook option, but meanwhile I found a easy solution to my problem. (this was missing on my first code: Array . from ( results.items . reduceRight (( m , t ) => m . set ( t.location , t ), new Map ()). values ()). reverse ()

The final code looks like that:
import wixUsers from ‘wix-users’ ;
import wixData from ‘wix-data’ ;
$w . onReady ( function () {
$w ( ‘#PunkteKonto’ ). onReady (() => {

    **let**  user  =  wixUsers.currentUser ; 
    **let**  userId  =  user.id ; 

    **let**  itemUser  =  $w ( '#PrivateMember' ). getCurrentItem (). _id ; 

    **if**  ( userId  ===  itemUser ) { 
        $w ( "#listRepeater" ). onItemReady (( $item ,  itemData ,  index ) => { 
            $item ( "#location" ). text  =  itemData.location ; 
            $item ( "#Level" ). text  =  itemData.level ; 
            $item ( "#Punkte" ). text  =  itemData.punkte ; 
            $item ( "#image" ). src  =  itemData.Image ; 
        }); 
        wixData . query ( 'PunkteKonto' ) 
            . eq ( "copyOwner" ,  userId ) 
            . find () 
            . then (( results ) => { 
                **if**  ( results.totalCount  >  0 ) { 
                    $w ( '#listRepeater' ). data  =   Array . **from** ( results.items . reduceRight (( m ,  t ) =>  m . **set** ( t.location ,  t ),  **new**  Map ()). values ()). reverse (); //This is the missing part!! 
                }  
            }) 
            . **catch** (( error ) => { 
                console . error ( error ); 
            });

});

17

Albert,

Congrats! So, this code manages to not return any of the “older” versions of that owner at that location? If so, it’s definitely a more elegant, straightforward solution to the task at hand.

Anthony

18

Yes, I tried it with different testUsers created by myself and added data to the database for everyone seperately with different locations.
The Code identifies who is the current Owner and makes sure, that no duplicates are displayed on the repeater based on the location.
With the reverse function the code makes also sure tho only take the newest data of the combined (copyOwner & Location).

But thanks for the support