Lesson Thursday

Now that we have created a lot of the functionality we need to see for our app, all that is really left, as far as our MVP goes, is to set up the routing for our many-to-many relationship in the frontend. Let's add that now. We'll begin with some tests, and then move to setting up our routes for our frontend as we have done previously.

The Foodtype / Restaurant is a two way, many-to-many relationship, so we'll need to make sure we can retrieve all Foodtypes for a given Restaurant, as well as vice versa.

We'll be using our restaurants_foodtypes join table in order to store the connection between these two objects.

Writing tests for our Many-To-Many database

Let's write the following tests:

src/test/java/dao/Sql2oFoodTypeDaoTest.java
@Test
public void addFoodTypeToRestaurantAddsTypeCorrectly() throws Exception {

    Restaurant testRestaurant = setupRestaurant();
    Restaurant altRestaurant = setupAltRestaurant();

    restaurantDao.add(testRestaurant);
    restaurantDao.add(altRestaurant);

    Foodtype testFoodtype = setupNewFoodtype();

    foodtypeDao.add(testFoodtype);

    foodtypeDao.addFoodtypeToRestaurant(testFoodtype, testRestaurant);
    foodtypeDao.addFoodtypeToRestaurant(testFoodtype, altRestaurant);

    assertEquals(2, foodtypeDao.getAllRestaurantsForAFoodtype(testFoodtype.getId()).size());
}
src/test/java/dao/Sql2oRestaurantDaoTest.java
@Test
public void RestaurantReturnsFoodtypesCorrectly() throws Exception {
        Foodtype testFoodtype  = new Foodtype("Seafood");
        foodtypeDao.add(testFoodtype);

        Foodtype otherFoodtype  = new Foodtype("Bar Food");
        foodtypeDao.add(otherFoodtype);

        Restaurant testRestaurant = setupRestaurant();
        restaurantDao.add(testRestaurant);
        restaurantDao.addRestaurantToFoodtype(testRestaurant,testFoodtype);
        restaurantDao.addRestaurantToFoodtype(testRestaurant,otherFoodtype);

        Foodtype[] foodtypes = {testFoodtype, otherFoodtype}; //oh hi what is this?

        assertEquals(restaurantDao.Restaurant(testRestaurant.getId()), Arrays.asList(foodtypes));
    }    


    @Test
    public void deleteingRestaurantAlsoUpdatesJoinTable() throws Exception {
        Foodtype testFoodtype  = new Foodtype("Seafood");
        foodtypeDao.add(testFoodtype);

        Restaurant testRestaurant = setupRestaurant();
        restaurantDao.add(testRestaurant);

        Restaurant altRestaurant = setupAltRestaurant();
        restaurantDao.add(altRestaurant);

        restaurantDao.addRestaurantToFoodtype(testRestaurant,testFoodtype);
        restaurantDao.addRestaurantToFoodtype(altRestaurant, testFoodtype);

        restaurantDao.deleteById(testRestaurant.getId());
        assertNotEquals(0, restaurantDao.Restaurant(testRestaurant.getId()).size());
    }

Try and see if you can write the methods that make these tests pass on your own, after you add them to your class respective DAO interfaces.

If you haven't created the join table yet, make sure you do. You may have to query the join table first to a subset of the information you need, before you query the object-specific table to get the rest.

If you get stuck, check the cheat sheet, or the example repo, but try it on your own first.

Building our frontend routes for Many-to-Many

Once our tests all pass, building our frontend routes is up next.

Let's pop over to our App.java and implement them so we can test with Postman.

First, we'll need a route that associates our foodtypes with restaurants. Then, we'll need additional routes to show the data we are looking to see.

Let's set up a post route that associates a foodtype with a restaurant first.

You may notice no findById for Foodtype exists yet. Oops! We should quickly implement this method - we can use the other methods we have created as our blueprint.

Go ahead and implement that method now if it does not yet exist, then add the routes shown below.

src/main/java/App.java
post("/restaurants/:restaurantId/foodtype/:foodtypeId", "application/json", (req, res) -> {
        int restaurantId = Integer.parseInt(req.params("restaurantId"));
        int foodtypeId = Integer.parseInt(req.params("foodtypeId"));
        Restaurant restaurant = restaurantDao.findById(restaurantId);
        Foodtype foodtype = foodtypeDao.findById(foodtypeId);

        if (restaurant != null && foodtype != null){
            //both exist and can be associated - we should probably not connect things that are not here.
            foodtypeDao.addFoodtypeToRestaurant(foodtype, restaurant);
            res.status(201);
            return gson.toJson(String.format("Restaurant '%s' and Foodtype '%s' have been associated",foodtype.getName(), restaurant.getName()));
        }
        else {
            throw new ApiException(404, String.format("Restaurant or Foodtype does not exist"));
        }
    });

Go through this route and make sure you understand what is happening here. Test it with Postman to make sure it works.

Next, we'll retrieve information.

src/main/java/App.java
    get("/restaurants/:id/foodtypes", "application/json", (req, res) -> {
    int restaurantId = Integer.parseInt(req.params("id"));
    Restaurant restaurantToFind = restaurantDao.findById(restaurantId);
    if (restaurantToFind == null){
        throw new ApiException(404, String.format("No restaurant with the id: \"%s\" exists", req.params("id")));
    }
    else if (restaurantDao.Restaurant(restaurantId).size()==0){
        return "{\"message\":\"I'm sorry, but no foodtypes are listed for this restaurant.\"}";
    }
    else {
        return gson.toJson(restaurantDao.getAllFoodtypesByRestaurant(restaurantId));
    }
});

get("/foodtypes/:id/restaurants", "application/json", (req, res) -> {
    int foodtypeId = Integer.parseInt(req.params("id"));
    Foodtype foodtypeToFind = foodtypeDao.findById(foodtypeId);
    if (foodtypeToFind == null){
        throw new ApiException(404, String.format("No foodtype with the id: \"%s\" exists", req.params("id")));
    }
    else if (foodtypeDao.getAllRestaurantsForAFoodtype(foodtypeId).size()==0){
        return "{\"message\":\"I'm sorry, but no restaurants are listed for this foodtype.\"}";
    }
    else {
        return gson.toJson(foodtypeDao.getAllRestaurantsForAFoodtype(foodtypeId));
    }
});

Great! Make sure you test all of these routes thoroughly. We now have the ability to serve our API clients all kinds of interesting and useful data. Great stuff!

Example GitHub Repo for Jadle Code at this stage

src/main/java/dao/Sql2oFoodTypeDao.java
@Override
public void addFoodtypeToRestaurant(Foodtype foodtype, Restaurant restaurant){
    String sql = "INSERT INTO restaurants_foodtypes (restaurantid, foodtypeid) VALUES (:restaurantId, :foodtypeId)";
    try (Connection con = sql2o.open()) {
        con.createQuery(sql)
                .addParameter("restaurantId", restaurant.getId())
                .addParameter("foodtypeId", foodtype.getId())
                .executeUpdate();
    } catch (Sql2oException ex){
        System.out.println(ex);
    }
}


@Override
public List<Restaurant> getAllRestaurantsForAFoodtype(int foodtypeId) {
    ArrayList<Restaurant> restaurants = new ArrayList<>();
    String joinQuery = "SELECT restaurantid FROM restaurants_foodtypes WHERE foodtypeid = :foodtypeId";

    try (Connection con = sql2o.open()) {
        List<Integer> allRestaurantIds = con.createQuery(joinQuery)
                .addParameter("foodtypeId", foodtypeId)
                .executeAndFetch(Integer.class); //what is happening in the lines above?
        for (Integer restaurantId : allRestaurantIds){
            String restaurantQuery = "SELECT * FROM restaurants WHERE id = :restaurantId";
            restaurants.add(
                    con.createQuery(restaurantQuery)
                            .addParameter("restaurantId", restaurantId)
                            .executeAndFetchFirst(Restaurant.class));
        } //why are we doing a second sql query - set?
    } catch (Sql2oException ex){
        System.out.println(ex);
    }
    return restaurants;
}
}

And in our restaurant file:

src/main/java/dao/Sql2oRestaurantDao.java
@Override
 public void addRestaurantToFoodtype(Restaurant restaurant, Foodtype foodtype){
     String sql = "INSERT INTO restaurants_foodtypes (restaurantid, foodtypeid) VALUES (:restaurantId, :foodtypeId)";
     try (Connection con = sql2o.open()) {
         con.createQuery(sql)
                 .addParameter("restaurantId", restaurant.getId())
                 .addParameter("foodtypeId", foodtype.getId())
                 .executeUpdate();
     } catch (Sql2oException ex){
         System.out.println(ex);
     }
 }

 @Override
 public List<Foodtype> Restaurant(int restaurantId) {
     ArrayList<Foodtype> foodtypes = new ArrayList<>();

     String joinQuery = "SELECT foodtypeid FROM restaurants_foodtypes WHERE restaurantid = :restaurantId";

     try (Connection con = sql2o.open()) {
         List<Integer> allFoodtypesIds = con.createQuery(joinQuery)
                 .addParameter("restaurantId", restaurantId)
                 .executeAndFetch(Integer.class);
         for (Integer foodId : allFoodtypesIds){
             String foodtypeQuery = "SELECT * FROM foodtypes WHERE id = :foodtypeId";
             foodtypes.add(
                     con.createQuery(foodtypeQuery)
                             .addParameter("foodtypeId", foodId)
                             .executeAndFetchFirst(Foodtype.class));
         }
     } catch (Sql2oException ex){
         System.out.println(ex);
     }
     return foodtypes;
 }