How can I retrieve Many-to-Many data

[burakkilic]

Hi;

I have many-to-many categories-items relation.

I want to retrieve the items that belongs to a category.

I make a request with "/items?CategoryId=1"

But I am getting

"ER_BAD_FIELD_ERROR: Unknown column 'Item.CategoryId' in 'where clause'"

because relationship is saved in category_items table.

How can I retrieve the associated data in many-to-many relationships.

[mbroadst]

Can you please provide some sample code (we can do this offline if you'd like). I want to fix the two issues you have open, but it looks like maybe it's better for me to create an entire test suite around it. If you can just provide me your models I can make the tests.

For many-to-many support, I think it makes sense to have an extended URL format that looks something like: https://localhost:3000/api/category/item/ where Category.hasMany(Item). This definitely hasn't been implemented yet, but I think is a good idea.

[burakkilic]

module.exports = function(sequelize, DataTypes) {

var Category = sequelize.define('Category', {
    order: DataTypes.INTEGER,
    published: DataTypes.BOOLEAN
}, {
    associate: function(models) {
        Category.hasMany(models.Category, {
            as: "SubCategories",
            foreignKey: "ParentId"
        });
        Category.hasMany(models.Item);
        Category.hasMany(models.CategoryTranslation);
    },
    tableName: 'categories'
});
return Category;
};


module.exports = function(sequelize, DataTypes) {

var Item = sequelize.define('Item', {
    code: DataTypes.STRING,
    price: DataTypes.DECIMAL(10,2),
    sortOrder: { type: DataTypes.INTEGER, defaultValue: false},
    image: DataTypes.STRING
}, {
    associate: function(models) {
        Item.belongsToMany(models.Category);
        Item.hasMany(models.ItemTranslation);
    },
    tableName: 'items'
});

return Item;
};

[mbroadst]

@burakkilic thank you for the models, I will be out of town for the next ten days but will try to incorporate these into tests asap.

[burakkilic]

Thanks @mbroadst

[dchester]

We could theoretically add support for something like /categories/:category_id/items, but what about just making it work how @burakkilic expected? We could augment existing routes to support associations, so for example /items?category_id=1 would just do the right thing. That seems simpler to me at least.

Either way, I wonder if we might want to require that users explicitly opt-in to enable/expose these traversals with something like:

var items = epilogue.resource({
    model: Item,
    endpoints: ['/items', '/items/:id'],
    associations: [Category]
});

[mbroadst]

Well, I haven't had a chance to take a good look at it yet, but to a certain degree just passing the right models into the "includes" should work as-is. I might be reading the initial question incorrectly though. I would try:

var items = epilogue.resource({
    model: Item,
    endpoints: ['/items', '/items/:id'],
    include: [Category]
});

What I'd ideally like to do in the future for all associations is:

• drop the need for adding "includes" (though still support it the way it currently works)
• opt-in for "auto associations" (maybe just "autoAssociate: true" in the factory or something), since we already have all the relevant associative information from the model the resource is created for.

[mbroadst]

@burakkilic ping, have you tried my suggestion?

[brettstack]

@mbroadst could you create an issue for us to track the progress of the association routing feature?

[mbroadst]

@breandr looks like someone beat both of us to it: #34. I'll track the work there