Getting an array of dates from a Strapi query

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 3.2.4
  • Operating System: macOS High Sierra 10.13.6
  • Database: SQLite 5.0.0, PostgreSQL 8.4.1
  • Node Version: 12.18.4
  • NPM Version: >=6.0.0
  • Yarn Version: 1.22.4

I installed a package date-streaks to calculate consecutive day streaks from my blog posts, but it needs the published_at data in an array, which my raw db query isn’t providing.

This is my query in my posts controller:

await strapi.connections.default.raw("SELECT published_at FROM posts WHERE author='1' ORDER BY published_at ASC");

What I’m getting from the query:

"command": "SELECT",
  "rowCount": 3,
  "oid": null,
  "rows": [
    {
      "published_at": "2020-10-30T04:00:00.193Z"
    },
    {
      "published_at": "2020-10-31T13:30:09.589Z"
    },
    {
      "published_at": "2020-11-01T13:29:20.990Z"
    }
  ],
  "fields": [
    {
      "name": "published_at",
      "tableID": 16641,
      "columnID": 7,
      "dataTypeID": 1184,
      "dataTypeSize": 8,
      "dataTypeModifier": -1,
      "format": "text"
    }
  ]......

What I need is to strip out all the other data from the result set, and just get an array of published_at dates, like this:

[
       "2020-10-30T11:00:00.193Z",
       "2020-10-31T13:30:09.589Z",
       "2020-11-01T15:29:20.990Z"
]

Anyone have any advice on how I can write my db query to get just an array? Or is it easier to use strapi.query…find/search instead of raw db query? Any general pointers or high level actions to take will be super helpful for me to research further! TIA!! :blush:

2

Found the solution, so answering my own noob question here :sweat_smile:

I saved the response from the raw db query as a variable, and then used the js method .map() to return a new array with just the dates:

const rawDates = await strapi.connections.default.raw("SELECT published_at FROM posts WHERE author='1' ORDER BY published_at ASC"); 

const dates = rawDates.rows.map(row => row.published_at);

return dates;

I understand that querying the db directly like this is better performance wise, but just for learning purposes, what’s the strapi.query equivalent solution… using find/search?

2 Likes