Query not finding records using reference field

Ask the community
,
1

I created 4 datasets with relationships to each other
userProfile has a user-reference-id that is referenced by userActivity
userActivity has a field “title” that references the activity field on the dataset Activity
Activity has an activityGroup that references the “title” field on the dataset activtyTitles
There should only be on userProfile record per user (one to one)
There may be multiple userActivity records per user (one to many)
There will only be one Activity record per userActivity, but there will be multiple userActivity records per Activity (one to one, one to many)
There will only be one activityTitle record per Activity record, but multiple Acctivity records per activityTitle

I’m calling a function to find out what types of activities a user is participating in

I’m passing the user-reference-id to the function and bypassing reading the userProfile record
I’m trying to use the user-reference-id in my first query to find all the userActivity records for a given user, but I’m getting zero records returned. There are 4 records for the given user in both my test and production datasets. (In my debugging efforts I ran the query without the .eq on the user-reference-id and I did get all my userActivity records returned, for all my users.)

the user-reference-id on the userProfile dataset is defined as a text field;
the refId field on userActivity is a reference field tied to the user-reference-id on userProfile
and userRefId has a value of “1”
Is there some rule about not being able to use a reference field in an .eq option?

I’m hoping this is just a syntax problem and not something that can’t be done.
HELP!

My function code is below:

export function checkActivityType(userEmail, userRefId) {

wixData.query(“userActivity”)
.ascending(“refld”, “endDate”)
.eq(“refld”, userRefId)
.find()
.then( (uAresults) => {
uAresults.items.forEach( (item) => {
activity = item.activity;
wixData.query(“activities”)
.ascending(“title”)
.eq(“title”, activity)
.find()
.then( (Aresults) => {
let itemsActivity = Aresults.items;
let itemActivity = itemsActivity[0];
let activityGroup = itemActivity.activityGroup;

 	      wixData.query("activitiyTitles") 
               .ascending("title") 
               .eq("title", activityGroup) 
               .find()  
	   .then( (results) => { 
		    let itemsGroup = results.items; 
	            let itemGroup = itemsGroup[0]; 
                        let activityAhc = itemGroup.ahc; 
                        let activityAes = itemGroup.aes; 
                        let activityTitle = itemGroup.title; 
            if (activityTitle === "Staff") 
           	    {admin = "yes";}  
	        if (activityAhc === true) 
    	    {ahc = "yes";} 
       	 if (activityAes === true) 
                {aes = "yes";}   
         
 	   		     } ); // end then activityTitle query       
        
  	        	 } ); // end then activities query      
        
        } );  //  end uAresults loop       

  } ); // end then userActivity query      

  
  var resultsArray = [ahc, aes, admin]; 
  return resultsArray;

} // end function

2

@webmaster-5 were you ever able to figure this out? I’m struggling with the same issue and I cannot find a solution anywhere. Please let me know! Thanks so much.

3

Having the same issue. Any input would help. Thanks!

4

I figured out a workaround… I’m guessing that the reference field doesn’t actually translate to a text value, so that’s why using something like .eq(“ref”,“match this”) doesn’t work.

Instead, what I did was, I made a reference field in the original database that references itself. Then I used the results of that field in the .eq().

In your question, you say you have a database called userProfile with a primary field of user-reference-id. Then you have a database called userActivity with a reference field called refId referencing user-reference-id in userProfile.

My workaround would involve making a reference field in userProfile. Let’s call it selfId. Then, set this reference field to reference userProfile, and set the value to match itself for each entry.

Now, you’ll have to query the userProfile database first, grab the value of selfId, and use that value to query userActivity.

Here’s a swing at what the beginning of your code might look like:

export function checkActivityType(userEmail, userRefId) {
wixData.query(“userProfile”)
.eq(“user-reference-id”,userRefId)
.find()
.then( (uPresults) => {
wixData.query(“userActivity”)
.ascending(“refld”, “endDate”)
.eq(“refld”, uPresults.selfId)
.find()
.then( (uAresults) => {
…

Hope that helps! Worked for me, at least.

5

Thanks for responding! I eventually just set up a hook to copy the reference field into a rich text field and then included that field in my query. It seems so silly that there isn’t a built in way to query reference fields!

6

@elena Actually, I realized that the value that the reference field holds is the id of the referenced entry. This means that you just have to match the reference field with the _id field of the referenced database.