hasMany on same table

[czkody]

Trying to solve tree heirarchy through orm.

CREATE TABLE `category` (
  `id` int(11) NOT NULL,
  `name` varchar(1024) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `category_tree` (
  `predecessor_id` int(11) NOT NULL,
  `successor_id` int(11) NOT NULL,
  `distance` int(11) NOT NULL,
  PRIMARY KEY (`predecessor_id`,`successor_id`),
  KEY `FK_category_tree_successor` (`successor_id`),
  CONSTRAINT `FK_category_tree_predecessor` FOREIGN KEY (`predecessor_id`) REFERENCES `category` (`id`),
  CONSTRAINT `FK_category_tree_successor` FOREIGN KEY (`successor_id`) REFERENCES `category` (`id`)
);

Defined model

var db = config.db;
var Category = db.define('category', {
    //properties
    id : Number,
    name : String
},{
    //options
}); 

Category.hasMany("successors", {distance : Number}, Category, {reverse: 'predecessors', mergeTable: 'category_tree', mergeId: 'predecessor_id', mergeAssocId: 'successor_id'});

Executing this:

Category(6).getSuccessors({distance: 1}, ['name', 'A'], function(err, categories) {
    res.json(categories);
});     

generates:

SELECT * FROM `category` WHERE `distance` = 1 AND `category_tree`.`predecessor_id` = 6 ORDER BY `name` ASC 

[dresende]

Thank you for your example, I'll test and debug as soon as I have time.

[dresende]

The problem is your ordering (['name', 'A']). If you remove this argument it works. I'm going to see if it's possible to add ordering to the method.

[czkody]

You're right, I didn't try. It works fine without ordering, but with ordering it would be better. :) Thank you, you are doing great work.

[dresende]

Thank you. You can try it now :)