Merge database entries per userID?

Ask the community
, , ,
1

Hey all,
I’m essentially trying to create an online course with dynamic pages (database name expData ) where users click a button to show they have completed a lesson. When they press the button, I get their userID, name, and what dynamic lesson page they’re on, and store it in a new database ( progressEXP ).

The code I have works, but when the user completes lessons on multiple pages, a new row is created in the database, even though the userID is the same.

1934×390 49.4 KB

What I want to happen is for there to be only one row per userID, so for this example this user would have one row with 4 items in the “status” column.

My code is below, any thoughts?

export function statusBtn1_onClick(event) {
    $w('#statusBtn1').changeState("sectionComplete");

    wixData.query("Members/PrivateMembersData")
        .eq("_id", wixUsers.currentUser.id)
        .find()
        .then((results) => {
            // get member info
            let memberFirstName = results.items[0].firstName;
            let memberLastName = results.items[0].lastName;
            console.log(memberFirstName, memberLastName);

            const user = wixUsers.currentUser;
            const userId = user.id;
            
            // get the dynamic page location
            let currentItem = $w("#expData").getCurrentItem();
            let section1Complete = [currentItem.title];
            
            //what to save
            let toSave = {
                "memberID": userId,
                "firstName": memberFirstName,
                "lastName": memberLastName,
                "status": section1Complete,
            };
            
            //store results in "progressEXP" database
            wixData.save("progressEXP", toSave)
                .then((results) => {
                    let item = results;
                    console.log("You did it");
                })
                .catch((err) => { let errorMsg = err;
                });
        })
}
2

Update I’ve tried using save(), insert(), and update() to get the info into the database, and all have the same result of creating multiple entries instead of one per user, so I’m guessing I have something else wrong.

3

Okay I figured out part of the problem. After reading the API I found that .save() only replaces items with the same “_id” field, so I deleted my “memberID” field and made the “_id” correspond with the current user ID.
Now there is only one entry per user.
Unfortunately, it essentially overwrites the entry, so if a user completes multiple lessons I can only see the most recent one (the “status” field).

1936×258 33.3 KB

Any tips on how to add multiple tags to this field instead of replacing?

Current code below:

import wixData from 'wix-data';
import wixUsers from 'wix-users';

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

export function statusBtn1_onClick(event) {
    $w('#statusBtn1').changeState("sectionComplete");

    wixData.query("Members/PrivateMembersData")
        .eq("_id", wixUsers.currentUser.id)
        .find()
        .then(r => { // get member info
            let memberFirstName = r.items[0].firstName;
            let memberLastName = r.items[0].lastName;

            // get the dynamic page location
            let currentItem = $w("#expData").getCurrentItem();
            let section1Complete = [currentItem.title + " L.1"];

            //what to save
            let toSave = {
                "_id": userId,
                "firstName": memberFirstName,
                "lastName": memberLastName,
                "status": section1Complete,
            };
            //store results in "progressEXP" database
            wixData.save("progressEXP", toSave)
                .then((results) => {
                    console.log(currentItem.title + " completed");
                })
                .catch((err) => {
                });
        })
}
4

You have a TAG-Field (status).
Must be an Array-Field or something similar, for example an OBJECT-Field right?

Now look onto your CODE…(what’s wrong here?)

let section1Complete=[currentItem.title+" L.1"];

It is not surprising that you get the following as RESULT…


You surely wanted to do the following…

let section1Complete=[currentItem.title, "L.1", "anotherValue", "oneMoreValue"];

This should normaly work.
If everything works like i expect it, you normaly should get separated values inside the TAG-FIELD.

If not, then you probably also could get one VALUE with all the values of the array as one STRING (but i think this will not happen).

Try it out.

To make it the right way…

FIRST → populate your defined ARRAY with all the needed/wanted VALUES with a for loop or another loop…

Something like this one…

let section1Complete = []
for (var i...and so on..){
	section1Complete.push(values[i])
}

After you have populated your Array with all the needed VALUES…

let section1Complete = ["Value1", "Value2", "Value3"]

Now you should be ready to continue …:sweat_smile:

BTW: This part of your code also could be better (at current time not very dynamic…)

let toSave ={
	"memberID": userId,
	"firstName": memberFirstName,
	"lastName": memberLastName,
	"status": section1Complete
};

You normaly don’t have to do that, when you allready have done your DB-Query.

let myItems = results.items ---> DO CONSOLE_LOG THE RESULTS !

myItems.title = "define here new title"
myItems._id = define here your new ID

Now comes the TRICK! (Attention!)
After you have changed the values inside your given RESULTS(ITEMS), you simply can save them back to DB…

wixData.save("Collection", myItems) ----> VOILA and it will be saved with new VALUES!

Or if you want to populate your OBJECT …

let myObject = {}

myObject["title"]="define here new title"
myObject["_id"]="define here new ID"

And now try to think some steps further and put this maybe into a loop ?:roll_eyes:

5

@russian-dima Thanks for your help! I think I followed your instructions but may have done something incorrectly because it is ALMOST working but not quite… Here’s an update, I’m definitely getting closer!

When someone presses the submit lesson button on a dynamic page (ex. Item1):

  • Start by querying the “Members” data to get the current user info
  • Then query the “progressEXP” database to see if the user has already completed any lessons (if there is a value in the “completedLessons” field)
  • If they have not completed any lessons, then add an item based on the current dynamic page they are on [“Item1”]
  • If they have completed lessons, find what those items are, and add them along with the item for the dynamic page they are on [“Item1”, “Item2”]

This last step ALMOST works, but does not store the array as intended.
Instead of [“Item1”, “Item2”, “Item3”] it makes it like [“Item1”, [“Item2”, “Item3”]] which I’m not sure how to fix…

2194×268 25.6 KB

Am I thinking of this incorrectly?

export function statusBtn1_onClick(event) {
    $w('#statusBtn1').changeState("sectionComplete");

    wixData.query("Members/PrivateMembersData")
        .eq("_id", wixUsers.currentUser.id)
        .find()
        .then(m => { // get member info
            let memberFirstName = m.items[0].firstName;
            let memberLastName = m.items[0].lastName;

            wixData.query("progressEXP")
                .eq("_id", wixUsers.currentUser.id)
                .find()
                .then(r => {
                    if (r.items.length > 0) {
                        // if there IS a value in "completedLessons"
                        // find what lessons are completed
                        let allCompleted = r.items[0].completedLessons;
                        console.log(allCompleted);
                        // find the current lesson
                        let currentItem = $w("#expData").getCurrentItem();
                        // current lesson with previous lessons into an array
                        let section1Complete = [currentItem.title, allCompleted];
                        console.log(section1Complete)

                        //what to save
                        let toSave = {
                            "_id": userId,
                            "firstName": memberFirstName,
                            "lastName": memberLastName,
                            "completedLessons": section1Complete,
                        };

                        //store results in "progressEXP" database
                        wixData.save("progressEXP", toSave)
                            .then((results) => {
                                console.log(currentItem.title + " completed");
                            })
                            .catch((err) => {});
                    } else {
                        // if there is NOT a value in "completedLessons"
                        // get the dynamic page location
                        let currentItem = $w("#expData").getCurrentItem();
                        let section1Complete = [currentItem.title];
                        console.log("Completing " + currentItem.title)
                        //what to save
                        let toSave = {
                            "_id": userId,
                            "firstName": memberFirstName,
                            "lastName": memberLastName,
                            "completedLessons": section1Complete,
                        };
                        //store results in "progressEXP" database
                        wixData.save("progressEXP", toSave)
                            .then((results) => {
                                console.log(currentItem.title + " completed");
                            })
                            .catch((err) => {});
                    }
                })
        })
}
6

I will take a look tomorrow at it if i will find enough time (already working on several projects → time is very barely at moment), but what you can do in the meanwhile, take a look onto the following site…

https://www.media-junkie.com/pflegeservice

I was working once with FILTERING-ENGINES and SELECTION-TAGS + TAG-Fields in DATABASES, where i also have had issues with TAG-FIELDS + SELECTIO-TAGS + CHECK-BOX-GROUPS, which are very similar in behaviour, related to → TAG-FIELDS in DB.

If you have enough time, read all the posts (you will find a lot of connected posts to this interactive-example) There should be also one or two posts related to selection-tags if i can remember it right. There you should be able to find more informations about how to work with SELECTION-TAGS / CHECK-BOX-GROUPS in relation to —> TAG-FIELDS in your DATABASE.

I know you read a lot, so take a closer look onto the given stuff, could be very informative for you. You can start with the ORIGINAL-POST (where everything has begun). :wink:

7

Ok the bell was ringing, i think you are already reading. :grin:

8

@russian-dima I was able to get the functionality I want my switching from a tags field to a text field, and it now updates with all a user’s completed lessons. Thanks for your responses, you pushed me in a good direction.

9

@lmeyer No problem! I knew, that you will be able to find your solution by your own.