Unable to sort item from a afterquery hook in database

Ask the community
,
1

Hi guys,

Recently, I added a hook afterquery to calculate many values depending on others from my database. I was wondering why the new values are not sorting right in the dataset when i try to sort them, for example, High to Low.

How can I fix this ?
Thanks! :slight_smile:

Thereā€™s my code in the afterquery data.js file:

export function VestiaireMixte_afterQuery(item, context) {
var today = new Date();
var birthdate = new Date(item.dateDeNaissance);
var msPerDay = 364 * 24 * 60 * 60 * 1000;
var age = (today.getTime() - birthdate.getTime()) / msPerDay;
var age = Math.round(age);
item.age = age.valueOf();

var totalmatch = item.victoire + item.defaite;
item.nbMatchJoues = totalmatch.valueOf();

var pourcentvictoire = (item.victoire / item.nbMatchJoues) * 100;
var pourcentvictoire = Math.round(pourcentvictoire);
item.pourcentageVictoire = pourcentvictoire.valueOf();

return item;
}

2

For starters you have only pasted up your data.js file that shows your afterQuery data hook.
So we can assume that you do have a query in your page code for this to work - just checking :wink:

https://support.wix.com/en/article/corvid-about-data-hooks
https://support.wix.com/en/article/corvid-using-data-hooks
https://www.wix.com/corvid/reference/wix-data.Hooks.html#afterQuery
Examples from the above Wix API Reference link

A hook after a find

// In data.js

export function myCollection_afterQuery(item, context) {
  let hookContext = context;  // see below

  // some changes to the received item

  return item;
}

/*
 * hookContext:
 *
 * {
 *   "collectionName": "myCollection",
 *   "userId":         "f45jf8d2-grkj-2opd-4ovk-9rfj4wo5tvj3",
 *   "userRole":       "siteOwner"
 * }
 */

Change the retrieved item

// In data.js

export function myCollection_afterQuery(item, context) {
  let hookContext = context;  // see below

  // some changes to the received item
  item.full_name = item.first_name + " " + item.last_name;

  return item;
}

/*
 * hookContext:
 *
 * {
 *   "collectionName": "myCollection",
 *   "userId":         "f45jf8d2-grkj-2opd-4ovk-9rfj4wo5tvj3",
 *   "userRole":       "siteOwner"
 * }
 */

See here for a tutorial from Wix that uses the afterQuery datahook in it which may be a good example for you to read and take bits from it etc.

Finally have a use of the search function for this forum as you may find previous forum posts that may help you with this issueā€¦

3

I donā€™t get it. :frowning:

Sorry, doing my best to understand but itā€™s my first time coding. :slight_smile:

With the code I linked in the data.js, iā€™m able to fill all the empty value in the datasheet exactly like I want (by the way, I use that code in data.js only to calculate different values into the datasheet).

But when I try to sort those ā€˜new calculated itemsā€™ (example here from the datasheet itself), it doesnā€™t sort them.

Also, if I remove the code into data.js, the calculated values are gone and itā€™s going blank again.

On the main page, I use dataset to sort and show only the highest number returned but it always return a nowhere value.

I tried to figure out what is the problem, but iā€™m kind of lost. :stuck_out_tongue:

1004Ɨ736 81.1 KB

4

@apocalyptus Itā€™s not possible to sort the collection by afterQuery values. The values are not stored in the collection theyā€™re just presented there (and only in the sandbox).

5

@jonatandor35 Thank you for the quick response!

Is there any way to calculate, then fill my datasheet with ā€˜trueā€™ Value?

Thanks! :slight_smile:

6

@apocalyptus Please elaborate.

7

@jonatandor35 Well, If I sort value that I type myself into the datasheet, the sorting system works fine. If I use afterQuery to fill the datasheet, the sorting system do not work.

Now, is there any way to calculate, then fill my datasheet without using afterQuery, the way Iā€™ll be able to use the sorting system?

8

@apocalyptus instead of afterQuery, you can use beforeInsert and beforeUpdate to calculate the value and to update the record (letā€™s say you add a filed key ā€œcalculatedValā€.
So in the data.js, you can do:

function calculateValue(item) {
    //make the calculation...
    return value;//value is the calculated value
}
export function VestiaireMixte_beforeInsert(item, context) {
    item.calculatedVal = calculateValue(item);
    return item;
}
export function VestiaireMixte_beforeUpadte(item, context) {
    item.calculatedVal = calculateValue(item);
    return item;
}
9

@jonatandor35 What is suppose to trigger the calcul?

Because I added this to data.js and nothing happened to the datasheet:

export function VestiaireMixte_beforeInsert(item, context) {
var totalmatch = item.victoire + item.defaite;
item.nbMatchJoues = totalmatch;
return item;
}
export function VestiaireMixte_beforeUpdate(item, context) {
var totalmatch = item.victoire + item.defaite;
item.nbMatchJoues = totalmatch;
return item;
}

10

@apocalyptus It only triggers when you add a new record or update an existing one.
It wonā€™t run on records that already there if you wonā€™t update them.
To solve that, you can create a page for 1-time update, add a button to the page and do something like:

import wixData from 'wix-data';
$w.onReady(() => {
    $w("#button1").onClick(event => {
        wixData.query("VestiaireMixte")
        .limit(1000)
        .find()
        .then(r => {
            let items = r.items;
            return wixData.bulkUpdate("VestiaireMixte", items);
        })
        .then(() => console.log("done"));
    })
})
11

@jonatandor35 Oh Yeah! It works fine now!

Very very big thanks to you ! :))