FeathersVuex returning one row duplicated from a service with an associated table

[trioinsight]

Description
First table: products
Product_id (PK), Product_code, ....
second table: productdescriptions
id (PK), product_code, lang_code, name,....

products model: products has many productdescriptions , FK: 'product_id'
products hook: included productdescriptions , raw:true, and returned context
using feathers-sequelize & mysql

Steps to reproduce

used different techniques renderless componenets, example from common patterns Actions return reactive store records
also tried this in vue component from model docs
created() {
const { Product } = this.$FeathersVuex.api
Product.find({ query: {product_id:6} }).then(/* ... */)
}
computed: { todoss () {
const { Product } = this.$FeathersVuex.api
return Product.findInStore({ query: {} }).data
}

Expected behavior

it should return all products with different lang codes example :
"data": [
{
"product_id": 6,
"product_code": "T0006UROBG",
.....
"Productdescriptions.id": 7,
"Productdescriptions.product_id": 6,
"Productdescriptions.lang_code": "de",
"Productdescriptions.product": "Toshiba 32C120U 32" Class 720P HD LCD TV"
},
{
"product_id": 6,
"product_code": "T0006UROBG",
.....
"Productdescriptions.id": 8,
"Productdescriptions.product_id": 6,
"Productdescriptions.lang_code": "en",
"Productdescriptions.product": "Toshiba 32C120U 32" Class 720P HD LCD TV"
}
]

to double check server is correctly configured
http://localhost:3030/products?product_id=6 returns expected result
also using feathers-client socket directly in vuejs returned the expected result
socket.emit('find', 'products', { product_id:6}, (error, data) => {.....

Actual behavior

[{"product_id":6,"product_code":"T0006UROBG",...
"Productdescriptions.id":8,"Productdescriptions.product_id":6,"Productdescriptions.lang_code":"en","Productdescriptions.product":"Toshiba 32C120U 32" Class 720P HD LCD TV"},{"product_id":6,"product_code":"T0006UROBG",.....
,"Productdescriptions.id":8,"Productdescriptions.product_id":6,"Productdescriptions.lang_code":"en","Productdescriptions.product":"Toshiba 32C120U 32" Class 720P HD LCD TV"}]

System configuration

"@feathersjs/authentication-client": "^4.4.3",
"@feathersjs/feathers": "^4.4.3",
"@feathersjs/socketio-client": "^4.4.3",
"feathers-hooks-common": "^4.20.7",
"feathers-vuex": "^3.2.1",
"vue": "2.5.22",
NodeJS version:
v12.13.0

feathers server:
"@feathersjs/authentication": "^4.4.3",
"@feathersjs/authentication-local": "^4.4.3",
"@feathersjs/authentication-oauth": "^4.4.3",
"@feathersjs/configuration": "^4.4.3",
"@feathersjs/errors": "^4.4.3",
"@feathersjs/express": "^4.4.3",
"@feathersjs/feathers": "^4.4.3",
"@feathersjs/socketio": "^4.4.3",
"@feathersjs/transport-commons": "^4.4.3",
"compression": "^1.7.4",
"cors": "^2.8.5",
"feathers-authentication-hooks": "^1.0.1",
"feathers-sequelize": "^6.1.0",
"helmet": "^3.21.2",
"mysql2": "^2.0.2",
"sequelize": "^5.21.3",
"serve-favicon": "^2.5.0",

[J3m5]

Can you inspect the store state in the vue dev tools to see if the records are duplicated?

[J3m5]

And does the records have an Id field? Like id or _id.
If not that could explain why you have duplicated records.

[trioinsight]

Can you inspect the store state in the vue dev tools to see if the records are duplicated?

Hi and thanks for the reply, the store state is the one i represented in the actual behavior section

[trioinsight]

And does the records have an Id field? Like id or _id.
If not that could explain why you have duplicated records.

id is set to 'product_id'
static modelName = 'Product'
const servicePath = 'products'
const servicePlugin = makeServicePlugin({
idField:'product_id',
Model: Product,
service: feathersClient.service(servicePath),
servicePath
})

[trioinsight]

the actual issue is not the duplication it self , the fact that the other language row/object 'de' as explained in the "expected behavior" section that is missing , while substituting the expected result with a duplicate of the first result
i did not test to see if in the productdescriptions table if i had 3 or more lang_codes , will feathers-vuex duplicate one result to 3 or more times or not
shall test and let you know
i wish if some one can reproduce this issue within the config i stated in the issue details

another Q i have in mind does feathersvuex strict to get unique IDs as a result , since the associated (joined) tables result obviously does not have a unique ID ,

[marshallswain]

I don't quite have full clarity of what's going on here. If you have the idField set to product_id and you are getting two records back with product_id: 6, then that's a problem, for sure. You should never have two records with the same idField. If two records have the same unique identifier, then they are treated as the same record. You shouldn't get two records back in the results unless something other than product_id is set as the idField.

[trioinsight]

I don't quite have full clarity of what's going on here. If you have the idField set to product_id and you are getting two records back with product_id: 6, then that's a problem, for sure. You should never have two records with the same idField. If two records have the same unique identifier, then they are treated as the same record. You shouldn't get two records back in the results unless something other than product_id is set as the idField.

Hi , Thanks Marshal for your explanation
let me explain more, two tables one has the essential product info PK is Product_ID and acts as an FK to second table too, the other has id and its job is to store product details in multi lang (columns product and lang_code for simplicity)

select a.product_id, a.product_code, b.product, b.lang_code from products as a inner join productsdescriptions as b ON a.product_id = b.product_id

since am joining the two tables i will not get a unique product_id because the same product can be stored in different langs, now :
the select statement AND using REST call AND using feathers-sockets on the client Generate the same result which is what i would expect it to be (detailed in the expected section)

Feathers-Vuex is not

[marshallswain]

since am joining the two tables i will not get a unique product_id because the same product can be stored in different langs, now :

If they have the same product_id, you should only be able to pull one record from the store with product_id: 6. The code in "Actual behavior" seems to indicate that you are somehow getting two separate records. Is that accurate?

[trioinsight]

If they have the same product_id, you should only be able to pull one record from the store with product_id: 6. The code in "Actual behavior" seems to indicate that you are somehow getting two separate records. Is that accurate?

Actually i should be pulling 2 records with the same Product_id but 2 different Lang_code (en, de) , what i am getting in the store is the same product_id for the same lang_code (en,en)
so in the actual behavior i am getting the same record for one language duplicated (en, en)

but it should be 2 records for the same product with different languages (en,de,....)

[marshallswain]

const servicePlugin = makeServicePlugin({
idField:'product_id',
Model: Product,
service: feathersClient.service(servicePath),
servicePath
})

You have product_id setup as the idField. By your specifications you want two records with the same product_id. What I'm telling you is that, by design, you should not be able to have two separate records with the same product_id while using feathers-vuex. If you have two records, then this is a bug.

[marshallswain]

And the bug is that you should only see one record.

[marshallswain]

Feathers-Vuex does not have any method of interpreting the results of an SQL join when multiple records have the same id. The solution in your case would be to either

• massage the data to fit into a record with a single id, or
• join the records on a sub-key inside the original instead of merging them into a flat array.

The second solution is how I would personally handle this. But I don't know the rest of your app.

[marshallswain]

Actually, the scenario where having duplicated records is NOT a bug is if you are directly using the results of a find action. This is probably your situation. When the second record arrives in the results, it basically updates the first record's properties because they have the same id. In the same way that you cannot have a duplicate id in a database, Feathers-Vuex will not support two records with the same id.

[trioinsight]

Actually, the scenario where having duplicated records is NOT a bug is if you are directly using the results of a find action. This is probably your situation. When the second record arrives in the results, it basically updates the first record's properties because they have the same id. In the same way that you cannot have a duplicate id in a database, Feathers-Vuex will not support two records with the same id.

I am surprised that feathers-vuex acts as a filtering proxy to ensure uniqueness of records , in my humble opinion it should behave as a transparent channel , same as it would be the case of calling a REST API , or Feathers-Sockets

[trioinsight]

Feathers-Vuex does not have any method of interpreting the results of an SQL join when multiple records have the same id. The solution in your case would be to either

• massage the data to fit into a record with a single id, or
• join the records on a sub-key inside the original instead of merging them into a flat array.

The second solution is how I would personally handle this. But I don't know the rest of your app.

The second solution is feasible with mongodb , incase of using feathers-sequelize with association i think it means extra after hook shaping , which means an overhead

[marshallswain]

I appreciate your feedback. The reason this will not work is that feathers-vuex provides an in-memory database which allows it to work for offline scenarios. It must strictly adhere to the requirement of unique ids in order for this to work. It expects relationships to be cleanly separated.

I imagine that the overhead from shaping even thousands of records would be immaterial.

You could also make an additional request for the relationships. Using feathers-shallow-populate for this would result in exactly one query for all of the related data. Depending on the location of your database server in relation to your application server (and other factors affecting database performance), either in-hook shaping or the additional request could be faster.

[marshallswain]

OR... you could set another field as the idField, which would allow the duplicates to be stored as is.

[marshallswain]

That last idea is definitely not the way I would handle any relationship because it's a workaround that would likely lead to frustration with more workarounds in the future.

[trioinsight]

I really appreciate your help, i shall check and hope to have a viable solution back

[trioinsight]

OR... you could set another field as the idField, which would allow the duplicates to be stored as is.

this is a clever idea, i guess , setting the PK as id in the first table while maintaining product_id as an FK might get things sorted i hope
edit : i dont think so after a sec thought

[trioinsight]

I believe the most logical solution, after knowing more how feathers-vuex works and with minimal overhead , is to send the request to server to fetch a single language at a time , according to user interaction with a combo to select language.

[marshallswain]

Oh, setting the primary key as the idField is definitely recommended, no matter which other solution you choose.

It will really simplify the way you handle the related records if you keep them in a place where they can be easily separated out, not tightly coupled in the response. If you have the schema more like this:

const product = {
   id: 1,
   descriptions: [
      { id: 1, lang: 'en', text: 'Back pack' },
      { id: 2, lang: 'es', text: 'Mochila' }
   ]
}

It becomes really easy to edit the description records when you set them up with their own service and Model. At that point, if you put a setupInstance on the Product Model that looks like this:

setupInstance(product, { models }) {
   if (product.descriptions) {
      product.descriptions = product.descriptions.map(d => models.api.ProductDescription(d))
   }
   return product
}

This will allow you to edit a description using model methods:

const english = product.descriptions.find(d => d.lang === 'en')
english.description = 'Backpack'
english.save() // sends request to patch the english description.

[trioinsight]

Feathers-Vuex does not have any method of interpreting the results of an SQL join when multiple records have the same id. The solution in your case would be to either

• massage the data to fit into a record with a single id, or
• join the records on a sub-key inside the original instead of merging them into a flat array.

The second solution is how I would personally handle this. But I don't know the rest of your app.

just as a reference , and if any one else needed this assuming feathers-sequelize, I have tested setting raw: false , in the before hook on server , it will shape the result according to your suggested 2nd point (i.e join the records on a sub-key inside the original instead of merging them into a flat array.)

 before: {
    all: [ /* authenticate('jwt') */ ],
    find: [
      context => { 
        const sequelize = context.app.get('sequelizeClient'); 
        const { productdescriptions } = sequelize.models; 
        context.params.sequelize = { 
        include: [ 
        { model: productdescriptions, as:'Productdescriptions', /*attributes:['product','lang_code'],*/ },
        ],
        raw: false, 
        }; 
        
        return context; 
        },
    ],

[marshallswain]

Ah. That makes things much simpler. Thanks for the update, @trioinsight!

[marshallswain]

I've added a note about "handling Sequelize joins" to the common patterns section of the docs. It links to this issue. Thanks @trioinsight for sharing with the community!