How to select LIMIT mysql with bookshelf nodeJS

Question

I'm using bookshelf plugin of node JS to execute mysql query. But i don't know how to execute LIMIT query. Like that

SELECT * from tableName LIMIT 2;

My connection bookshelf:

Bookshelf.mysqlAuth = Bookshelf.initialize({
    client: client,
    connection: {
      host     : host,
      user     : user,
      password : password,
      database : database
    }

and the data method:

bookshelf.Img = Bookshelf.Model.extend({
    tableName: 'image'
  });

You can use this when calling for connection:

  var qb = data.Img.query();
  qb.where('img_md5', '=', imgMD5save).update({img_size: fileLength, img_local_url: folder, img_type: fileType, img_downloaded: 1}).then(function(){});

I've tried with

 qb.limit(5).then(function(){});

but it fired an error

Possibly unhandled TypeError: Cannot call method 'apply' of undefined

Please suggest any solutions for that. Thank you!

Answer 1

After reviewing the operations you're trying to execute on your qb model, you're trying to run an UPDATE query with a LIMIT clause.

• LIMIT can be used with UPDATE but with the row count only.
• Reference this StackOverflow question
• Instead of using a LIMIT clause on your update, I would recommend strengthening your WHERE clause for an UPDATE query.

If your intent is just to run a SELECT query with a LIMIT clause, please look at the code snippet I've provided below:

var qb = data.Img.query();
  qb
    .select('*')
    .from('image')
    .where('img_md5', '=', imgMD5save)
    .limit(2)
    .then(function(result){
        return result;
    })
    .catch(function(error) {
        throw new error;
    });

Answer 2

According to the documention you can write it this way:

bookshelf.Img.query(function(qb) {
    qb.offset(30).limit(10);
})
.fetchAll()
.then(...);

This will return a collection of 10 images starting from the 30th image.