Suppose i have inserted two rows ( id: 1, attribute_id: 1 ) , ( id: 2, attribute_id: 2) in the `relationship_attribute` table
2021-06-01 15225, 2021
akashgp09
next i want to insert the attribute values associated with these two id's : 1 and 2 in the table `relationship_attribute_text_value table`
2021-06-01 15254, 2021
akashgp09
> ( id: 1, attribute_id: 1 ) , ( id: 2, attribute_id: 2) sorry i meant attribute_type here not attribute_id
2021-06-01 15217, 2021
monkey
akashgp09: We'll need some utility functions to update the relationship_attribute_set elements, like we do for other sets. Have a look for example at https://github.com/bookbrainz/bookbrainz-data-js/… , as well as this file with some set-realted utility functions:
So I'm not sure I understand specifically what the question is. Where are you blocked?
2021-06-01 15201, 2021
BrainzGit joined the channel
2021-06-01 15248, 2021
akashgp09
after inserting some rows in the `relationship_attribute` table how will i insert the attribute values associated with this rows (id) in the table `relationship_attribute_text_value`
2021-06-01 15251, 2021
monkey
Something like `new RelationshipAttributeTextValue({id: myRelAttributeId, value: "Hello!"}).save()` ?
2021-06-01 15248, 2021
monkey
`new RelationshipAttributeTextValue({id: myRelAttributeId, value: "Hello!"}).save(null, {method: 'insert'});` to be more exact
2021-06-01 15203, 2021
akashgp09
ye, how can i insert multiple rows at once ?
2021-06-01 15237, 2021
monkey
I guess I would call the method twice. Not sure if there's an option for saving multiple new rows
2021-06-01 15243, 2021
monkey
Let me look
2021-06-01 15240, 2021
monkey
No great options it seems. I would recommend calling `await new RelationshipAttributeTextValue(…).save(…)` twice, or use the underlying knex library if for some reason that doesn't work (see https://github.com/bookshelf/bookshelf/issues/445)
2021-06-01 15203, 2021
akashgp09
yeah thanks for this : )
2021-06-01 15202, 2021
monkey
👍
2021-06-01 15212, 2021
akashgp09
actually the problem is something bigger. give me some time I will write this up and explain the issue properly . Right now what i can think of is that it's not appropriate to add foreign on `relationship_atrribute` table id column
2021-06-01 15225, 2021
akashgp09
instead we should have the text_value column in the same table `relationship_atrribute` table
2021-06-01 15237, 2021
akashgp09
also no other table `relationship` , `aliases' , `release_event`, `identifiers` have foreign key on their `id` column
`attribute.map(row => await new RelationshipAttributeTextValue({id: row.id value: 'someValue' }).save(null, {method: 'insert'}));` looks pretty good to me at first glance.
2021-06-01 15217, 2021
monkey
The textvalue id ends up equal to the attribute id, so we should be able to do an SQL JOIN on that id.
2021-06-01 15217, 2021
monkey
When the value changes, we also create a new attribute row and a new attributeSet row.
2021-06-01 15217, 2021
monkey
The issue with putting the text value in the relationship_attribute table is that an attribute's value will not always be text, for example for dates.
2021-06-01 15252, 2021
monkey
Now we do need to make sure that we can retrieve the value easily, say with `Relationship.forge(…).fetch({withRelated: attributeSet.attribute.value})`.
2021-06-01 15222, 2021
akashgp09
> The textvalue id ends up equal to the attribute id, so we should be able to do an SQL JOIN on that id.
2021-06-01 15230, 2021
akashgp09
the joining part is OK
2021-06-01 15218, 2021
akashgp09
but i am conern with how will we know which attribute id will have which textValue
2021-06-01 15221, 2021
akashgp09
one more thing
2021-06-01 15223, 2021
monkey
Ah, I see maybe where the misunderstanding is: the relationship_attribute_text_value has no column `id` but instead the column is `attribute_id` with a foreign key pointing to an attribute
await new RelationshipAttributeTextValue({id: row.id value: 'someValue' }).save(null, {method: 'insert'})) will this always be a insert method ?
2021-06-01 15232, 2021
monkey
So when we join, we join on the attribute_id
2021-06-01 15258, 2021
monkey
I think we'll only ever insert new items (if the value is new or if the value changes)
2021-06-01 15212, 2021
monkey
That's how we keep the history of modifications
2021-06-01 15242, 2021
monkey
Whenever an attribute value changes, we create a new attribute (we keep the attributes that haven't changed as-is ), put them all in a new attributeSet and create a new relationship with that new attributeSet, and finally a new relationshipSet with the new relationship (and any previous rels that have not changed)
2021-06-01 15225, 2021
monkey
It's a bit involved but I don't see another solution if we want to keep revision history like we do with the rest of the sets