Calculations in a Database

Is there a way to do a calculation in a database field? I am putting together a database for a Nutrition Program and would like to specify which week, day, and meal of the program each entry corresponds to. Then, I would like there to be a way to feature the images of the current day’s meals as a teaser on our public Nutrition Page. To do that, I believe I would have to calculate from the start date of the program, which of those entries corresponds to the current date.

Yes, it’s possible in more than one way.
I will explain the simplest one…

in wixcode, you can add data hooks . data hooks are code snippets that run in the backend, before or after wix-data handles a request.

in you case, you could add an “after query” hook that gets a chance to perform the computation you mentioned, and then mutate the data that is returned as part of the query.

so imagine the following:

  • you add a field to the collection called “computed value” or similar
  • the field is left empty; it will get values through a calculation performed in the backend
  • in the after query hook, perform the calculation for each retrieved item and update the “computed field” property of the item
  • in the client, use that field to perform any logic you may have

notes:
since this field does not really have any data in the collection as it is computed on the fly, the following is not supported:

  • filtering on that field’s values
  • sorting on that field’s values

also, any data you persist into that field will be replaced by the computation value on the fly, so not much use to persist data in it.

hope that helps!

p.s.
assuming your page has a dataset on it, you can perform the same kind of logic using the dataset’s onReady() event to achieve the same.

3 Likes

Thank you!! I will look into both of these to see if I can translate your directions into my ability to code. Lol!! I appreciate your help!

1 Like

when your users signup you could store signup-date and signup-weeknumber. Then you take the todays day, todays current weeknumber and you can calculate easy.

current weeknumber - signup-weeknumber will give you which week they are in and then you can filter a datacollection on weeknumber from the result of earlier calculation.

So if I signup week 37 and today it is week number 42, 42-37 = 5 which means I am in program week 5, then get all records that correspond to week 5.

Then do the same with days.

If you need code search google for javascript date time difference calculations.

2 Likes

Thank you, Andreas!

Hi all,
I see this thread is a little long in the tooth, so I may try creating a new thread also, but here we go!

In a collection named “docjobs”, I have two columns (“docCoverageDay” and “paNpCoverageDay”) with numbers which I would like to add, and then insert the sum into a new column (“totalCoverageDay”). The following code works to simply add 5 to the docCoverageDay:

export function docjobs_afterQuery(item,) {
	//TODO: 
	item.totalCoverageDay = tcd(item.docCoverageDay);
	
	return item;
}

function tcd(docCoverageDay, paNpCoverageDay) {
	
	let tcd = docCoverageDay + 5;
	return tcd;
	
	//let tcd = docCoverageDay + paNpCoverageDay;
	//return tcd;
	
	
}

but the following code does NOT work when trying to add the two columns together as desired:

export function docjobs_afterQuery(item,) {
	//TODO: 
	item.totalCoverageDay = tcd(item.docCoverageDay);
	
	return item;
}

function tcd(docCoverageDay, paNpCoverageDay) {
	
	//let tcd = docCoverageDay + 5;
	//return tcd;
	
	let tcd = docCoverageDay + paNpCoverageDay;
	return tcd;
	
	
}

I followed the basic idea of this tutorial but I’m a noob, with a noob problem, and am hoping for a noob solution (o:

thanks so much!
G