Sorting a DataviewJS table

[leesoko]

What I’m trying to do

I have files like this:

# Jane Doe
---
birthday: '2000-01-01'
---

And I want a Dataview query to get the next birthdays from people, as well as their age, and days until the birthday.

With this code (and yes it’s mostly AI generated)…

function formatDate(date) {
    const monthNames = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" ];
    return `${monthNames[date.getMonth()]} ${date.getDate()}`;
}

function calculateAge(birthdayStr) {
    const today = new Date();
    const birthday = new Date(birthdayStr);
    let age = today.getFullYear() - birthday.getFullYear();
    const m = today.getMonth() - birthday.getMonth();
    if (m < 0 || (m === 0 && today.getDate() < birthday.getDate())) {
        age--;
    }
    return age + 1; // Adding 1 to get the age they will be turning
}

function getDaysUntilNextBirthday(birthdayStr) {
    const birthday = new Date(birthdayStr);
    const today = new Date();
    birthday.setFullYear(today.getFullYear());

    // If the birthday has already occurred this year, set it to the next year
    if (birthday < today) {
        birthday.setFullYear(today.getFullYear() + 1);
    }

    // Calculate the difference in days
    const diffTime = Math.abs(birthday - today);
    const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    return diffDays;
}

const peopleData = dv.pages('"people"')
    .map(p => {
        const birthdayStr = p.birthday;
        const birthdayDate = new Date(birthdayStr);
        return {
            link: `[[${p.file.name}]]`,
            formattedDate: formatDate(birthdayDate),
            age: calculateAge(birthdayStr),
            daysUntil: getDaysUntilNextBirthday(birthdayStr)
        };
    });

dv.table(["Name", "Date", "Age", "Days Left"], 
    peopleData.map(p => [p.link, p.formattedDate, p.age, p.daysUntil])
);

… I get something like this:

Name	Date	Age	Days Left
Jamie Doe	December 31	23	320
Jane Doe	January 1	25	321

So all that’s missing is sorting the table by the “Days Left” column in ascending order. And that’s where I seem to fail.

Things I have tried

• add peopleData.sort((a, b) => b.daysUntil - a.daysUntil); in front of the dv.table(...); part at the end
• add ,{ "Days Until": 'asc' } after peopleData.map(...) at the end
• add peopleData.sort((a, b) => a.daysUntil - b.daysUntil) in front of peopleData.map(...) at the end

Edit: None of these changed anything, the query was still valid, but not sorted.

Possible Solution / Alternatives

I could use a DQL query instead, in fact that was my original plan.

The code I had is…

TABLE 
dateformat(birthday,"MMMM dd") AS "Day",
round((this.file.mday - birthday).years) AS "Age",
(this.file.mday - birthday).days
FROM "people"

which returns something like:

People	Day	Age	(this.file.mday-birthday).days
Jamie Doe	December 31	22	8075
Jane Doe	January 01	24	8804

I didn’t manage to get the days until the next birthday, which is why I switched to DataviewJS afterwards. But with DQL at least I knew how to sort

Any help would be greatly appreciated ^^

[holroy]

Normally in dataviewjs in a chained query like you’re using it would suffice to do something like peopleData.sort(p => p.age) before you do the mapping. I’ve not tested that in your query, as there is so much strangeness happening in it due to your use of AI…

On another note, you could also try out the following query:

```dataview
TABLE WITHOUT ID birthday, dateformat(birthday, "dd MMMM"), today.year - birthday.year, monthDiff, age
FROM "people"
FLATTEN date(today) as today
FLATTEN today.month - birthday.month as monthDiff
FLATTEN (today.year - birthday.year +
  choice( monthDiff < 0 OR (monthDiff = 0 AND today.day < birthday.day), -1, 0)) as age
SORT age ASC
```

And see if that doesn’t return something like what you’re looking for. The main trick used in this query is to just access the dates as dates and pick the various parts out from the date, and using the choice() to subtract a year if todays date (within the year) is before the birthday.

[leesoko]

That didn’t do the trick, but it inspired me to use FLATTEN (I’m not really familiar with SQL yet), which is how I came up with:

TABLE
birthdate as "Birthday",
round((today - birthday).years) as "Age Then",
daysLeft as "Days Left"

FROM "people"

FLATTEN dateformat(birthday,"MMMM dd") as birthdate
FLATTEN date(today) as today
FLATTEN dateformat(birthday,(this.file.mday.year)+"-MM-dd") as birthdayThisYear
FLATTEN (date(birthdayThisYear) - today).days as daysLeft

WHERE daysLeft >= 0

SORT daysLeft asc
limit 10

…which worked for me

So thank you^^