Filtering Data based on ReferenceField

Ask the community
,
1

Hi All,
I have a table [table1] on my page.
I also have collection called “client-records” and associated Dataset on the page.
The collection has a reference field called relatedIUser that refers to the Privatemembers Data table.

The OWNER of these items is Me. But I would like to filter the table results so that the table only displays the rows where the logged-in-user matches the relatedUser field.

I have the code below but am not getting anywhere. Any help would be appreciated.

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

//Populate the table
async function UserNotesQuery() {
 
//Find logged on user's ID
let user = wixUsers. currentUser

let userID = user. id;
let isLoggedIn = user. loggedIn

    wixData.query("client-records")
 .include("relatedUser")
 .eq("relatedUser", userID)
 .find()
 .then((results) => {
 if (results. items.length > 0) {
                console. log(results);
 let sessionrows = results. items;
                $w("#table1") .rows = sessionrows;
 } else {
                console. log ("No Results Found.");

 }
 })
}

$w. onReady(UserNotesQuery)
2

NOTE: had to add unnecessary spaces because the system kept flagging one of the “.” items as a URL.

3

I can see some errors in this code and I’m going to try to clear it up.

4

First, you don’t need to user .include() or .queryReferenced() if you do not intend to use the data inside the reference item in the referenced collection, if it is just a filter by the field (reference field or not), you can use .eq().

This would work, I guess, cause I have not tested yet.

import wixUsers from "wix-users"
import wixData from "wix-data"

let user = wixUsers.currentUser
let userId = user.id
let isLoggedIn = user.loggedIn

$w.onReady(async () => {

 let table = $w("#table1") //Change this to your table element ID.

 let filterDataRelatedUser = await queryDataRelatedUser(userId)

 if (filterDataRelatedUser.length > 0) {
        table.rows = filterDataRelatedUser
 } else {
        console.log("No related user found!")
 }
})

async function queryDataRelatedUser(userId) {
 let results = wixData.query("client-records").eq("relatedUser", userId).find()
 return results.items
}
5

@bwprado thank you very much for your reply.

The docs on .include() are strange. They suggest that reference fields are not returned in a query unless you include them. I had originally left that out but upon viewing the ITEMS output I noticed the field was missing both with and without the .include().

I’ll give this a shot and let you know. Thank you again.

6

@bwprado thank you very much for your reply.

The docs on .include() are strange. They suggest that reference fields are not returned in a query unless you include them. I had originally left that out but upon viewing the ITEMS output I noticed the field was missing both with and without the .include().

I’ll give this a shot and let you know. Thank you again.

7

Hi Bruno,
I am receiving an error at the end of your code stating that Items is not valid for the type.

8

Yeah, I got the same error, it seems that the Editor was updated and no longer accept this ASYNC/AWAIT syntax, so here is the old way:

function queryDataRelatedUser(userId) {
 wixData.query("client-records")
 .eq("relatedUser", userId)
 .find()
 .then(results => {
 return results.items
 })
}

Just replace the last function and put this one instead.

9

This code is going to work, also:

import wixData from "wix-data"

let user = wixUsers.currentUser
let userId = user.id
let isLoggedIn = user.loggedIn

$w.onReady(async () => {

 let table = $w("#table1") //Change this to your table element ID.

 const filter = await queryDataRelatedUser(userId)
 const filterdDataRelatedUser = filter.items

 if (filterdDataRelatedUser.length > 0) {
        table.rows = filterdDataRelatedUser
 } else {
        console.log("No related user found!")
 }
})

function queryDataRelatedUser(userId) {
 const results = wixData.query("client-records")
 .eq("relatedUser", userId)
 .find()
 return results
}
10

Bruno that worked perfectly. Thank you very much for you help!

11

I did realize that if the user has NO notes it shows everyone’s notes. Working on addressing this and will update if anyone else needs to use this code.

12

FOR ANYONE LOOKING TO COPY THIS CODE … I had to add a line to Bruno’s Accepted answer (very much appreciated!) For some reason javascript returns all results if there is no matching rows.




import wixUsers from "wix-users"
import wixData from "wix-data"

let user = wixUsers.currentUser
let userId = user. id
let isLoggedIn = user. loggedIn

$w.onReady(async () => {

 let table = $w("#table1") //Change this to your table element ID.

 const filter = await queryDataRelatedUser(userId)
 const filterdDataRelatedUser = filter. items

 if (filterdDataRelatedUser. length > 0) {
        table.rows = filterdDataRelatedUser
 } else {
        table.rows = null
        console.log("No related user found!")
 }
})

function queryDataRelatedUser(userId) {
 const results = wixData. query("client-records")
 .eq("relatedUser", userId)
 .find()
 return results
}