Help with querying referenced fields

elena · December 14, 2018, 9:18pm

I need some help with a database query… Basically I have three collections, an “events” collection has a one to many relationship with both my “tags” collection, and my “categories” collection. I have a search box query running beautifully, but I just discovered that it doesn’t actually query my categories and tags reference fields. Help! I tried using an .include(), but it didn’t help. And, what’s more, I know you can’t use an include() with more than one multi reference field (both my reference fields are multi-select). I’m totally lost looking at trying the queryReferenced thing. This is what I have so far:

import wixData from ‘wix-data’;

$w.onReady( function () {
$w(“#searchedrep”).onItemReady(($item, itemData, index) => {
$item(“event”).text = itemData.relatedjob;
$item(“#description”).html = itemData.jobdescription.substring(0, 225) + ‘…’;
$item(“#thumbnail”).src = itemData.thumbnail;
$item(“#thumbnail”).link = itemData[‘link-EventPhotos-relatedjob’];
$w(“#thumbnail”).clickAction = “link”;
})
});

export function searchbutton_click_1(event) {
wixData.query(“EventPhotos”)
.include(“categories”)
.contains(“relatedjob”, $w(“#searchbox”).value)
.or(wixData.query(“EventPhotos”)
.contains(“jobdescription”, $w(“#searchbox”).value))
.or(wixData.query(“EventPhotos”)
.contains(“categories”, $w(“#searchbox”).value))
.or(wixData.query(“EventPhotos”)
.contains(“tags”, $w(“#searchbox”).value))

    .find() 
    .then((results) => { 
        $w("#searchedrep").data = results.items; 
        $w("#searchedrep").expand(); 
    });

}

@mike moynihan are you willing to help me out again?

mikemoynihan99 · December 15, 2018, 2:28pm

In your above code you are only querying 1 collection called " EventPhotos ". You are querying 3 field keys (columns) in that collection. If this is what you are indeed trying to do and you want to filter your repeater for the results then code something like this…

export function searchbutton_click(event, $w) {
//change to the correct ID for your dataset
$w(“#dataset1”).onReady(() => {

let SearchValue = $w(“#searchbox”).value;

        $w("#dataset1").setFilter(

//change field keys as required

            wixData.filter() 
            .contains("collectionFieldKey1", SearchValue) 
            .or( 
                wixData.filter() 
                .contains("collectionFieldKey2", SearchValue) 
                .or( 
                    wixData.filter() 
                    .contains("collectionFieldKey3", SearchValue) 
                ) 
            ) 
            ); 
        }) 
}

elena · December 17, 2018, 4:30pm

Hi Mike, that is what I’m trying to do, but I also want it to filter my results by my reference fields… So, for example, my “categories” field is a field in my “event photos” collection that references another collection with multiple categories. I just want the query to also be able to filter by those values, so that when a user types in a category, the results will show any “event photos” that belong in that category. Is that what the “set filter” function will do? The way I’ve tried it before it gives me an error telling me that my reference fields can’t be set to null or undefined.

Thank you!!

mikemoynihan99 · December 17, 2018, 4:56pm

@elena
have you tried it ?

it will filter based on the search word looking in all 3 field keys

elena · December 17, 2018, 5:09pm

@mikemoynihan99 Yep, I just did, it’s working to filter by my main database’s fields, but not by its reference fields.

mikemoynihan99 · December 17, 2018, 5:14pm

@elena
so your not just trying to filter one dataset which is connected to one collection ?

elena · December 17, 2018, 5:20pm

@mikemoynihan99 I suppose not? I’m sorry, I guess I’m just not understanding why it shouldn’t be able to be filtered by a reference field… I am just trying to show results from one dataset from one collection (I think), but I want to be able to filter those data points by a reference field. My “event photos” and “categories” are two separate collections, they have a many-to-many relationship, I just want users to be able to enter any search they wish, including terms that correspond to the related “categories,” and have it turn up results from my “event photos” collection.

elena · December 17, 2018, 5:24pm

mikemoynihan99 · December 17, 2018, 5:41pm

so with my above filter example you could for instance filter field keys relatedJob, eventDate, jobDescription.

as your dataset is only connected to " EventPhotos ", you can only filter the " EventPhotos " collection, i.e. if the " EventPhotos " collection has 1000 rows of data the max number of rows you can filter is 1000. You can not filter more than the 1000 rows.

If what you are wanting to do is return results from more than 1 collection code separate wixData.query for each collection and concatenate the results for each to make one list of results.

const allSearchResults= collection1Results.concat(collection2Results).concat(collection3Results)

elena · December 17, 2018, 5:46pm

Ohhhh, okay, I will go try that. Thanks very much for your help. I’m familiar with configuring interconnected databases, but not with code so much, so this is a steep learning curve for me.

I will go try it and let you know!

elena · December 17, 2018, 6:07pm

So, Mike, where do I need to insert the line combining all the results?

export function searchbutton_click_1(event) {
wixData.query(“EventPhotos”)
.contains(“relatedjob”, $w(“#searchbox”).value)
.or(wixData.query(“EventPhotos”)
.contains(“jobdescription”, $w(“#searchbox”).value))
wixData.query(“Categories”)
.contains(“categories”, $w(“#searchbox”).value)
.find()
.then((results) => {
$w(“#searchedrep”).data = results.items;
$w(“#searchedrep”).expand();
});
}

mikemoynihan99 · December 17, 2018, 6:24pm

@elena
do you get results from all 3 collections when you console.log(results)

elena · December 17, 2018, 6:28pm

@mikemoynihan99 I’m sure something in my syntax is off, I get no results.

items: […]length: 0totalCount: 0query: {…}Search BoxLine 23

elena · December 17, 2018, 6:31pm

Here’s the full log

{…}
items:
Array(0)

length:
0

totalCount:
0

query:
{…}

invalidArguments:
Array(0)

filterTree:
{…}

$and:
Array(1)

0:
{…}

categories:
{…}

$contains:
“corporate”

provider:
{…}

collectionName:
“Categories”

limitNumber:
50

included:
Array(0)

elena · December 17, 2018, 7:30pm

Well Mike, this is what I have so far… I can get it to display results from the “Event Photos” database, but not from “Categories.” Is there not a way to convert the reference field value into a string or plain text so that the query would work on it? It just seems so wild that there isn’t a way for it to easily query the reference field on the one database I want to see results from.

import wixData from ‘wix-data’;

$w.onReady( function () {
$w(“#searchedrep”).onItemReady(($item, itemData, index) => {
$item(“event”).text = itemData.relatedjob;
$item(“#description”).html = itemData.jobdescription.substring(0, 225) + ‘…’;
$item(“#thumbnail”).src = itemData.thumbnail;
$item(“#thumbnail”).link = itemData[‘link-EventPhotos-relatedjob’];
$w(“#thumbnail”).clickAction = “link”;
})
});
export function searchbutton_click_1(event) {
wixData.query(“EventPhotos”)
.contains(“relatedjob”, $w(“#searchbox”).value)
.find()
.then((results) => {
let ResultsPic = results.items;

        wixData.query("Categories") 
            .contains("#title", $w("#searchbox").value) 
            .find() 
            .then((results1) => {

let ResultsCat = results1.items;

let AllResults = $w(“#searchedrep”).data = ResultsPic.concat(ResultsCat);
$w(“#searchedrep”).expand();
console.log(AllResults);
});
});
}

mikemoynihan99 · December 17, 2018, 7:37pm

this is how to query multiple field keys in 2 collections and concatenate the results into one list…

import wixData from ‘wix-data’

$w.onReady( function () {

$w(‘#searchButton1’).onClick( function () {

wixData.query("firstCollectionName") 
  .contains("collectionFieldKey1", $w("#searchInput1").value) 
  .or(wixData.query("firstCollectionName") 
    .contains("collectionFieldKey1", $w("#searchInput1").value)) 
  .find() 
  .then((results1) => {

let results1Items = results1.items;
console.log(results1Items);

    wixData.query("secondCollectionName") 
      .contains("collectionFieldKey1", $w("#searchInput1").value) 
      .find() 
      .then((results2) => {

let results2Items = results2.items;
console.log(results2Items);

const allResults = results1Items.concat(results2Items);

        console.log(allResults); 

      }) 

  })

})
})

elena · December 17, 2018, 7:40pm

@mikemoynihan99 Thanks! But then how do I get them to display in the repeater the same? Because wouldn’t the results from the “Category” dataset not look like my “Event Photos” results?

mikemoynihan99 · December 17, 2018, 7:48pm

@elena

$w(" #repeater1 ").data = allResults;

elena · December 17, 2018, 7:56pm

elena · January 7, 2019, 9:16pm

@mikemoynihan99 I’m still struggling with this same issue… Can you please help me out?

Basically I’m just trying to make my referenced field be accessible in a query. I’ve tried using the include() function, the query referenced function, but nothing is working.

Topic		Replies	Views
Have not seen any tutorials on multiple item reference fields since their release. In particular setting up searches of these fields Ask the community code , question	4	169	January 14, 2019
How to return/filter by a multi reference field when querying Ask the community code , question	7	934	March 19, 2021
Query using a Multi-reference Field Ask the community code , question	3	3046	March 11, 2019
Search query with the help of multireference field Ask the community code , question	0	84	July 12, 2021
Using Include() function with multi-reference fields Ask the community code , question	8	1505	May 31, 2019

Help with querying referenced fields

Related topics