0

Persons Table

------------------------------------------------------------
id  |  name  |  cost_price  |  sell_price  |  product_id  |
------------------------------------------------------------
 1    person1    10000         20000            1
 2    person2    20000         30000            2
 3    person3    30000         40000            3

Products Table

--------------
id  |  name  | 
--------------
 1   product1 
 2   product2 
 3   product3
My SQL Query 
select name, (cost_price - selling_price) as profit, products.name
from persons
inner join products on persons.product_id = products.id 
where product.id = 1
order by product.name asc

I fail to replicate this in Data Mapper, this is what I have tried(I have defined the models & their associations properly)

Person.all( 

# SQL Join equivalent
  Person.product.id => 1,  

# products.name how to define that ?
# Also can we directly take cost_price-selling_price as profit or 
# we need to model the resultset ?
  :fields => ['id', 'name', 'cost_price', 'selling_price']

# How do I order by product.name ?
  :order => []
)
Mudassir Ali
  • 7,913
  • 4
  • 32
  • 60

1 Answers1

1

Get person:

person = Person.first id: 1
# or
person = Person[1]

person now contains all related products(DataMapper doing all join stuff for you),
you just need to fetch/list them:

products = person.products order: :name.asc

regard (cost_price - selling_price) as profit
you can add a method inside your Person model, like this:

class Person
  # ...

  def profit
    cost_price - selling_price
  end
end

To iterate over Persons/Products:

Person.each do |person|
  puts person.name
  puts " Profit: #{person.profit}"
  puts " Products:"
  person.products.all(order: :name).each do |product|
    puts "  #{product.name}"
  end
end
  • It seems we can't do it in a single query. Thanks for the idea though..This helps – Mudassir Ali Nov 21 '12 at 11:16
  • Sorry fr the mistake, I modified the question it is actually where product.id in SQL query instead of person.id, so I need to find records as per the join then iterate over them to fetch product name ? – Mudassir Ali Nov 21 '12 at 11:22
  • sorry, missed that. to filter over persons: `Person.all( ... ).each do |person|`, where `...` can be any filter, `id: 1`, or `name: /Bob/` or `:profit.gt N` etc –  Nov 21 '12 at 11:42