Different results from MySQL in Node.js than in terminal

Question

I am getting all values as null except for one only, this is the statement:

SELECT * FROM posts AS p
LEFT JOIN likes AS l
ON l.postId = p.postId
ORDER BY p.date DESC

The problem happen only on mysql and mysql2 in node.js, while on terminal it works perfectly showing all results as I expected.

Result in mysql/mysql2x node.js:

{
"error": false,
"data": [{
    "user_id": null,
    "postId": null,
    "type": "image/png",
    "url_thumb": "https://picsum.photos/id/200/110/90",
    "url_file": "https://picsum.photos/id/1074/210/190",
    "title": null,
    "content": null,
    "category": "test",
    "private": 1,
    "date": null,
    "active": "1",
    "id": null,
    "like_status": null
},
{
    "user_id": "u-8723Ml723aa3723",
    "postId": "post-8374NK7243NK823at6",
    "type": "image/png",
    "url_thumb": "https://picsum.photos/id/200/110/90",
    "url_file": "https://picsum.photos/id/1074/210/190",
    "title": "Some title",
    "content": "Some content",
    "category": "test",
    "private": 1,
    "date": 1619398758,
    "active": 1,
    "like_status": 1
},
{
    "user_id": null,
    "postId": null,
    "type": "image/png",
    "url_thumb": "https://picsum.photos/id/200/110/90",
    "url_file": "https://picsum.photos/id/1074/210/190",
    "title": null,
    "content": null,
    "category": "test",
    "private": 1,
    "date": null,
    "active": "1",
    "id": null,
    "like_status": null
}]}

con.query(
  "SELECT * FROM posts AS p "+
  "LEFT JOIN likes AS l "+
  "ON l.postId = p.postId "+
  "ORDER BY p.date DESC ",
  function(err, b){
    con.end();
    if(err){
      console.log(err)
    }
    else{
     var response = {};
     response.error = false;
     response.data = b;
     res.send(JSON.stringify(response)); 
    }
  }
);

What am I doing wrong here?

Seems that the problem is when parsing the result or the mysql statement is wrong?

XMachina · Answer 1 · 2021-09-06T09:32:03.833

Ok, I solved it, the problem was that both tables contains columns with same name, so I had to fixed it using aliases, and that makes that at the end, the JSON result gets not overwritten by the Object structure which in fact all the key names must to be unique, this could be a good feature to add into the nodejs mysql/mysql2 library by adding prefixes to allow to get "duplicated" key names.

https://stackoverflow.com/questions/21832701/does-json-syntax-allow-duplicate-keys-in-an-object#:~:text=ECMA%2D404%20%22The%20JSON%20Data,an%20object%20SHOULD%20be%20unique.

The link above helped me to understand the nature of the problem and this is how it got "fixed"...

SELECT p.*, l.postId AS post_liked FROM posts AS p
JOIN likes AS l
WHERE l.user_id = "userUid"
ORDER BY p.date DESC

Different results from MySQL in Node.js than in terminal

1 Answers1