MerkisL MerkisL - 2 months ago 10
MySQL Question

Comparing data inside "for" loop

So I've been trying to solve this on my own as it looks very simple, but I couldn't do it and nothing that could help me with it came out on here. So I decided to just ask. I'm using ExpressJS btw.

I have MySQL table called 'notes' with following schema:

+----------+
| Field |
+----------+
| id |
| title |
| body |
| author |
| date |
| time |
+----------+


So after fetching this data from db, I'd like to have it on page like this:

- unique date
- title
- body
- author

- title
- body
- author

- unique date
- title
- body
- author


As each row has its own date and dates for some articles are same, I'd only like to show date only once on page and show only title, body, author and time if it's same date. Sort of like timeline.

What I've tried is to compare notes inside "for" loop, like this (in my notes.ejs):

<% for(i=0; i<notes.length; i++) {%>
<% if(notes[i].date != notes[i+=1].date) {%>
<li><%= notes[i].title %></li>
<%} %>
<%} %>


However, i+=1 sets new value for i in the current iteration and I can't correctly display results.

This is probably very simple, but I just can't get it right.

Answer

You can certainly do this inside the template, but I really wouldn't suggest it. I'd rather see you do the merging / grouping before passing it to the view / templater.

Do you have access to underscore / lodash outside of the templater? _.groupBy() would be ideal for a problem like this.

// this assumes that your sql results are actual dates.
// if they aren't, skip the `toISOString()`
let grouped = _.groupBy(sqlResults, result => result.date.toISOString());
let dates = _.unique(sqlResults.map(result => result.date.toISOString()));
// or just read dates from the `grouped` keys, if you dont want them separately
let data = { grouped, dates };
return res.render('view.ejs', data);

With the template (or something similar to this):

<% for (let date of dates) {%>
  <h4><%= date %></h4>
  <ul>
    <% for (let obj of grouped[date]) {%>
      <li><%= obj.title %></li> 
    <%} %>
  </ul>
<%} %>