I've got feathers.js functioning with mysql via sequelize. This is working, I can collect data from tables. Next step is to define 'joins' in the models.
I have a table with a column 'status_id' and 'country_id'. These columns reference to an id in a metadata table. In SQL I would right:
SELECT status.description, country.description, detail
FROM details
INNER JOIN metadata status
ON (details.status_id = status.id AND status.type = 'status'
INNER JOIN metadata country
ON (details.country_id =country.id AND country.type = 'country')
This metadata table won't be big in this case so hence this approach. It does give flexibility I need.
What do I need to do to make this in feathters.js?
Ok I've done some adjusting of the code. To keep things readable for everyone, I'm going to step over to actual table examples. I have a table 'sections' and a table 'categories' (this is a more simple example). The section has a foreign key with the category. So this is what I've done for that so far:
category-model.js
classMethods: {
associate() {
category.hasOne(sequelize.models.sections, {
as: 'category',
foreignKey: 'category_id'
});
},
},
section-model.js
classMethods: {
associate() {
section.belongsTo(sequelize.models.categories, {
allowNull: true
});
},
},
services\index.js
...
app.set('models', sequelize.models);
...
Object.keys(sequelize.models).forEach(function(modelName) {
if ("associate" in sequelize.models[modelName]) {
sequelize.models[modelName].associate();
}
});
I'm using Jetbrains webstorm in this case. So I did a npm start, and my table now has the correct foreign key, so that part works. Also, the display of the data is still correct. The query which gets the sections still is working. If I had a different coding in the index.js, then npm start didn't fail, but the sections query failed.
Next is up: hooks. This is where I'm getting some confusion now. Some sites say it's in the 'find definition' (let's call it that for now, but this is in my vue component mounted section). Then you it's explained, well showed, the include is in that part, but it does nothing, well the code still runs, but I don't see category information when I get the sections through postman. I will then have e.g.
serviceSections.find({
include: [{
model: serviceCategories.Model
}],
query: {
$sort: {
section_description_en: 1
}
}
}).then(page => {
page.data.reverse();
this.listSections = page.data;
})
serviceCategories is defined as "appFeathers.service('categories');". As mentioned it does nothing. So jumping back to the explanation I had gotten here, it says '.. from a before hook ..'. I found the hooks\index.js file, for categories and for services. But here I make mistakes. I made this adjustment first in the categories on then in the sections one
exports.before = {
...
find: [{
function (hook) {
if (hook.params.query.include) {
const AssociatedModel = hook.app.services.category.Model;
hook.params.sequelize = {
include: [{ model: AssociatedModel }]
};
}
return Promise.resolve(hook);
}
}],
...
};
This is giving code 500 fn.bind error.
Just thought to post the progress, this doesn't mean I stop looking for the last step (or a missing step).
I forgot one thing. My check to see if it is complete is to open F12 in chrome, go to the VUE plugin and expand 'listSections' which is the result of "serviceSections.find". I expect to see category column in it but maybe that is the wrong expectation. I also don't see a 'join' in the select of my debugger
Edit some time later:
Okay, so I messed around. Eventually I also came across this post on how to retrieve data from many to many relationships. Reading this, I figured the intention on adjusting the "hooks\index.js" was correct, but that meant my code wasn't. So trying different combinations of that post, and the tips provided above, I now have this
section\hooks\index.js
...
exports.before = {
all: [],
find: [
getCategory()
],
get: [
getCategory()
],
create: [],
update: [],
patch: [],
remove: []
};
...
function getCategory() {
return function (hook) {
const category = hook.app.services.categories.Model;
hook.params.sequelize = {
include: [{ model: category }]
};
return Promise.resolve(hook);
};
}
This is working in postman with GET, because of putting it in the 'get' part of 'before' section, and it's working in VUE for I put the function in the 'find' part of 'before'.
Mutliple Joins
Okay, I needed multiple joins in to the 'sections'. I have a status too. This is coming from my metadata table. So this meant making the same association to sections in the metadata-model. I did it like this:
metadata-model.js
classMethods: {
associate() {
metadata.hasOne(sequelize.models.sections, {
as: 'satus',
foreignKey: 'status_id',
targetKey: 'status_id'
});
}, },
Here I started to always put in the foreignKey attribute. The targetKey is the name of the column in the other tabel. Handy if you need to change it. The 'as' attribute is an alias, I like to use this nearly always at least when using it multiple times. In the section-model I made changes to.
section-model.js
classMethods: {
associate() {
section.belongsTo(sequelize.models.categories, {
allowNull: false,
as: 'category'
});
section.belongsTo(sequelize.models.metadata, {
allowNull: false,
as: 'status'
});
}, },
To finalize this, I changed the hooks function. I first tried too functions, but that didn't work, so I merged the two.
section\hooks\index.js
function getRelatedInfo() {
return function (hook) {
hook.params.sequelize = {
include: [
{
model: hook.app.services.categories.Model,
as: 'category'
},{
model: hook.app.services.metadata.Model,
as: 'status',
where: {
type: 'status'
}
}
]
};
return Promise.resolve(hook);
};
}
As you see I changed the function name. It is important to use the alias again, it didn't work other wise. In the metadata part, I put a filter. I don't want to load the entire table when looking up. Okay, I join on an id so it really is one on one, but this way if for some reason the metadata entry changes to a different type, I still have the selection on 'status' and can make a warning of incorrect rows.
The final part would be if you want the inner or left outer joins. I triggered that by the 'required: true' attribute within the hooks sections.
VUE.js part
The last part is to push it to a vue component. I do that in the mounted section. I have this code for that.
const socket = io();
const appFeathers = feathers()
.configure(feathers.socketio(socket))
.configure(feathers.hooks());
const serviceSections = appFeathers.service('sections');
const serviceArticles = appFeathers.service('articles');
const serviceMetadata = appFeathers.service('metadata');
...
mounted() {
serviceArticles.find({
include: [{
model: serviceMetadata.Model,
as: 'country',
query: {
$select: [
'country_icon'
]
}
}],
query: {
$sort: {
article_description_en: 1
},
$select: [
'id',
['article_description_en', 'article_description'],
'article_length',
'article_ascend',
'article_code'
]
}
}).then(page => {
this.listTrails = page.data;
})
}
What I do here is filter undesired columns from the array. I also rename a few. The '*_en' ones are multilingual, so I need to use a variable for it. The include is repeated again to get related columns from the joins.