Active Record and the Wildcard: A Tale of More Than One Search Term

Suppose you are working on a project that gives users the ability to search your database and return a list of items based on that search term. Easy enough — there are plenty of apps and websites that do this. But, what if you wanted to filter items from your database with more than one parameter?

I encountered this situation while building a pet adoption app using React on Rails. My database was filled with pets of different species, sizes, ages, and locations. It was easy enough to filter the list of pets so that it returned only dogs, or only small pets, or only senior pets. However, I wanted to be able to return a list sorted by more than one parameter. What if a user was interested in adopting small, female puppies located near Port Washington, NY? It made sense to build that feature.

Step 1: The Form

User input starts with submitting a form, so it makes sense to start there. In React, it is necessary to build the form as a stateful component, where each piece of state is a filter that you apply to your list of items from the database. I’ll stick to my pet adoption project as an example, where I chose to use dropdown menus for species, sex, city, age, and size using Semantic UI React.

export default class FilterForm extends Component {
  constructor(props){
    super(props)
  this.state = {
    species: "",
    sex: "",
    city: "",
    age: "",
    size: ""
  }
  this.handleChange = this.handleChange.bind(this)
  this.handleSubmit = this.handleSubmit.bind(this)
}

The handleChange function updates the state accordingly when an option is selected form a dropdown.

handleChange(event, result){
  this.setState({
    [result.placeholder]: result.value
  })
}

The handleSubmit function is what sends the values of the state as search terms to the back-end.

handleSubmit(event){
  event.preventDefault()
  let stateObj = this.state
  // any key in the state with a value of "" is removed first
  for (var key in stateObj){
    if (stateObj[key] === ""){
      delete stateObj[key]
    }
  }
  // the applyFilter function, passed in through props, is then
  // called on the remaining key value pairs of the state
  this.props.getFilteredPets(stateObj)
}

The getFilteredPets function is passed in as props. This is the function that makes the request to the back end.

getFilteredPets(pets){
  let query = JSON.stringify(pets)
  return fetch(`${this.url()}`, {
  method: ‘POST’,
  headers: {
    'content-type': 'application/json',
     'accept': 'application/json'
    },
  body: query
  }).then(res => res.json())
}

When the values of the form are sent to the Rails back-end, it hits the Pets controller first. Here’s where it gets tricky. The values submitted through the form are sent as params, but the user may not want to sort by every category available. In other words, a user might select “cats” from the Species dropdown, but they may not want to limit their search by age, sex, or location, so they don’t select an option from those dropdown menus. If a category isn’t selected from the dropdown, then no params are sent. The answer is to account for any params that hit the controller and to search “all” for any category where params are not present.

Step 2: The Wildcard

Behold, the percent sign, known as a "wildcard." Written as '%' in Active Record, it is shorthand for “search any.” What we want to tell Active Record is, “for a given column in the database, return only the results that match the value specified by the user. If no value was selected, return all results from that column.” In the Pets controller in Rails, I have a show action that will sort by whatever params it receives. If no params are received for a certain column, it puts '%' as a placeholder in the query, telling Active Record to return all for that column in the database.

def show
  # a variable for each column is set with the params (if received)
  # or a wildcard (if no params received)
  species = params[:species] || ‘%’
  sex = params[:sex] || ‘%’
  city = params[:city] || ‘%’
  size = params[:size] || ‘%’
  age = params[:age] || ‘%’
  
  # Pet objects are sorted by the variables declared above,
  # and saved to a variable
  pets =
Pet.by_species(species).by_sex(sex).by_city(city).by_size(size).by_  age(age)
  # then, pets is rendered as JSON and displayed on the front-end
  render json: pets
end

There you have it! Next time you need to query your database by more than one search term, consider using the wildcard.