upsertGraph + m2m relation with extra columns + modify on the join table issue #760
Description
Hi,
I ran into an issue when trying to save graph with upsertGraph with relations and values for "extra" columns at the same time. I've tested 0.9.4 and 1.0-rc6.
I will try to provide a stripped down variations of models I am using, hopefully enough to identify the problem.
class Group extends Model {
static tableName = 'groups';
static jsonSchema = {
type: 'object',
required: ['name'],
properties: {
id: { type: 'integer' },
sort: { type: 'integer' },
name: { type: 'string', minLength: 1, maxLength: 200 },
},
};
static relationMappings = {
decks: {
relation: Model.ManyToManyRelation,
modelClass: Deck,
modify: (builder) => {
builder.orderBy('sort');
},
join: {
from: 'groups.id',
through: {
from: 'groups_decks.group_id`,
to: 'groups_decks.deck_id`,
extra: ['sort'],
},
to: 'decks.id',
},
},
};
}
class Deck extends Model {
static tableName = 'decks';
static jsonSchema = {
type: 'object',
required: ['name'],
properties: {
id: { type: 'integer' },
name: { type: 'string', minLength: 1, maxLength: 200 },
},
};
static relationMappings = {
group: {
relation: Model.HasOneThroughRelation,
modelClass: Group,
join: {
from: 'decks.id',
through: {
modelClass: GroupDeck,
from: 'groups_decks.deck_id',
to: 'groups_decks.group_id',
extra: ['sort'],
},
to: 'groups.id',
},
},
};
}
class GroupDeck extends Model {
static tableName = 'groups_decks';
static jsonSchema = {
type: 'object',
required: ['group_id', 'deck_id'],
properties: {
id: { type: 'integer' },
group_id: { type: 'integer' },
deck_id: { type: 'integer' },
sort: { type: 'integer' },
},
};
static relationMappings = {
group: {
relation: Model.HasOneRelation,
modelClass: Group,
join: {
from: 'groups_decks.group_id',
to: 'groups.id',
},
},
deck: {
relation: Model.HasOneRelation,
modelClass: Deck,
join: {
from: 'groups_decks.deck_id',
to: 'decks.id',
},
},
};
}
Now assuming group id 1 and deck of id 1 and id 2 already exist in db, i want to create relation between them with additional 'sort' value. Note that i used 'modify' to automatically sort related records when using eager (and it works, but maybe there is a better option to do this?)
running:
Group.query().upsertGraphAndFetch({
id:1,
decks: [{id: 1,sort:0}, {id: 2, sort:1}]
}, {relate: true, unrelate: true})
causes an error with a malformed sql query tried to be executed:
update `decks` set where `decks`.`id` = 1 and `decks`.`id` in (select `groups_decks`.`deck_id` from `groups_decks` where `groups_decks`.`group_id` = 1) order by `sort` asc - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where `decks`.`id` = 1 and `decks`.`id` in (select `groups_decks' at line 1
firstly it attaches orderBy('sort') to update query, secondly not there where it should (instead it tries to use it in the target table and there is no sort column)
secondly, as a side note, i noticed that it tries to update id column. is such operation expected to be allowed?