Created my first test custom query on Strapi! OK seems simple enough... next: a custom query for production, for real
@scriptifyjs I'm using Postgres for db instead, so I should look at this instead ? https://www.postgresql.org/docs/9.5/tutorial-agg.html
I've used to build Node.js REST and GQL for quite some time now, and Strapi offers a very good tradeoff between flexibility and out-of-the box functionalities, perfect for smaller projects. Only heard good things about Rails, never tried it myself, I grew up in the Node.js land 🍭
If you use the package I think you actually don't need the custom query, as you can use Strapi's abstraction (.search) directly. But I think opting for SQL would be a good idea performance-wise, although SQL can be really difficult to grasp 🤷♂️ https://www.db-fiddle.com/ has always helped me a lot with that, didn't touch SQL for quite some time now though
You could extend the "find" method of the controller and pass custom data in the result, like so: https://ibb.co/fxPb3K3
Btw @scriptifyjs I'm trying to create a custom query to count articles I posted on consecutive days…thought about using the node module date-streaks, but unsure how to integrate and use in my controller/model… any pointers?
SQL is even better suited for tasks like this! Maybe this article can help you: http://www.johnshiver.org/posts/finding-longest-streak-of-days-with-postgresql/ If you can make raw SQL Queries with Strapi, I'm sure there's a way, that'd be the best solution I think.
Ah ok! thanks for pointing that out! Will try…. and also try SQL and see which one works better perf wise…
Hey @scriptifyjs now i managed to install and get the date-streaks package working using some fake data, but it needs the data in an array, which my raw db query isn’t providing.
This is my strapi query:
strapi.connections.default.raw("SELECT published_at FROM posts WHERE author='1' ORDER BY published_at ASC");
What I get from the strapi 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 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"
]
Do u have any advice on what I can do? TIA!! 😊
I deeply fell in love when first using Strapi, it gives you so much out of the box. Perfect for Indie Makers.
Yes me too! It's marketed as a headless cms but to me it's like a low-code API builder! Love it..
@scriptifyjs oh COOL thanks for the code snippet! Will try and let you know how it goes! So I don't need date-streaks npm right?
You could use that package but than you need to fetch all blogposts within the timeframe you want to analyze, doing that on every query can become a performance problem. If you only use Strapi for an SSG (and fetch the articles at build time) and you are sure it will stay like that, than you could also go that route. But MongoDB Aggregations are pretty cool IMO, just a bit more complicated to get into
@scriptifyjs progress update: managed to make raw sqlite db query in Strapi! Added a new streak route and a controller method using strapi.connections.default.raw("SELECT author, strftime('%d-%m-%Y %H:%M:%f', published_at/1000.0, 'unixepoch') FROM posts ORDER BY author DESC"); Not completely there yet but at least now I can query the author and published_at dates and see the output on my api client. Next steps - install date-streaks package and integrate it to calculate streaks, figure out how to query in production db since its Postgres
Using the JavaScript map array method:
const data = [
{ published_at: "2020-10-30T04:00:00.193Z" },
{ published_at: "2020-10-31T13:30:09.589Z" },
{ published_at: "2020-11-01T13:29:20.990Z" }
];
const datesOnly = data.map((row) => row.published_at);
console.log({ datesOnly });
IT WORKS YAAASSS!!! Had to tweak to const datesOnly = data.rows.map(...) but thanks for the right direction!!! So grateful for your help man! 🙏🙏🙏
@scriptifyjs oh haha great minds - I just landed on that john shiver article after searching through google. Never tried making SQL queries before ever, but will read up and figure out…
Not sure yet actually. Will try out the date-streaks package. Also want to try out using SQL to calculate directly as per https://learnsql.com/blog/how-to-calculate-length-of-series-in-sql/
@scriptifyjs yeah looking at the mongodb docs now…wow it's so out of my league haha..
Hahaha yes now that I know Strapi. Was using Rails to build APIs and tbh it was a bit of a leap of faith. I don't know any Node.js, Express.js or Koa.js which Strapi uses, so had a bit of separation anxiety initially haha
You could extend the "find" method of the controller and pass custom data in the result, like so: https://ibb.co/fxPb3K3
Quick qn: in the code image you shared, what's supposed to go into that part where you wrote // Aggregate ?
Node.js land seems to be where everyone is headed these days! Worth learning nonetheless (for me)
Regarding the calculation of the streak itself, I'd suggest you to have a look at MongoDB's Aggregation Pipelines: https://docs.mongodb.com/manual/aggregation/ You can calculate pretty much every derived value you can immagine with such a pipeline + it's way more performant than fetching all documents yourself and do the calculation on every query.
Awesome sounds good! So you plan to do the rest of the calculation in Node.js?
Please sign in to leave a comment.