CODEWITHSUNDEEP
ruby-on-railsruby
Mozart Len
Mozart Len

Reputation: 111

Defining relationships Ruby-on-Rails

I have a 'location' table and a 'role' table,

Location Table:

id|location_name|location_code|category|
1 |  location1  |   0001      | Urban  |
2 |  location2  |   0002      | Rural  |
3 |  location3  |   0003      |Suburban|
________________________________________

Revenue Table:

id|role_code|rural|urban|suburban|
1 |   1001  |  5  |  10 |   15   |
2 |   1002  |  7  |  12 |   17   |
1 |   1003  |  9  |  14 |   19   |
__________________________________

The location table is to know the category of the area (Rural, urban or suburban) and the role table is to know the price of the revenue based on the location category.

how do I connect both tables together so that whenever users put in location and role, they see the price?

Upvotes: 2

Views: 79

Answers (5)

Omiyera Damilola
Omiyera Damilola

Reputation: 141

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.

Upvotes: 1

Kumar
Kumar

Reputation: 3126

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)

Upvotes: 3

Christian Doucette
Christian Doucette

Reputation: 1314

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.

Upvotes: 1

fidato
fidato

Reputation: 719

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}"

Upvotes: 1

Hackman
Hackman

Reputation: 1719

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)

Upvotes: 1

Related Questions