Combining 2 search result using "Or" operator

navpallav04 · 2025-03-27T14:18:48.440Z

I am using the below logic to display zodic sign based on DOB selection, the problem is the 30th nov is taken as scorpio and so on, the db is setwith start date, start month, end date and end month along with zodiac name:
Query 1 (Start Boundary)**

Find zodiac signs where:

Start Month = Date Picker’s Month
Start Day ≤ Date Picker’s Day

Query 2 (End Boundary)

Find zodiac signs where:

End Month = Date Picker’s Month
End Day ≥ Date Picker’s Day

Final Step: Merge the Queries

(Search for Zodiac Signs (Start Boundary) :merged with Search for Zodiac Signs (End Boundary)) :first item

ademiguel · 2025-03-27T14:32:29.727Z

I don’t fully understand the problem. Are incorrect Zociac Sign records being retrieved?

Why don’t you put both constraints in the same Search expression?
Why don’t you use just two date fields to account for the start and end dates of the zodiac sign?

Please provide an example of the buggy behaviour, with an example of the record you want to retrieve, with the values for its fields and the User selected dates.

navpallav04 · 2025-03-27T14:55:27.934Z

example - scorpio starts on 23rd oct and ends on 21st nov. My db has fields for start month = 10, start date = 23, end month = 11, end date = 21 for scorpio.
so if a person enters his date of birth as 31st oct YYYY, my query should check the date 10/31 against my db using the logic above , which is if the start month field matches 10 then check date >=23 . Now the second constraint which I have pud for second search is if the month is 11 .. I am unable to combine these 2 using “Merged with”. I have tried using single constriant but unable to do so since I dont have “or” or “and” to select in the option.

My final search line is - (Search for Zodiac Signs (Start Boundary) :merged with Search for Zodiac Signs (End Boundary)) :first item

and the Queries for start boundary and end boudary is as below:
Query 1 (Start Boundary Check)

Condition: If the Start Month of Zodiac Sign = DOB’s Month
AND Start Day ≤ DOB’s Day

Query 2 (End Boundary Check)

Condition: If the End Month of Zodiac Sign = DOB’s Month
AND End Day ≥ DOB’s Day

ademiguel · 2025-03-27T16:32:27.352Z

navpallav04:

I am unable to combine these 2 using “Merged with”

Does this mean that you are getting an expression error? That you are getting an incorrect Zodiac Sign record? Or that you are not getting any record as a result?

navpallav04 · 2025-03-27T16:46:13.000Z

I am getting output, but the search logic seems to be not working, all dates in October are coming to be Libra, All dates in nov come to scorpio and so on.

ademiguel · 2025-03-27T16:54:06.416Z

Please send screenshots of Queries 1 and 2

navpallav04 · 2025-03-28T03:11:49.000Z

Adrian,
Here are the screenshots:
Search query 1 :

image.png866×548 36.1 KB

Search Query 2

image.png857×513 34.1 KB

ademiguel · 2025-03-28T09:46:37.780Z

The problem is :extract day operator is misleading. It does not extract the day of the month, but the day of the week, i.e. a Monday would be ‘1’, Wedenesday ‘3’ and so on, as stated in the docs

So you need an alternative approach. I’d define a new field for Zodiac Sign of type date range to account for the dates they span. Then I’d use the contains (point) operator to verify if the selected date is within range.

@petter the contains (point) operator might need a name update in the docs, as it appears as contains.

image709×355 37.5 KB

image1063×487 44.1 KB

navpallav04 · 2025-03-28T09:58:30.000Z

I will try this and let you know, thankyou

petter · 2025-03-28T10:50:13.543Z

@petter the contains (point) operator might need a name update in the docs, as it appears as contains .

Thanks for pointing that out @ademiguel! Now updated

navpallav04 · 2025-03-28T11:14:51.000Z

Hey,
Before trying to use your method I simply changed “extract:day” to " extract:date" and it worked.. I am still testing it, hopefully it solves the issue.

Thankyou again for the support.
Pallav

achen2222 · 2025-03-29T04:05:46.270Z

I asked chat gpt to give me a javascript algorithm to find the zodiac sign. this gives you a hint that your records dont need to have both dates just one date. Then you can find all records that are less than your date (rather than extract days/months just create the year of the date as year zero) and the zodiac sign will be the last one

function getZodiacSign(month, day) {
// Zodiac signs and the last day of each sign in each month
const zodiacSigns = {
‘Capricorn’: new Date(0, 0, 20), // January 20
‘Aquarius’: new Date(0, 1, 19), // February 19
‘Pisces’: new Date(0, 2, 20), // March 20
‘Aries’: new Date(0, 3, 20), // April 20
‘Taurus’: new Date(0, 4, 21), // May 21
‘Gemini’: new Date(0, 5, 21), // June 21
‘Cancer’: new Date(0, 6, 22), // July 22
‘Leo’: new Date(0, 7, 23), // August 23
‘Virgo’: new Date(0, 8, 23), // September 23
‘Libra’: new Date(0, 9, 23), // October 23
‘Scorpio’: new Date(0, 10, 22), // November 22
‘Sagittarius’: new Date(0, 11, 21), // December 21
‘Capricorn’: new Date(0, 11, 31) // December 31
};

// Create a date object for the birth date
const birthDate = new Date(0, month - 1, day);

// Find the zodiac sign
for (let sign in zodiacSigns) {
    if (birthDate <= zodiacSigns[sign]) {
        return sign;
    }
}

return 'Capricorn'; // Capricorn starts from December 22 and goes until January 19

}