How to delete relationship in a join table

[tomBeach]

Here is something I have never understood about routes: A specific user (id=1) relates to specific stock (id=3) via a join table. The user no longer has the stock, so I need to delete the id:1 -- id:3 relationship from the join table (ownership). This requires two ids (user and stock). How do those ids translate into the router function? My version is below, but makes no sence to me. What is the proper syntax for this function?

// == DELETE stock from group
router.delete("/users/:id/ownership/:id", function(req, res){
    console.log("DELETE: /users/:id/ownership");
    Ownership.findById(req.params.id).then(function(ownership){
        if(!user) return error(res, "not found");
            ownership.destroy().then(function(){
            res.json({success: true});
        });
    });
});

[RobertAKARobin]

Here's a simplified version of what you seem to be doing.

router.delete("/users/:userId/ownership/:ownershipId", function(req, res){
  Ownership.destroy({where: {id: req.params.ownershipId}}).then(function(){
    res.json({success: true});
  });
});

You don't need the user ID to delete an ownership. You just need the ownership's ID.

Imagine a database that has a Users table and a Friendship. Each Friendship belongs to two Users: it has 3 columns, id, user1_id, and user2_id. Each Friendship will have a unique ID. So you only need to know the Friendship's ID in order to find the one you want to delete. Replace "Friendship" with "Ownership" and you've got the same idea here.

[tomBeach]

How do I get the ownership id? The only way to find it is with the other two ids: userid and stockid. Where they both appear in the join table, that's the record to delete.

[RobertAKARobin]

Oh, I see. Well, presumably you have access to the user ID and to the ID either of their stock or their ownership. Otherwise you wouldnt be able to display the stock and the user, right? So depending on whether you use the stock id or the ownership id to display their stock, you can use the ownership id to find the relationship and delete it, or the stock id and the user id to find the relationship and delete it presuming the user only has one ownership of a particular stock. You need to know something unique about the ownership. Pardon the bad spelling, on my phone.

[tomBeach]

I do have both user and stock ids, but its how to write the controller statement (under the incoming route) that is tripping me up. I need to do a select on the ownershipt table, then delete the record returned. But the syntaxt? No clue. Here's my most recent attempt:

router.delete("/users/:user_id/stock/:id", function(req, res){
    User.findById(req.params.id).then(function(user){
        if(!user) return error(res, "not found");
            Ownership.findById(req.params.id).then(function(stock){
                stock.destroy().then(function(){
            res.json({success: true});
        });
    });
});

[jshawl]

I'm having trouble understanding where / what the issue is here, as the code looks good! How about some console.logs to figure out what is happening where:

router.delete("/users/:user_id/stock/:id", function(req, res){
  console.log("server received delete request")
  User.findById(req.params.id).then(function(user){
    console.log("Found user by id:")
    console.log("user: ", user)
    console.log("id: ", req.params.id)
    if(!user) {
      console.log("!user") 
      return error(res, "not found");
    }
    Ownership.findById(req.params.id).then(function(stock){
      console.log("Found ownership by id, named as stock:")
      console.log("stock: ", stock)
      console.log("id: ", req.params.id)
      stock.destroy().then(function(){
        console.log("destroyed stock")
        res.json({success: true});
      });
   });
});

[RobertAKARobin]

I think the trouble is that he's doing Ownership.findById(req.params.id) where params.id is the stock ID, as opposed to the ownership ID.

So you might want something like this:

router.delete("/users/:user_id/stock/:stock_id", function(req, res){
  Ownership.destroy({where: {user_id: req.params.user_id, stock_id: req.params.stock_id}}).then(function(){
    res.json({success: true});
  });
});

[tomBeach]

Here is an example of why this is so frustrating; One of the few routes I have been able to get working is the one below (uncommented version), yet even when the route is successful it does not display console.log statements. (I even tried commenting out everything except the console.logs and still saw nothing!) I know the route itself works because when commenting the whole thing it fails (duh...).

More importantly, I can't figure out how to deal with the second selection criterion in the route ("group"). I am looking for stocks that are matched to the owner (user) via the ownership join table. Each record in that table also has a "group" column (e.g. "portfolio", "watchList", etc.), a string that should be searchable. When I add the group parameter to the route it fails. I can't tell if it's being passed in since nothing will console.log from this route. Or should group be passed in the params object? If so, how does it get into params from the ajax call (below route code). This process would be so much more understandable if these parameters could be passed as basic arguments as they are in so many other programming situations...

Beyond that, I do not understand where user.getStocks comes from. I don't see it defined anywhere in the code. I assume it's a "built-in" function, but I can't find a reference anywhere that explains the syntax of built-in functions. (Can it take arguments? Would it be "deleteStocks" or "updateStocks" for delete or patch requests?) Since we are actually dealing with the join table (which never appears in this code but is the basis for everything going on here) how does it know to look in the group column of the join table if the join table is never mentioned?

So many questions...

successful route

router.get("/users/:id/ownership/:group", function(req, res){
    console.log("GET: /users/:id/ownership");
    console.log("req.params: " + req.params);
    User.findById(req.params.id).then(function(user){
        if(!user) return error(res, "not found");
            user.getStocks().then(function(stocks){
            // user.getStocks({where: {user_id: req.params.user_id, group: req.params.group}}).then(function(stocks){
            // stocks.getStocks({where: {user_id: req.params.user_id, group: req.params.group}}).then(function(stocks){
            // ownership.getStocks({where: {user_id: req.params.user_id, group: req.params.group}}).then(function(stocks){
            res.send(stocks);
        });
    });
});

ajax call

        var url = "http://localhost:3000/users/2/ownership/" + this.groupName;  // NOTE groupName => group
        $.ajax({
            url: url,
            type: "get",
            dataType: "json"
        }).done(function(jsonData){
            console.log("  ajax request success!");
            console.dir(jsonData)
            extractDatabaseTickers(jsonData);
        }).fail(function(){
            console.log("  ajax request fails!");
            self.handleError;
        }).always(function(){
            console.log("  ajax request always");
            self.handleAlways;
        });

[RobertAKARobin]

Yeah, that's a strange about the console.log not showing up. I can think of literally no reason console output wouldn't show up. My inclination is that they are showing up, but they're buried in everything else that's showing up in your terminal and visually difficult to see. But I could easily be wrong, since you've been trying so many different things with it. When in doubt, put console.log("*********************"); in your code or something easy to see.

As for using the multiple parameters, I've showed in this thread several times the way I'd do it, which is using where:

router.delete("/users/:user_id/stock/:stock_id", function(req, res){
  Ownership.destroy({where: {user_id: req.params.user_id, stock_id: req.params.stock_id}}).then(function(){
    res.json({success: true});
  });
});

It's the same thing for finding:

router.get("/users/:user_id/stock/:stock_id", function(req, res){
  Ownership.findAll({where: {user_id: req.params.user_id, stock_id: req.params.stock_id}}).then(function(ownership){
    res.json(ownership);
  });
});

The .getPlurals functions are documented here:

http://docs.sequelizejs.com/en/latest/docs/associations/#associating-objects

It's like @post.comments in ActiveRecord.

[tomBeach]

Robin -- I think we have one more twenty left... Can I get that to resolve this routing / query issue?

[RobertAKARobin]

On my way!