Ejs form, send array of data that can be converted into postgresql update queries

Question

My app includes a scoresheet grid where each cell represents a student's score in one topic. The teacher can enter scores in each cell before clicking a submit button that sends them all at once.

Here is the ejs form that I have right now:

scoresheet.ejs

<tbody>
  <% students.forEach((student, i) => { %>
    <tr>
      <td class="student-cell right">
        <%= student.last_name %>, <%= student.first_name[0] %>
      </td>

      <% topics.forEach(topic=> { %>
        <td class="score-cell center">
          <input type="text" class="score-input" name="scores_<%= student.id %>_<%= topic.id %>">
        </td>
      <% }); %>
    </tr>
   <% }) %>
  </tbody>

This form produces a req.body that looks something like this:

scores_1_2: '75',
scores_1_3: '92',
scores_1_4: '100',
scores_1_5: '100',
scores_1_6: '',
scores_2_1: '65',
scores_2_2: '60',
scores_2_3: '50',
scores_2_4: '35',

I'm trying to take this data and convert it into Postgresql query (or mutiple queries).

For example, the line scores_2_4: '35' would become

UPDATE scores SET points = 35 WHERE student_id = 2 AND topic_id = 4

The scores table is a many-to-many join table to connect students and topics.

I suspect that I still have a bit of work to do with my form. I'm probably not sending this data in an ideal way. This is my best solution so far to include a student_id and topic_id along with the teacher's score input.

If this approach is acceptable, then I also need a hint about how to convert all of this data into an update statement.

I'm using current versions of postgresql, nodejs, express, ejs and the node-postgres package.

Thank you in advance for any insight.

First off 50 is at array position zero is incorrect, in Postgres all arrays are indexed from 1 not 0. Secondly how do you know 50 is student_id 1, and topic_id 1. And what are 60, 70, 80, 90,AND 100? What would be the arrays for update scores set points = 20 for student_id = 101 AND topic_id = 952;? — Belayer
– Belayer, Commented Jul 2, 2022 at 1:22
So maybe I've set up my form in a bad way? Is there a way to send student_id and topic_id along with the user's input for the points? — Jeff Zivkovic
– Jeff Zivkovic, Commented Jul 2, 2022 at 2:02
@JeffZivkovic Is that a JSON HTTP API? If so, use the student id and topic id as keys of an object, e.g. {"1": {"1": 50, "2": 60, "3": 70}, "2": {"1": 80, "2": 90, "3": 100}}. This approach will let you send arbitrary ids. — Bergi
– Bergi, Commented Jul 2, 2022 at 3:01
So does the question still stand as given? Else please edit to clarify the task. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 2, 2022 at 3:18
Yes, you really should adjust the form, unless you always want to pass data for all students and all topics. — Bergi
– Bergi, Commented Jul 2, 2022 at 19:59

Bergi · Accepted Answer · 2022-07-03 09:20:25Z

1

This is my best solution so far to include a student_id and topic_id along with the teacher's score input.

Yes, it's fine. You just have to parse the scores_${student_id}_${topic_id} format on the server back into the data structure you expect.

A more customary encoding is to use bracket notation instead of underscores though. Many parsers for application/x-www-form-urlencoded POST bodies can automatically transform this into a nested object, see e.g. Can not post the nested object json to node express body parser and How to get nested form data in express.js?.

 <input type="text" class="score-input" name="scores[<%= student.id %>][<%= topic.id %>]">

I also need a hint about how to convert all of this data into an update statement.

Use multiple UPDATE statements for simplicity:

const { scores } = req.body;
for (const studentId in scores) {
    const studentScores = scores[studentId];
    for (const topicId in studentScores) {
        const points = studentScores[topicId];
        // TODO: check for permission (current user is a teacher who teaches the topic to the student)
        await pgClient.query(
            'UPDATE scores SET points = $3 WHERE student_id = $1 AND topic_id = $2',
            [studentId, topicId, points]
        );
    }
}

You might want to throw in a parseInt or two with proper input validation for the studentId, topicId and points if you need them to be integers instead of strings; otherwise postgres will throw an exception.

answered Jul 3, 2022 at 9:20

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff Zivkovic Over a year ago

I appreciate you following through my various attempts at this. I got it working with underscore notation. I couldn't make the connection with bracket notation because that causes req.body to return a plain array of the scores. Like [[20,30,40],[50,60,70]]. But these are just the scores. There's no student_id or topic_id. (That's the form I was trying to use in my original question, before I totally changed the question). So my question is solved. But I'd love to learn a little more if you have more to say about the bracket notation approach. Cheers!

Bergi Over a year ago

Ah, I see, body-parser with extended: true uses qs which parses integer values in brackets to arrays. If one could pass its options directly there's parseArrays: false, but this appears to be impossible with body-parser. The workaround would be name="scores[u<%= student.id %>][t<%= topic.id %>]" and then slicing off the first character, but then we're basically back to parsing the parameter name ourselves.

Jeff Zivkovic Over a year ago

Thanks for the feedback. That's reassuring that I wasn't missing something basic.

Collectives™ on Stack Overflow

Ejs form, send array of data that can be converted into postgresql update queries

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related