Comments (8)
Is your table perhaps simply empty?
from postgres.
If you're not returning anything from sql.begin, that also explains it. Please post more complete code.
from postgres.
This is what my code looks like
let countDataOverview = 0;
let dash_data_name = ['c_amount', 's_sms', 'new_sub', 'new_unsub', 'm_total_sub', 'm_total_unsub', 'm_total_charge', 'smsc_stat', 'total_sub', 'total_unsub'];
let dash_data = {
c_amount: 0,
s_sms: 0,
new_sub: 0,
new_unsub: 0,
m_total_sub: 0,
m_total_unsub: 0,
m_total_charge: 0,
smsc_stat: 0,
total_sub: 0,
total_unsub: 0,
}
async function queryOverview(){
return await sql.begin(sql => [
/*
todo - Fetch different data for overview
*/
sql`select count(distinct phone)::int from sms.delivery where date = cast(now() as date)`,
sql`select count(distinct phone)::int from sms.sentsms where date = cast(now() as date)`,
sql`select count(distinct phone)::int from subscription.sub where subdate = cast(now() as date)`,
sql`select count(distinct phone)::int from subscription.unsub where unsubdate = cast(now() as date)`,
sql`select count(phone)::int from subscription.sub where subdate between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
sql`select count(phone)::int from subscription.unsub where unsubdate between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
sql`select sum(charge)::int as count from sms.dailycharge where date between (SELECT date_trunc('month', now())::date) and (SELECT (date_trunc('month', now()) + interval '1 month - 1 day')::date)`,
sql`select COALESCE(smsc_stat, 0) as count from smsc`,
sql`select count(phone)::int from subscription.sub`,
sql`select count(phone)::int from subscription.unsub`,
])
}
async function overAll(){
try {
overviewQ = await queryOverview();
weeklyQ = await queryWeeklyCharge();
overviewQ.map(result => {
dash_data[dash_data_name[countDataOverview]] = result[0].count;
countDataOverview++
})
console.log(weeklyQ)
weeklyQ.map(result => {
weekly_charge[weekly_name[countDataOWeekly]] = result[0];
countDataOWeekly++
console.log(result[0].charge)
})
sql.close();
}catch (e) {
console.log("There is an error reading from database, " + e.message)
}
}
export const load = async ({locals}) => {
if(!locals.user) {
throw redirect(302, '/')
}
await overAll()
return {
dash_data,
weekly_charge
}
}
The table called smsc doesn't have a data just for testing purpose
from postgres.
Oh, but if it doesn't have data, isn't that your answer? Then the query returns no rows and result[0] will be undefined?
from postgres.
Besides from that, I think you could benefit from restructuring your code here to just be a single query using CTEs or subqueries, that way you could also ensure you'd always get 0 in case of no rows.
All that data mangling you're doing in js would be way more efficient + prettier in SQL
from postgres.
Oh, but if it doesn't have data, isn't that your answer? Then the query returns no rows and result[0] will be undefined?
In a real case, the application will set the column with 0, but I want to know more if it's possible to replace null or undefined with 0
from postgres.
Besides from that, I think you could benefit from restructuring your code here to just be a single query using CTEs or subqueries, that way you could also ensure you'd always get 0 in case of no rows.
All that data mangling you're doing in js would be way more efficient + prettier in SQL
CTE's, I have no clue about that, I will check it now.
I am fetching data from multiple tables to show on my application's dashboard, just in case the query doesn't return anything I wanted to send 0 to the component using that value.
That's why I used sql.begin
,
Can you give a hint please, using CTEs to check if the column has no rows and then make it 0 on the return, or if there is a way to replace results from sql.begin
with 0 if undefined?
from postgres.
UPDATE:
I found this query from https://copyprogramming.com/howto/how-can-i-return-0-from-query-in-pgsql-if-the-row-doesn-t-exist
I replaced
sql`select COALESCE(smsc_stat, 0) as count from smsc`
with
sql`select coalesce((select smsc_stat from smsc), 0) as count`
and it returned 0 as I expected
from postgres.
Related Issues (20)
- simple() method is missing in types definition HOT 1
- `sql.array` is undocumented HOT 1
- Connecting to database with SSL on Cloudflare
- How do I use named Prepared Statements, with variables HOT 7
- postgres.js needs something like slonik's sql.join() for building dynamic queries HOT 2
- syntax error at or near "$1" HOT 1
- UnhandledSchemeError when using Drizzle ORM with Next.js authentication application
- query is not inferred correctly when doing multiple update, and a value is an array. HOT 1
- How to specify multiple hosts and ports in options
- Issue with dynamically building condition clause HOT 8
- could not determine data type of parameter $1 HOT 4
- Issue with auto failover with unresponsive host HOT 2
- Ability to inspect query fragments without database connection HOT 1
- Timeout issue when using with Bun HOT 17
- [question] How to get the query string before execution? HOT 3
- Nested arrays aren't expanded properly in 3.4.3 HOT 3
- Unable to parse Default Var on ALTER TABLE
- Unexpected Server Error Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. Received protocol 'cloudflare:' HOT 5
- arbitrary UNSAFE_TRANSACTION errors when using sql.begin HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from postgres.