Hello!
In theory this query seems so simple, but I cannot figure out how to extract the count of multiple reference items for each row in a collection. See screenshot below.
I would like to create a “COUNT” calculation in the collection as a 4th column that counts the number of Design Profiles that have each Decor Item.
The result of this data hook/query should be the following and would update every time new reference items are added.
(1) Design Profile referenced for Throw Blankets
(3) Design Profiles referenced for Throw Pillows-Regular
so on and so forth
thank you in advance for any help!
@stcroppe @liran-kurtz-wix @sapirh @code-queen
@tarrahopwood For this you need to make use of the wixData.queryReference function.
Basically you you would need to load all items in the -DecorItems collection and then reduce the items array looking up all referenced DesignProfiles.
Something like this: [REVISED TO CREATE A FUNCTION AND FIX TYPOs] 
$w.onReady(() => {
// Get our Design profile counts
getDesignProfileCountsForDecorItems()
.then(arrayOfResultObjects => {
console.log(JSON.stringify(arrayOfResultObjects));
})
.catch(error => {
console.log(error.message);
});
})
function getDesignProfileCountsForDecorItems() {
const RECOVERABLE_ERROR = “No entries to check”;
// Get all decor items
return wixData.query(“-DecorItems”)
.find()
.then(queryResult => {
// Did we find any items?
if (queryResult.length === 0) {
throw Error(RECOVERABLE_ERROR);
}
// If we get here then we have records to process
let resultsPromise = queryResult.items.reduce((promiseArray, nextRecord) => {
// Create a queryReference for this record
let queryPromise = wixData.queryReference(“-DecorItems”, queryPromise._id, “designProfile”)
.then(designProfiles => {
return { queryPromise.title : designProfiles.length }
});
promiseArray.push(queryPromise);
return promiseArray;
}, []);
// When we get here we should have an array of objects containing one property
// <Decor Item Name> : <Number of Design Profiles>
//
// Example: [{"Throw Blankets":1}, {"Throw Pillows - Regular":3}...]
return Promise.all(resultsPromise);
})
.catch(error => {
if (!error.message.include(RECOVERABLE_ERROR)) {
// A real error so throw it again
throw error;
}
// If we get here we don't have anything to return so return an empty array
return [];
});
}
Im writing this on my phone so cannot account for full syntax correctness. That’s why it’s something like this
@stcroppe As always - THANK YOU! I will try this out and let you know. Super impressive you wrote that on your phone!
@stcroppe Thank you again for your help. The following seem to be the following issues:
-
the arrayOfResultObjects is logging " undefined "
.then (arrayOfResultObjects => { console.log(JSON.stringify(arrayOfResultObjects));
-
There appears to be some issue when i get to the queryPromise.
For the return { queryPromise.title : designProfiles.length } there is either a syntax error I can’t figure out or it is unable to reach the code above.
See below result in editor:
import wixData from ‘wix-data’;
$w.onReady(() => {
// Get our Design profile counts
getDesignProfileCountsForDecorItems()
.then(arrayOfResultObjects => {
console.log(JSON.stringify(arrayOfResultObjects)); ////LOGGED AS UNDEFINED///
})
. catch (error => {
console.log(error.message);
});
})
function getDesignProfileCountsForDecorItems() {
const RECOVERABLE_ERROR = “no entries to check”;
// Get all decor items
return wixData.query(“-DecorItems”)
.find()
.then(queryResult => {
// Did we find any items?
if (queryResult.length === 0) {
throw Error(RECOVERABLE_ERROR);
}
// If we get here then we have records to process
let resultsPromise = queryResult.items.reduce((promiseArray, nextRecord) => {
// Create a queryReference for this record
let queryPromise = wixData.queryReference(“-DecorItems”, queryPromise._id, “DesignProfile”)
.then(DesignProfiles => {
return { queryPromise.title : designProfiles.length }
});
promiseArray.push(queryPromise);
return promiseArray;
}, []);
// When we get here we should have an array of objects containing one property
// :
//
// Example: [{“Throw Blankets”:1}, {“Throw Pillows - Regular”:3}…]
return Promise.all(resultsPromise);
})
. catch (error => {
if (!error.message.include(RECOVERABLE_ERROR)) {
// A real error so throw it again
throw error;
}
// If we get here we don’t have anything to return so return an empty array
return [];
});
}
@tarrahopwood OK - Yes again, the perils of responding to forum posts on an iPhone.
Try this
import wixData from 'wix-data';
$w.onReady(() => {
// Get our Design profile counts
getDesignProfileCountsForDecorItems()
.then(arrayOfResultObjects => {
console.log(JSON.stringify(arrayOfResultObjects)); ////LOGGED AS UNDEFINED///
})
.catch(error => {
console.log(error.message);
});
});
const RECOVERABLE_ERROR = "no entries to check";
function getDesignProfileCountsForDecorItems() {
// Get all decor items
return wixData.query("-DecorItems")
.find()
.then(queryResult => {
// Did we find any items?
if (queryResult.length === 0) {
throw Error(RECOVERABLE_ERROR);
}
// If we get here then we have records to process
let resultsPromise = queryResult.items.reduce((promiseArray, nextRecord) => {
// Create a queryReference for this record
let queryPromise = wixData.queryReference("-DecorItems", nextRecord._id, "DesignProfile")
.then(designProfiles => {
let returnObject = {};
returnObject[nextRecord.title] = designProfiles.length;
return returnObject;
});
promiseArray.push(queryPromise);
return promiseArray;
}, []);
// When we get here we should have an array of objects containing one property
// <Decor Item Name> : <Number of Design Profiles>
//
// Example: [{"Throw Blankets":1}, {"Throw Pillows - Regular":3}...]
return Promise.all(resultsPromise);
})
.catch(error => {
if (!error.message.include(RECOVERABLE_ERROR)) {
// A real error so throw it again
throw error;
}
// If we get here we don't have anything to return so return an empty array
return [];
});
}
@stcroppe hmm - the resultsPromise is still “undefined”
.then(arrayOfResultObjects => {
console.log(JSON.stringify(arrayOfResultObjects));
and I still get this error thrown for the final catch error:
error.message.include is not a function
@tarrahopwood Yes again my mistake. The function is this one which examines strings:
So the line with the error should be error.message.includes not include :-(.
Cheers
