javascript - getting NaN value some times and some times not

I get NaN value when I try to show the value of (item.total_amount,item.price,item.addtional_price) in ejs page but when I debug the variables some time I get the value and some time not!! this is the nodejs code

const getCustomers = async (req) => {
    const name = req.query.name || null;
  
    try {
      let query = `
        SELECT 
          customers.id AS customer_id,
          customers.name AS customer_name,
          customers.phone AS customer_phone,
          SUM(receipt_vouchers.amount) AS total_amount,
          reservations.price,
          reservations.addtional_price
        FROM 
          customers
        LEFT JOIN 
          receipt_vouchers 
          ON receipt_vouchers.customer_id = customers.id
        LEFT JOIN 
          reservations 
          ON reservations.customer_id = customers.id
      `;
  
      let params = [];
  
      // If a name is provided, add a WHERE clause to filter by customer name
      if (name) {
        query += ` WHERE customers.name ILIKE $1`;
        params.push(`%${name}%`);
      }
  
      // Group by customer and reservation columns to avoid aggregation on reservations values
      query += `
        GROUP BY 
          customers.id, 
          customers.name, 
          customers.phone, 
          reservations.price, 
          reservations.addtional_price
      `;
  
      // Execute the query
      const result = await db.query(query, params);
      return result.rows;
    } catch (error) {
      console.error("Error fetching customers:", error);
      throw error;
    }
  };


app.get("/customers", async (req, res) => {
    try {
        const customers = await getCustomers(req);
        console.log(customers.map(row => row));
        res.render("customers.ejs", { // Ensure the template path is correct
            data: customers
        });
    } catch (error) {
        console.error("Error in /customers route:", error); // Debugging
        res.status(500).json({ error: "Internal Server Error" }); // Handle errors
    }
});

and this is the ejs part

<tbody id="tableBody">
          <% if (data.length > 0) { %>
            <% data.forEach(function(item) {
                console.log("total_amount:", item.total_amount);
                console.log("price:", item.price);
                console.log("additional_price:", item.additional_price);
                let theAmount = parseInt(item.total_amount);
                let thePrice = parseInt(item.price) ;
                let theAdditionalPrice = parseInt(item.additional_price);

                 let total = thePrice + theAdditionalPrice;
                 let balance = total - theAmount;
            %>
              <tr>
                <td><%= item.customer_id %></td>
                <td><%= item.customer_name %></td>
                <td><%= item.customer_phone %></td>
                <td><%= item.total_amount %></td>
                <td><%= total %></td>
                <td><%= balance %></td>

and this is the value of debugging

total_amount: null
price: null
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 322523.00
price: 2938429
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 3999.00
price: null
additional_price: undefined
total_amount: 83834.00
price: 39382
additional_price: undefined
total_amount: 2000.00
price: null
additional_price: undefined
total_amount: 10000.00
price: 100000
additional_price: undefined
total_amount: null
price: null
additional_price: undefined

some time is get the value and some time not !!!

I try to change the data type coz I got the data from data base so I change to parseInt & parseFloat but i still getting the same value Nan I asked AI to solve this problem it suggest to add ( parseFloat(item.additional_price)||0 ) but this will give me a wrong value (0) while I want a real value (not 0)

const getCustomers = async (req) => {
    const name = req.query.name || null;
  
    try {
      let query = `
        SELECT 
          customers.id AS customer_id,
          customers.name AS customer_name,
          customers.phone AS customer_phone,
          SUM(receipt_vouchers.amount) AS total_amount,
          reservations.price,
          reservations.addtional_price
        FROM 
          customers
        LEFT JOIN 
          receipt_vouchers 
          ON receipt_vouchers.customer_id = customers.id
        LEFT JOIN 
          reservations 
          ON reservations.customer_id = customers.id
      `;
  
      let params = [];
  
      // If a name is provided, add a WHERE clause to filter by customer name
      if (name) {
        query += ` WHERE customers.name ILIKE $1`;
        params.push(`%${name}%`);
      }
  
      // Group by customer and reservation columns to avoid aggregation on reservations values
      query += `
        GROUP BY 
          customers.id, 
          customers.name, 
          customers.phone, 
          reservations.price, 
          reservations.addtional_price
      `;
  
      // Execute the query
      const result = await db.query(query, params);
      return result.rows;
    } catch (error) {
      console.error("Error fetching customers:", error);
      throw error;
    }
  };


app.get("/customers", async (req, res) => {
    try {
        const customers = await getCustomers(req);
        console.log(customers.map(row => row));
        res.render("customers.ejs", { // Ensure the template path is correct
            data: customers
        });
    } catch (error) {
        console.error("Error in /customers route:", error); // Debugging
        res.status(500).json({ error: "Internal Server Error" }); // Handle errors
    }
});

and this is the ejs part

<tbody id="tableBody">
          <% if (data.length > 0) { %>
            <% data.forEach(function(item) {
                console.log("total_amount:", item.total_amount);
                console.log("price:", item.price);
                console.log("additional_price:", item.additional_price);
                let theAmount = parseInt(item.total_amount);
                let thePrice = parseInt(item.price) ;
                let theAdditionalPrice = parseInt(item.additional_price);

                 let total = thePrice + theAdditionalPrice;
                 let balance = total - theAmount;
            %>
              <tr>
                <td><%= item.customer_id %></td>
                <td><%= item.customer_name %></td>
                <td><%= item.customer_phone %></td>
                <td><%= item.total_amount %></td>
                <td><%= total %></td>
                <td><%= balance %></td>

and this is the value of debugging

total_amount: null
price: null
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 322523.00
price: 2938429
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 3999.00
price: null
additional_price: undefined
total_amount: 83834.00
price: 39382
additional_price: undefined
total_amount: 2000.00
price: null
additional_price: undefined
total_amount: 10000.00
price: 100000
additional_price: undefined
total_amount: null
price: null
additional_price: undefined

some time is get the value and some time not !!!

Share Improve this question asked 19 hours ago Ali Alariqi 111 silver badge2 bronze badges New contributor Ali Alariqi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Did you tried that query directly on sql server and confirmed that all data shown? Try to debug and set break point inside that query and check that item object actually contains those values, when you receive them. – maximelian1986 Commented 19 hours ago
I see you are doing a customers left join reservations and using reservations columns in the select clause instead of grouping functions. That means that you are retrieving customers despite having or not matching joining records on the other tables and anyway you are having multiple records for the same customer based on how many reservations it has (this part might be not an error). Anyway the left join from customers it's enough to have null values for columns not belonging to the customers table itself. Using inner join would quickly make it behave differently. – Diego D Commented 19 hours ago
I am assuming the typo "addtional_price" is not really in your code?! Since you're using LEFT JOIN, some customers might not have matching reservations or receipts, causing NULL values in price, additional_price, or total_amount. Also you can ensure zero values (as you got suggested) in any case by using let xyz = parseFloat(item.xyz) || 0; – Ian Carter Commented 13 hours ago

Add a comment |

2 Answers 2

Sorted by: Reset to default 1

item.additional_price can be sometimes undefined. Also, sometimes price and total_amount can be null, which leads to NaN when performing arithmetic. Instead of parseInt(item.price), first check if the value exists.

<% 
    let theAmount = item.total_amount !== null ? parseFloat(item.total_amount) : null;
    let thePrice = item.price !== null ? parseFloat(item.price) : null;
    let theAdditionalPrice = item.additional_price !== null ? parseFloat(item.additional_price) : null;

    let total = (thePrice !== null && theAdditionalPrice !== null) ? thePrice + theAdditionalPrice : null;
    let balance = (total !== null && theAmount !== null) ? total - theAmount : null;
%>
<tr>
    <td><%= item.customer_id %></td>
    <td><%= item.customer_name %></td>
    <td><%= item.customer_phone %></td>
    <td><%= theAmount !== null ? theAmount : "N/A" %></td>
    <td><%= total !== null ? total : "N/A" %></td>
    <td><%= balance !== null ? balance : "N/A" %></td>
</tr>

The query is flaky:

Since you are joining customers with both receipt_vouchers and reservations, the SUM(receipt_vouchers.amount) can be incorrect because it might duplicate rows when a customer has multiple reservations.

This means total_amount might be higher than expected or NULL in some cases.

SELECT 
  customers.id AS customer_id,
  customers.name AS customer_name,
  customers.phone AS customer_phone,
  COALESCE(receipts.total_amount, 0) AS total_amount,
  reservations.price,
  reservations.additional_price
FROM 
  customers
LEFT JOIN 
  (SELECT customer_id, SUM(amount) AS total_amount FROM receipt_vouchers GROUP BY customer_id) AS receipts
  ON receipts.customer_id = customers.id
LEFT JOIN 
  reservations 
  ON reservations.customer_id = customers.id

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

javascript - getting NaN value some times and some times not - Stack Overflow

2 Answers 2

与本文相关的文章

评论列表(0)