Timing issue with mysql query in NodeJS

Question

I'm using mysql module in nodejs.

I want to do something like this, but the problem is that the callback of query is async and I can't fill the result array correctly.. How can I do? Thanks!

function fn(callback) {
    client.query(
        'SELECT * FROM imbarcazioni',
        function select(err, ships) {
        if(err) {
            throw err;
        }

        ships.forEach(function(ship) {
            client.query(
                'SELECT * FROM storico_imbarcazioni WHERE id_imbarcazione=' + ship.id,
                function select(err, hist) {
                    ship.history = hist;
                }
            );
        });

        callback(hist);
    });
}

Linus Thiel · Accepted Answer · 2012-04-20 12:27:32Z

3

As usual, I recommend async for these kinds of things. Here, you could use async.map:

function selectHistory(ship, callback) {
    client.query('SELECT * FROM storico_imbarcazioni WHERE id_imbarcazione = ?', [ship.id], function(err, history) {
        if(err) return callback(err);
        ship.history = history;
        callback(null, ship);
    });
}

client.query('SELECT * FROM imbarcazoni', function(err, ships) {
    async.map(ships, selectHistory, function(err, ships) {
        // ships is an array with each ship along with it's history
    });
});

That said, for your particular example, I would rather use a JOIN and handle it in SQL:

SELECT * FROM imbarcazioni
LEFT JOIN storico_imbarcazoni ON storico_imbarcazoni.id_imbarcazione = imbarcazioni.id

edited Apr 20, 2012 at 12:27

answered Apr 20, 2012 at 11:34

Linus Thiel

39.3k9 gold badges112 silver badges104 bronze badges

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

5 Comments

frx08 Over a year ago

thanks, it's what I'm looking for! for the left join i tried but without success..

Linus Thiel Over a year ago

@frx08: Thanks for the fix! I fixed the SQL statement (for the JOIN) -- there were a few errors. You should read up on JOINs, and here is a nice visualization by StackOverflow co-founder Jeff Atwood.

frx08 Over a year ago

the problem for the sql way is that I want to have a single ship object that contains the own history object, while with the sql query I get many duplicate objects..

Linus Thiel Over a year ago

Yes, you will have to normalize the results to fit your requested model, but the performance win is huge and, well, JOIN is basically what relational databases are all about.

andyengle Over a year ago

Thanks for your response, Linus. This helped me with something I was getting hung up on. Now my code is nice and clean. And it works. :)

Collectives™ on Stack Overflow

Timing issue with mysql query in NodeJS

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related