How to insert a record with a Relationship

Question

Apologies, if this question is obvious, but I can't seem to find sufficient documentation. I might be lacking knowledge with restful methodologies. How do I store a record with a relationship?

I have a place. I want to store posts of users made to this place. So a place can have many posts. A post belongs to one place.

I'm using Aqueduct 3.0 Pre-Release.

I have following models:

place.dart

class Place extends ManagedObject<_Place> implements _Place {}

class _Place {
  @primaryKey
  int id;

  @Column(unique: true)
  String name;

  ManagedSet<Post> posts;
}

post.dart

import 'package:places_api/model/place.dart';
import 'package:places_api/places_api.dart';

class Post extends ManagedObject<_Post> implements _Post {}

class _Post {
  @primaryKey
  int id;

  @Column()
  String text;

  @Relate(#posts)
  Place place;
}

I try to save the post, but there is only the possibility to store a place object, and not a place_id. Obviously below post query does not work, as there is only a values.place object, and not a values.place_id property. Is it intended to load the place, and then store all the posts to it?

Also without relationship, I can't store the place_id, as it seems that Aqueduct treats the _ as something special. Can't I use database properties, that have an underscore?

Is there any example that explains this?

  @Operation.post()
  Future<Response> createPost() async {
    final body = request.body.asMap();
    final query = new Query<Post>(context)
      ..values.place_id = body['place_id']
      ..values.text = body['text'];

    final insertedPost = await query.insert();

    return new Response.ok(insertedPost);
  }

Currently I'm sending following body as POST:

{
    "place_id": 1,
    "text": "My post here"
}

To following URL: http://localhost:8888/posts

Would it be better to send something like this?

{
    "text": "My post here"
}

To URL: http://localhost:8888/place/1/posts

Then fetch the place first, and store the post to it?

Answer 1

When represented as JSON, a relationship is always a list or an object. In your case:

{
  "text": "text",
  "place": {
    "id": 1
  }
}

This allows client application parsing code to remain consistent - a related object is always an object, never a synthetic field (e.g., place_id). The underlying database does name the column place_id by joining the relationship name and its primary key name with an underscore, but that's an implementation detail that isn't exposed through the API.

When inserting an object, foreign keys are inserted because they are a column in the table. Therefore, you can write your operation method as so:

@Operation.post()
Future<Response> createPost(@Bind.body() Post post) async {
  final query = new Query<Post>(context)
    ..values = post;

  final insertedPost = await query.insert();

  return new Response.ok(insertedPost);
}

If you were to use the example JSON and this code, you'd get this SQL query: INSERT INTO _post (text, place_id) VALUES ('text', 1).

When inserting the 'has' side of a relationship, you have to insert the related objects as a separate query. An update/insert query will only set/insert values on a single table. If it makes sense for your API, you may want to POST the following place JSON:

{
  "name": "Place",
  "posts": [
    {"text": "text"}
  ]
}

Your code to insert this object graph might look like:

await context.transaction((t) async {
  final q = Query<Place>(t)..values = body;
  final insertedPlace = await q.insert();
  await Future.wait(body.posts, (p) async {
    final postQuery = Query<Post>(t)
      ..values = p
      ..values.place.id = insertedPlace.id;
    return postQuery.insert();
  });
});

Couple of other small notes: asMap has been removed and replaced with as<T> or decode<T>. You also do not need an @Column annotation if you aren't adding any flags. All fields declared in the table definition type are database columns. Transient fields are declared in the ManagedObject subclass, and can be annotated with @Serialize() if you want them to be a part of the API surface.