Objection.js: How to delete a complex graph?

[apronin83]

Hello!
I have three tables (Sqlite):

• Authors (id, name)
• AuthorsPosts (author_id, post_id) - many to many
• Posts (id, content)

class Post extends Model {
    static get tableName() {
        return "t_posts";
    }
}

class Author extends Model {
    static get tableName() {
        return "t_authors";
    }

    static get relationMappings() {
        return {
            posts: {
                relation: Model.ManyToManyRelation,
                modelClass: Post,
                join: {
                    from: "t_authors.id",
                    through: {
                        from: "t_authors_posts.author_id",
                        to: "t_authors_posts.post_id"
                    },
                    to: "t_posts.id"
                }
            }
        };
    }
}

Please give an example of deleting the graph Authors -> AuthorsPosts -> Posts or write how to gracefully delete this graph?

Thank you so much.

[apronin83]

My code:

const author = await Author.query()
  .skipUndefined()
  .findById(req.params.id)
  .debug(true);

if (author) {
  let post_ids = [];

  const posts = await author.$relatedQuery("posts").map(async post => {
    await post_ids.push(post.$id());
  });

  if (post_ids.length > 0) {
    await author
      .$relatedQuery("posts")
      .unrelate()
      .then(async () => {
        await post_ids.map(async post_id => {
          await Post.query()
            .deleteById(post_id)
            .debug(true);
        });
      });
  }

  await author
    .$query()
    .delete()
    .debug(true);
}

[koskimas]

First of all, you need to add onDelete('CASCADE') for your foreign keys in t_authors_posts table. See the example projects' migration files. That way the rows in t_authors_posts get automatically deleted when either end of the relation (either the author or the post) gets deleted. That saves you from doing both an unrelate and a delete.

Second of all, you don't need to loop through you relation and delete them individually. Instead of this

    await author
      .$relatedQuery("posts")
      .unrelate()
      .then(async () => {
        await post_ids.map(async post_id => {
          await Post.query()
            .deleteById(post_id)
            .debug(true);
        });
      });

you can just do this:

await author.$relatedQuery("posts").delete()

So your example gets reduced to this:

const author = await Author.query()
  // You definitely shouldn't use `skipUndefined` here. It will cause the
  // first author from the db (with arbitrary order) to be returned if
  // `params.id` is undefined. Effectively, you end up deleting a
  // a random author and all their posts.
  .findById(req.params.id)
  .debug(true);

if (author) {
  await author
        .$relatedQuery('posts')
        .delete()

  await author
    .$query()
    .delete()
    .debug(true);
}

[apronin83]

Excellent thanks!