How can I join linked datasets with one query

EDITING POST TO PROVIDE MORE CONTEXT:

I have two different databases that store information about different promotional offers.

The first is a database called Experiences which contains all the experiences available and contains location information for each experience (City, State, Etc.). This Database’s Primary Field is the Experience Name (labeled Title with an id of title)

The second database is called Experience Tracking and it contains a reference field that points back to the Experience database. This database includes a counter that increases when users redeem offers for different experiences.

What I am trying to do is create a query string that can pass a location into the Experiences database and filter the returned experiences by the most popular (Joining the Experience Tracking database and ordering by viewCounter in descending order).

I have the two databases imported into my page with the following settings

Experiences:


Experience Tracking:

I am attempting to filter the items using the following Query string:

wixData.query("Experiences").contains("state", resort.state).include("experience").descending("viewCounter").limit(2).find().then(returned => {
        console.log(returned)
    })

Whenever I load the page I get the following error logged to the console:

Included experience field does not exist in schema

I have tried to change the include statement to point to the actual Experience Tracking database instead of the reference field. Doing so gives me the same error except experience is replaced with Experience Tracking.

I have read through the following recourses thoroughly and I am still not sure why this is not working.
Adding record with a reference field | Velo by Wix

Displaying Content from Multiple Database Collections Using Reference Fields | Help Center | Wix.com

include - Velo API Reference - Wix.com

Velo: Querying Items that Reference Other Items | Help Center | Wix.com

queryReferenced - Velo API Reference - Wix.com

Please help. What I want to accomplish should be easy enough. I am not sure why I can not figure this out.

You can easily do that if you have a reference field on the Promotional Offers Collection that references the Redeem Counter. Just add an .include() method to the query on the Promotional Offers Collection.

If not, you can do two queries, like so:

import wixData from 'wix-data'

const getOffersByState = async (state) => await wixData.query('Offers')
.eq('state', state).find()

const getPromosByOffersIDs = async (id) => await wixData.query('Redeemed').hasSome('_id', id).find()

$w.onReady(async () => {
    const { items: offersByNY } = await getOffersByState('NY')
    const { items: redeemedPromos } = await getPromosByOffersIDs(offersbyNY.offerID)
}

So with the reference field to return the top 4 most viewed items it would be:

wixData.query("Experiences").contains("state", state).include("Title", "Experience").decending("viewCounter").limit(4).find()

where Title is the main reference field of the Experiences database and Experience is the reference field of the tracking database correct?

@amotor Ok, now you got me confused. Include is there to get the data from the referenced item in a different collection.

Would you care to show us how your collections are setup?

The .include() method only accepts one parameter, it is the field with the reference.

@bwprado You are right. I was confused. I was using the second example from the documentation but that assumed that there were two collections referencing a field in the collection we would be querying. I think that I can just use the following:

wixData.query("Experiences").contains("state", state).include("Experience Tracking").decending("viewCounter").limit(4).find()

Where experience tracking is the name of the second database.

I am used to using SQL join queries so I was assuming that we had to reference the foreign and primary keys of the two databases, but after reviewing the docs again I think this is the correct way of going about this.

1 Like

@bwprado Okay I thought I had this figured out but now when I run the above code I get an error saying that Experience Tracking does not exist in schema. Re reading the article for the third time now it says Create a query, include a reference field, and run it ". The specific field that references the Experiences database in the Experience Tracking database is just called Experience. See below:


So I updated my code to now include experience (which is the ID of the Experience field) and I still get the same error.

Included experience field does not exist in schema.

Is it working? If not, can you show me your collections?

I have also tried to just filter the database that contains the Experiences. Doing so does not log anything to the console with no errors thrown, however code that I have written after this line does not execute.

$w("#dataset2").setFilter(wixData.filter().contains("state", resort.state).include("experience").descending("viewCounter").limit(2).then(returned => console.log(returned))

@amotor so, a dataset filter does not return anything, it just filters a dataset. You have to feed the dataset somewhere.

function setFilter(filter: WixDataFilter): Promise

@bwprado On the page dataset2 is hooked up to the Experiences database and contains all the experiences from all the states. I am using that same structure on a different page to filter a different repeater from the same database (just not joining the reference database). This is the code that works on the other page:

$w("#dynamicDataset").setFilter(wixData.filter()
        .contains("category", category)
        ).then(() => {
        // expand repeater after filter
            $w("#repeater1").expand;
        })

I also have the repeater (in this case repeater2) hooked up to the Experiences database. So if the filter is successful it should filter the repeater data just like it does on the other page in which the above code is working.

After referencing every single document Wix has on multi reference queries and countless fruitless efforts on my end I have finally figured out that it is physically impossible to use reference queries the way I was.

In order to get this to work I had to START on the database with the reference field. Not try to join the database WITH the reference field. All I did was create a new field in my Experiences database and set it to a reference field that pointed to the id of the tracking database and the query worked as intended.

Great! That was the main goal, to create a reference field. I’m glad it worked.

1 Like

@bwprado Thank you for all your help with this!