I have a database row like this:
invoice
description
amount_invoiced
When I do a multiple record query on this database table, I’d like to also output an extra field I compute at the time of output:
invoice
description
amount_invoiced
amount_accounting_currency (a_set_value_value * amount_invoiced)
How can I do this in a Server Action?
How about running it through a repeat, making sure to output all the columns you need, and then add a Set Value action that does the computation.
And will the repeat make the output an array like the output of the database query is?
Yes, the output of a repeat is an array.
Oh fab, I’ll give it a go! 
Thanks Ken!
Except it is midnight here, so maybe I’ll catch some sleep first… 
Sure. Just remember to disable the output of the query, so you don’t inadvertently select it on the front end.
Good point!
But, if the rows returned from query is a lot, it would be wiser to just use a custom query instead.
Well, maybe.
I think it falls in the “your mileage may vary” category, but yes doing the work in the database is an option.
I guess I’m just in the camp of using the db mostly for storage and retrieval of data and leaving the business logic to the server handler.
In my experience, I have found that letting DB server do some heavy lifting on its own is much more efficient then trying to code the same thing later. DB servers are very much capable of handling complex logics in queries, views & stored procedures.
Ya, I know, it’s a very common debate.
My reasons boil down to the projects I currently work on, where the single most critical thing is business validation (don’t get me started on how many builders forget/ignore this). And in that world, flexibility and speed of development is much more important than hyper optimized architecture.
Also, I’m a developer, not a dba, so I’ll err on the side of code. The workflow for code versioning and deployment is much simpler than that of a database.
And lastly, as a human, I’m the most expensive piece of the stack. I will gladly advise my clients to spend a couple bucks more on infrastructure, to save paying me to track down 100ms here 1000ms there. So if it won’t be noticed by the user, I don’t care… today.
For all of my projects (and I would guess most of everybody else’s here) the time for optimization is when scaling a business, not during the business validation phase.
Thanks for that perspective. Business validation phase should indeed incline towards optimizing time towards getting things done.
But, over the years, I have found that it takes a huge cost, both time & money, to restructure the application to be scalable. Increasing the hardware capabilities only help up to a point.
So, the approach that we take is to think of every application from the perspective of scaling it in the future, identify where we can cut down right-now to save time, and in the end, even if it takes a couple days or a week extra, we try to build the core foundation to be scaled without much disruption in the future.
We still have a lot to learn and it could be that your approach is better in the long run.
Where you have, say, mysql 8 performing queries this is its THING. I've worked with mysql for a decade and it keeps getting more & more capable & faster.
using the db mostly for storage and retrieval of data and leaving the business logic to the server handler
to me is underestimating the dedicated functions of mysql to process even the most complicated queries involving multiple joins, etc. in milliseconds.
MySQL is very powerful. It can handle much of the functionality of the most expensive and powerful database packages
MySQL performs quickly—even with large data sets
As you know, with node.js installation the included NPM enables use of the latest MySQL clients -- such as Node MySQL 2
where we can leverage the best aspects of node.js with the query power of the MySQL database installation.
In other words, depending on the data schema & needed queries, we might find querying a mysql database advantageous to just relying on NoSQL databases.
NoSQL scales horizontally across multiple servers better but it is generally does not handle complex queries as well as MySQL which easily JOINs tables which NoSQL is not natively equipped to do.
Also to consider is that MySQL offers in addition to the default tables, indexes & stored procedures now such goodies as updatable views, triggers, cursors, query caching, partitioned tables and clustering.
It might be that since Node can also query MySQL tables, including joins of tables and filtering of queries with latest passed variables that leaving MySQL to do "the heavy lifting" can be a best solution for applications that will not have to scale horizontally across multiple servers.
And, of course, with REDIS caching reading & writing queries to ease the load off MySQL or NoSQL databases and applications we can thank Wappler for staying close to the cutting edge of newest technologies.
Fabulous debate folks! You post something sleepily before going to bed and never know whether a treasure of debate or an ignored post will follow when you wake up.
I actually stayed up till 1am and tried out your idea @mebeingken. My situation is more complex than the simple explanation I gave in this post. It worked but was 10x slower for the server action to return the result.
Basically I’m adding detail to the invoice output about whether a payment is due or overdue. That information comes from a different search from a future_payment table. That table could return any number of rows or zero rows for a given invoice.
When you look to do this, you can cross-reference the two tables either on the client, the server or in the database.
My sense now I’ve experimented with this is it gets more efficient as you go towards the database, so maybe I’ll try something out there next!
Ahh, the plot thickens.
Then by all means join in the database.
Sorry, didn’t mean to suggest the database is not a critical and powerful tool in the belt, it is.
As with most things there needs to be a balance...and for me and the projects I work on, I favor the business side of the balance, not the technical.
Thanks, Ken
I’m settling in to your tutorials and they are incredibly useful!
I’m 100% a visual learner, so these are perfect for me !
I don’t believe you’ve mentioned an important piece of information as to how you get this query returned.
What kind of database “table” ? From MongoDB, MySQL, or some other NoSQL?
Is the query requiring a complete table search first & then applying the filters to limit the output rows? What is the Unique Value field if it’s a relational database like MySQL?