-2

I am reading data from a MYSQL database using the following SQL:

 SELECT GROUP_CONCAT(MemberMemberId SEPARATOR ',')
 AS
 MemberMemberId
 FROM 
 member_events
 WHERE
 event_date = "2000-01-01" 
 AND 
 EventEventId= 1;

This SQL is embedded as raw SQL in a Sequelize query, itself part of an Express router, as follows:

router.get("/membernames", async (req, res) => {
  const memberNames = await sequelize.query(
    'SELECT GROUP_CONCAT(MemberMemberId SEPARATOR ",") AS MemberMemberId FROM member_events WHERE event_date = "2000-01-01" AND EventEventId= 1;',
    //    'SELECT m.member_id, m.forename, m.surname FROM members m INNER JOIN member_events me ON m.member_id = me.MemberMemberId WHERE event_date = "2000-01-01" AND EventEventId= 1;',
      { 
        type: sequelize.QueryTypes.SELECT 
      }
    )
      res.json(memberNames);
});

When accessing that router via an http request in Insomnia, I get the following array:

[
    {
        "MemberMemberId": "1,2,7,3"
    }
]

What I need for my usecase is a simple list of numbers as follows:

"1,2,7,3"'

Is the correct way for me to get the data in to the format that I need, to extract it from the database as I have done, then use some form of Javascript string manipulation in my React frontend (e.g. .replace) to change it to the format I need? Or is there another approach I should be investigating?

Jess
  • 151
  • 12
  • 1
    What's the point of using concat to convert the query results into a string in the first place if you need them as an array? Just select the ids with a normal SQL query and return that. – Guy Incognito Mar 04 '23 at 10:08
  • I'm not sure I follow you. If I select member_id (rather than using concat....) then I get the following output: [ { "MemberMemberId": 1 }, { "MemberMemberId": 2 }, { "MemberMemberId": 7 }, { "MemberMemberId": 3 } ] But that's also not the format that I need – Jess Mar 04 '23 at 10:15
  • 1
    `res.json(memberNames.map(item => item.MemberMemberId))` – Guy Incognito Mar 04 '23 at 10:16
  • 2
    Also note that doing raw queries with Sequelize isn't really the proper way to do it. Sequelize is just dead weight if you're not really using it, raw queries can be done directly with the MySQL library as well. – Guy Incognito Mar 04 '23 at 10:19
  • 1
    Does this answer your question? [Sequelize return array with Strings instead of Objects](https://stackoverflow.com/questions/33576129/sequelize-return-array-with-strings-instead-of-objects) – pilchard Mar 04 '23 at 11:02
  • No, that is using Sequelize code to change what is output. Whilst that might be one way to approach this overall requirement, it was not the approach I had taken - I used raw SQL - mainly because the more I use Sequelise, the less capable I'm finding it, and the more time I'm spending working out how to work around its limitations. If I was starting again, I wouldn't use it at all. – Jess Mar 04 '23 at 11:07
  • regardless, you'll note that all the answers actually return an array of objects which is then mapped to an individual property `.map(account => account.Name);` also covered in this duplicate [From an array of objects, extract value of a property as array](https://stackoverflow.com/questions/19590865/from-an-array-of-objects-extract-value-of-a-property-as-array) – pilchard Mar 04 '23 at 11:47
  • Yeah, that answer seems closer, although my question was about how to output as a list - a string - rather than as an array. So it partially answers it. As an aside, as a newbie I'd have never located that question you found, as I simply lack the knowledge/language to even ask the right question to find it. But if my question is deemed a duplicate and needs removing, that's fine with me. – Jess Mar 04 '23 at 12:16
  • No need to remove it. Flagging duplicates helps to connect related questions/answers often with a longer history of answers or a slightly different focus. – pilchard Mar 04 '23 at 13:44

1 Answers1

0

Per Guy's comment, the solution was to iterate through the array before the data was returned to the http request. As follows:

    res.json(memberNames.map(item => item.MemberMemberId))

That output the data as a list inside an array, as follows:

    [
        1,
        2,
        7,
        3
    ]

So before using it, I applied .toString() to it as well, which dropped the square brackets and gave me the data in the format I needed.

Jess
  • 151
  • 12