Counting of an Array within DB

Joseph

Hi all,

Im trying to get the count of likes for each post when querying all posts. 

I tried going through output > aggregate but it doesnt offer the option for arrays (i.e. posts.liked_by)
[1.PNG][agg.jpg]
I also tried going through eval but it seems to throw an error saying "aggregate needs to be processed first"
[eval.JPG][eval fail.JPG]
Can anyone please advise me on how I could get the count of an array? Many thanks!

Ray Deck

Postgres, which Xano uses for the database under the hood, offers a function called "array_length" that would satisfy your need. One calls this using the structured query language (SQL) common to these kinds of databases.

However, AFAICT one cannot customize the SQL except indirectly via the menus. At this time, these do not offer much customization of how the data gets pulled. You are correct that the aggregate operators will not work here - they are operating on cross-row aggregation, not within-row.

So the solution for now is to pull your arrays as part of the query, then iterate over the result using a for loop and use the "set" filter to update with the record-level summary statistics you are looking for. This is not top performance, but it will work, and it's fairly straightforward to substitute more efficient SQL approaches when/if the Xano team makes them available. I made a small video to walk through this: https://www.loom.com/share/dc5abb760d1b4e3aa9d433f3743ff5ea

Joseph

Hey Ray, thanks for your help and video, it was extremely informative and helpful.

I also found another way which was to create an entire new DB for 'likes' to record every 'like' 'transaction'. But I will give your method a go since I'd like to keep my records and DBs more succinct and organised.