How do you insert a reference to a model without creating it?

Question

I have model A that can have one or more rows of model B affiliated with it. The relation only has to go one way.

I ran:

result = await A.query()
  .where('id', 123) // 123 is an already existing model A
  .insertGraph([
    {
      B: [{id: 456}] // 456 is an already existing model B
    }
   ]);

But I get an error Key (id)=(456) already exists.

To my understanding, Objection is trying to make a new model B with an id 456 where I just want to add a relation between the the two rows A:123 and B:456.

Rashomon · Accepted Answer · 2019-04-27T07:56:39.897

1

Since you are updating, I would not use insertGraph(). It could be done with upsertGraph() using refs. But this method is a bit complex, ideally I would just do:

const a = await A
  .query()
  .findById(123);

const numRelatedRows = await a.$relatedQuery('B')
  .relate(456)

Using upsertGraph(), you would relate child 456 this way, but the rest of child (if existing) would be unrelated. You can modify the behaviour of upsert with a lot of options, but for me in your case $relatedQuery() is the way to go:

const result = await A.upsertGraphAndFetch(
  {
    id: 123,
    B: [
         {#ref: 456} // relate 456, and unrelate the rest, since not present here
       ]
  }
)

edited Apr 27 '19 at 07:56

answered Apr 27 '19 at 07:00

Rashomon

5,962
4
29
67

I JUST found relate() and that was the direction I was exploring. I was surprised there's no way to do it in one query, though. is `upsertGraph()` with refs, like you said, something that can be more efficient? – bzupnick Apr 27 '19 at 07:23
I updated the answer with `upsertGraph()`. At the beggining doing two queries was kind of annoying for me, but you will realize it makes sense. To relate, Objection needs the information of the parent. Imagine you want to relate and entire table with million of rows, Objection would have to fetch the entire table. This is a responsability that Objection leaves to you. Makes the developer aware of whats going on – Rashomon Apr 27 '19 at 07:41
Thank you for the edit! With that last comment, are you saying that objection does multiple queries behind the scenes ANYWAY with the upsertGraph? So it's not any more efficient? – bzupnick Apr 27 '19 at 07:43
I would say yes, since its unrelating the not present child elements. That should be done with some extra query. In terms of efficience, `$relatedQuery()` is the way to go in this case. – Rashomon Apr 27 '19 at 07:46

How do you insert a reference to a model without creating it?

1 Answers1