Merge many-to-one list of objects

Question

I have two database tables currently, representing a many-to-one relationship. Activities, which have an ID (integer, autoincrement, primary key) and a name (varchar(255), not null); and ActivitySlugs, which have an ID (same as above), a slugname (not null varchar), and an activity_id (foreign key to Activities.id).

Currently I'm supposed to get a list of all activities and all of their slugs (as one list), so that it looks something like this:

[
    {id: 1, name: "long name", slugs: ["nick", "names"]},
    {id: 2, name: "foobar", slugs: ["foo", "bar"]}
]

The naive solution (and about the only one I can think of) is to do a "SELECT *" on each table, and then merge them, but then I get data that looks like this:

activities = [
    {id: 1, name: "long name"},
    {id: 2, name: "foobar"}
]
slugs = [
    {id: 1, slugname: "nick", activity_id: 1},
    {id: 2, slugname: "names", activity_id: 1},
    {id: 3, slugname: "foo", activity_id: 2},
    {id: 4, slugname: "bar", activity_id: 2}
]

So then I can iterate over both and add them:

activities.forEach(function(activity) {
    slugs = [];
    activitySlugs.forEach(function(slug) {
        if (slug.activity_id == activity.id) {
            slugs.push(slug.name);
        }
    });
    activity.slugs = slugs;
);

This feels clumsy, slow, and unrefined. It certainly won't scale at all. Unfortunately, I can't seem to find a better way to get them to merge as a many-to-one with an array like this.

Answer 1

You can construct an id2activity map to access any activity by its id:

var id2activity = {};
activities.forEach(function(activity) {
    activity.slugs = [];
    id2activity[activity.id] = activity;
);

activitySlugs.forEach(function(slug) {
    id2activity[slug.activity_id].slugs.push(slug.name);
});

Answer 2

Since a slug can only belong to one activity, you should iterate over slugs and attach to a certain activity object instead.

var activities = [
    {id: 1, name: "long name"},
    {id: 2, name: "foobar"}
];
var slugs = [
    {id: 1, slugname: "nick", activity_id: 1},
    {id: 2, slugname: "names", activity_id: 1},
    {id: 3, slugname: "foo", activity_id: 2},
    {id: 4, slugname: "bar", activity_id: 2}
];

slugs.forEach(function(slug) {
    var a = activities.filter(function(a){return a.id==slug.activity_id;}); 
    // should check if it exists - assume that it does
    if (a[0].slugs == undefined) {
      a[0].slugs = [slug.id];
    } else {
      a[0].slugs.push(slug.id);
    }
});