Compare two fileds in a database

Ask the community
,
1

Hi Reader,

I would like to compare two date fields in a database. Say the fields are deadline and completedOn date. I want to fetch the rows whose completedOn date is less than equal to the deadline date. I tried something like this,
wixData.query(“collectionName”).le(“completedOn”,“deadline”)
But it didn’t work.

Can someone help me with this?

2

This previous post should help you for that.
https://www.wix.com/corvid/forum/community-discussion/how-to-query-multiple-fields-in-a-dataset

3

Hello,

Thanks for your reply. In that post, they are trying to fetch the data from database based on user input. In my case, no input from the user. I want to fetch data based on the two columns.

wixData.query(“collectionName”).eq (“columnName”,userInput) is Works fine

But, wixData.query(“collectionName”).eq (“columnName1”,“columnName2”) is not working.

4

@preethipalaniappanid Yes, however you need to do like they have done with their own Wix Data Filter and do the same with your own Wix Data Query and use either the and() or the or()function, you can’t add the two fields into the one query.

Make sure that you read the reference info carefully as you might not need to use the and() function in your code.
https://www.wix.com/corvid/reference/wix-data.WixDataQuery.html#and
https://www.wix.com/corvid/reference/wix-data.WixDataQuery.html#or

As you are saying you are just pulling info from a dataset instead of a user input, then you could just simply use Wix Dataset getCurrentItem instead.
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#getCurrentItem

If you are after specific field instead of the whole of the users info, then simply add the field name at the end of the function like this.
$w(“#dataset1”).getCurrentItem().fieldName;

With that option you might have to use the setFieldValue() or setFieldValues() function too along with the save() function.
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#setFieldValue
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#setFieldValues
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#save

5

Hi,

Yeah, I am trying to get the count of items finished on time that’s before the deadline date. Basically, I have two columns one is the actual deadline and another column is holding the completion on the date.

So in order to get the count, I need to find the count of items completed on or before the deadline. I feel like, I cant use the OR and AND logical operator.

It would be great if you could post the sample code to achieve my requirement.

Thanks in advance!!

446×521 8.47 KB