Deleting a User and sorting elements in play(Scala) 2.6.x

Repository

https://github.com/playframework/playframework

What Will I Learn?

In this tutorial you will learn the following

• How to delete a user in a database
• Sorting elements in a database

Requirements

The following are required in order to properly follow along this tutorial

• Intellij IDEA
• sbt
• playframework with slick installed
• Web browser
• Basic knowledge of Scala programming language

Resources

• Slick website:- http://slick.lightbend.com/
• Slick repository:- https://github.com/slick/slick
• Slick License:- BSD-style license

Difficulty

• Intermediate

Tutorial Contents

Welcome to another tutorial on the play(scala) framework, in the previous tutorial, we looked at how to update a user in a database, in this tutorial we will be looking at how we can delete a user from a database using slick. Slick can be used in combination with MySQL, but queries will be written in slick.

This tutorial promises to be easy to understand if you have read my previous tutorials, if you haven't seen my other tutorials, the links can be found in the curriculum section

Creating queries in our model

Play framework is built using the MVC (Model, view and Controller)architecture, and this helps to separate business logic from everything else. To create our queries will navigate into the models found in app/models in intellij IDEA.

1. Delete queries

We have already created a file known as UserRepository, which is where we will be typing the following code.

//This deletes a particular item in a database
  def del(id: Long) : Future[Int] = db.run {
    people.filter(_.id === id).delete
  }

In the above code we have defined a function known as delete, which accepts the id of row it wants to delete as a parameter. We use the filter function to select a particular id, whereas people is simply a reference to our TableQuery object which we declared like this.

 private val people = TableQuery[PeopleTable]

We are telling the query to delete a user by adding .delete. In other word what we are trying to say is Delete user WHERE id == someid

2. Sort queries

In SQL sorting can be carried by using the ORDER BY keyword, and elements can be sorted either in ascending or descending order. The Scala equivalent of ORDER By is ````sortBy```, we shall attempt to retrieve elements in ascending order using the sortBy function. Let's attempt to write a query inside our model

def sort (): Future[Seq[Person]] = db.run {
    people.sortBy(p => (p.age.desc, p.name)).result
  }

In the above query we are ordering the values with age in descending order, which means that the first person that will be returned will be the oldest, followed by the next until it reaches the youngest person. We are simply saying select * from PERSON order by AGE asc, NAME the sortBy function sorts values either in ascending or descending order if you add asc or desc methods.

def aggregate (): Function[Seq[Person]] = db.run {
    people.map(_.age).max.result
  }

Creating Controller actions

Now that we are done creating our models, it's time to create actions in our controllers. This is where we call the functions that have been defined in our models. if you recall in our model we have defined 3 functions del, sort and aggregate, these functions will delete, sort and aggregate data.

1.Delete actions

To create delete actions, we will create an implicit request to run our delete query. To do that we will define a function i our controller called delPerson which accepts the id of the user as a parameter, we will then creating a mapping for a wild card and then redirect the back to the home page. We can implement it by typing the following code

 def delPerson (id: Int) = Action.async { implicit request =>
    repo.del(id).map { _ =>
      Redirect(routes.UserController.index())
    }
  }

2. Sort actions

In our controller we will call the sort function which we defined in our models. To do that we will create a method called sortPeople and create an implicit request to sort the values returned. We will reference this function like this repo.sort because repo a reference to our userRepository object.

def sortPeople () = Action.async {
    implicit request =>
      repo.sort ().map { people =>
        Ok(Json.toJson(people))

      }
  }

Implementing GET requests in routes

For the actions we just created we need to create roues for them so that we can use GET requests to run our queries. routes file can be found we will type the following code

GET    /sortpersons                       controllers.UserController.sortPeople
GET     /delperson/:id                    controllers.UserController.delPerson(id: Int)

The /sortpersons is the url which we will call to implement the sortPeople function is the UserController class, then the /delperson/:id will contain the id of the person you want to delete from the database, it implements the delPerson function.

To delete a user with id no 1 from the database, we will simply type this route our browser
localhost/9000/delperson/1. So let's view the result in our browser, we might notice that the user with id equals 1 is missing from the database.

Now let's try to sort out the users in descending order, to do that we will type the following routes in browser
localhost:9000/sortpersons

Curriculum

Include a list of related tutorials you have already shared on Utopian that make up a Course Curriculum, if applicable.

• Creating a user registration system in play 2.6.x (scala) using mysql
• Creating a user registration system in play 2.6.x (Scala) using mysql part2: Retrieving data in JSON format and navigating with routes
• Retrieving a particular user and initiating GET requests in play 2.6.x(Scala)
• Updating a particular user in play 2.6.x using slick

Proof of Work Done

All the code used in this tutorial can be found below
https://github.com/leczy642/play-scala-slick-CRUD