Defining relationships Ruby-on-Rails

The current structure is not very scalable in nature. I would highly recommend changing the way you structure the data. Take a look at the following example.

# Instead of storing the location category in as text you can store references
#   of location_category in locations & revenues table
class LocationType # Location Category
end
# +----+-----------+
# | id | name      |
# +----+-----------+
# |  1 | Urban     |
# +----+-----------+
# |  2 | Rural     |
# +----+-----------+
# |  3 | Sub Urban |
# +----+-----------+


# Similarly instead of storing role_code, you can store references to roles 
#   in your revenue table
class Role
end
# +----+------+--------+
# | id | code | name   |
# +----+------+--------+
# |  1 | 1001 | Role 1 |
# +----+------+--------+
# |  2 | 1002 | Role 2 |
# +----+------+--------+
# |  3 | 1003 | Role 3 |
# +----+------+--------+

class Location
  belongs_to :location_type
end
# +----+-----------+---------+------------------+
# | id | name      | code    | location_type_id |
# +----+-----------+---------+------------------+
# |  1 | location1 | 001     |                1 |
# +----+-----------+---------+------------------+
# |  2 | location2 | 002     |                2 |
# +----+-----------+---------+------------------+
# |  3 | location3 | 003     |                3 |
# +----+-----------+---------+------------------+

class Revenue
  belongs_to :location_type
  belongs_to :role
end
# References for roles & location_type stored instead of actual values
# +----+-----------+---------+------------------+
# | id | role_id   | revenue | location_type_id |
# +----+-----------+---------+------------------+
# |  1 |         1 |       5 |                2 |
# +----+-----------+---------+------------------+
# |  2 |         2 |       7 |                2 |
# +----+-----------+---------+------------------+
# |  3 |         3 |       9 |                2 |
# +----+-----------+---------+------------------+
# |  4 |         1 |      10 |                1 |
# +----+-----------+---------+------------------+
# |  5 |         2 |      12 |                1 |
# +----+-----------+---------+------------------+
# |  6 |         3 |      14 |                1 |
# +----+-----------+---------+------------------+
# |  7 |         1 |      15 |                3 |
# +----+-----------+---------+------------------+
# |  8 |         2 |      17 |                3 |
# +----+-----------+---------+------------------+
# |  9 |         3 |      19 |                3 |
# +----+-----------+---------+------------------+

With this structure now you can run optimal queries on your database to extract data.

# Example
location_type_id = params[:location_type_id]
role_id = params[:role_id]

@revenue = Revenue.where({
  location_type_id: location_type_id,
  role_id: role_id
}).sum(:revenue)

If you want you can also include location_id in your Revenue table, then you can find the revenue for a particular location like so:

@revenue = Revenue.where({
  role_id: role_id,
  location_id: location_id
}).sum(:revenue)

If the user puts in location2 and role 1003 you could do:

location = params[:location] # or some other way to get the input
role = params[:role] # or some other way to get the input

location_category = Location.find_by(name: location).category.downcase
# now location_category will be rural

Reveneu.find_by(role_code: role).send(location_category)
# with the .send method you can access the column that you need(in this case rural)

Assuming that user have role_code & location_name in user model -

user = User.first
category = Location.find_by(name: location).category.downcase
price = Revenue.find_by(role_code: user.role_code).send(category)
puts "Your price is #{price}"

A simple way to get price would be to make a function that takes in a Location object (user_entered_location) and a Revenue object (user_entered_revenue) and returns a price.

def get_price(user_entered_location, user_entered_revenue)
    if user_entered_location.category=="Urban"
        return user_entered_revenue.urban
    elsif user_entered_location.category=="Suburban"
        return user_entered_revenue.suburban
    else
        return user_entered_revenue.rural
end

You could also look into joining tables.

The easiest approach to this is to use an if statement to check if the location is Urban, rural or Suburban.

I am assuming you want to use the price in a different model so I called the model transaction in the code sample below.

if transaction_params[:price].blank?
    
  if @transaction.location.category == 'Rural'
    @transaction.price = @transaction.revenue.rural
  elsif @transaction.location.category == 'Suburban'
    @transaction.price = @transaction.revenue.suburban
  else
    @transaction.price = @transaction.revenue.urban
  end
end

You can do this in create method in your transaction controller.