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) @Sapir (wix) @Code Queen Nayeli
@Tarra Maxwell For this you need to make use of the wixData.queryReference function.
https://www.wix.com/corvid/reference/wix-data.html#queryReferenced
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 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
@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 // <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 As always - THANK YOU! I will try this out and let you know. Super impressive you wrote that on your phone!