CODEWITHSUNDEEP
mysqlnode.jsormbookshelf.js
Mauricio Salas
Mauricio Salas

Reputation: 25

Undefined binding detected when compiling SELECT query:

I'm new at bookshelf.js and i'm trying to build a one to many relation from two tables and when i want to fetch the data from postman it throws me the following error:

"Undefined binding(s) detected when compiling SELECT query: select customer_order_line.* from customer_order_line where customer_order_line.id_order_line in (?)"

here is my data model for customer_order:

 var customer_order = db.Model.extend({
    tableName: 'customer_order',
    hasTimestamps: true,

    orders : function() {
        return this.hasMany('order_line', 'id_order_line');
    },

    payment : function() {
        return this.belongsTo('payment','id_payment');
    }

})

module.exports = db.model('customer_order', customer_order);

customer_order_line:

var order_line = db.Model.extend({
    tableName: 'customer_order_line',


    product : function(){
        return this.belongsTo('Product','id_products');
    },

    order : function(){
        return this.belongsTo('customer_order','id_customer_order');
    }
})

module.exports = db.model('order_line', order_line);

and here's my function that fetches the data:

 getOrders: function(req, res, next) {
    Customer_orders.forge()
    .fetch({withRelated: ['orders']})
    .then(function (collection){

      res.json({
        error : false,
        data : collection.toJSON()
      });
    })
    .catch(function (err){
      res.status(500)
      .json({
        error : true,
        data: {message: err.message}
      });
    });
  }

mysql tables:

CREATE TABLE customer_order (
            id_customer_order INT AUTO_INCREMENT NOT NULL,
            id_employee INT NOT NULL,
            id_payment INT NOT NULL,
            total DECIMAL(10,2) NOT NULL,
            status NUMERIC(11) NOT NULL,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL,
            PRIMARY KEY (id_customer_order)
);

CREATE TABLE customer_order_line (
            id_order_line INT AUTO_INCREMENT NOT NULL,
            id_customer_order INT NOT NULL,
            id_products INT NOT NULL,
            cost_without_taxes DECIMAL(10,2) NOT NULL,
            cost_with_taxes DECIMAL(10,2) NOT NULL,
            final_price DECIMAL(10,2) NOT NULL,
            quantity NUMERIC(11) NOT NULL,
            total_without_taxes DECIMAL(10,2) NOT NULL,
            total_with_taxes DECIMAL(10,2) NOT NULL,
            total DECIMAL(10,2) NOT NULL,
            status NUMERIC(11) NOT NULL,
            PRIMARY KEY (id_order_line)
);

Upvotes: 2

Views: 3272

Answers (1)

Bharathvaj Ganesan
Bharathvaj Ganesan

Reputation: 3194

I have modified your customer_order model as shown below

var customer_order = db.Model.extend({
tableName: 'customer_order',
hasTimestamps: true,

    orders : function() {
       return this.hasMany('order_line', 'id_customer_order');
       //this must contain the foreign key present inside the order_line table and not id_order_line
    },

    payment : function() {
       return this.belongsTo('payment','id_payment');
    }

})
module.exports = db.model('customer_order', customer_order);

The primary key and foreign key must be explicitly given while specifying relationship, if your column name don't follow the naming conventions of bookshelf.js.

Go through this tutorial blog nodejs with bookshelfjs mysql by Qawelesizwe Mlilo.

Upvotes: 1

Related Questions