CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Geordee Naliyath
Geordee Naliyath

Reputation: 1859

Rails Merging Multiple ActiveRecord Queries

Can somebody point out how ActiveRecord is generating a nested hash out of a Left Outer Join query?

I am doing an eager load for a complex query in which different WHERE clauses needs to be applied on to the has_many associated tables. When Rails generate the query to fetch associations with conditions applied, I do not get the intended result.

I am able to construct the result in pieces - for the driving table, and separately for the has_many associations. Now I need to merge the results of association tables into the driving table.

Or in simpler terms, I have the following tables in an application.

Items
___________________
| id | item_name  |
___________________
| 1  | "Item One" |
| 1  | "Item One" |
| 1  | "Item One" |
| 1  | "Item One" |
| 1  | "Item One" |
___________________

Inventories
_________________
| id | quantity |
_________________
| 1  | 10       |
| 1  | 10       |
| 1  | 10       |
| 1  | 10       |
| 1  | 10       |
_________________

Transactions
_________________________________________
| id | item_id | tran_status | tran_qty |
_________________________________________
| 1  | 1       | "ordered"   | 1        |
| 2  | 1       | "picked"    | 1        |
| 3  | 1       | "ordered"   | 4        |
| 4  | 1       | "canceled"  | 5        |
| 5  | 1       | "ordered    | 2        |
_________________________________________

Adjustments
_______________________________________
| id | item_id | adj_status | adj_qty |
_______________________________________
| 1  | 1       | "adjusted" | 1       |
| 4  | 1       | "canceled" | 1       |
_______________________________________

I am trying to create a JSON response for an AJAX action in the following format (using JBuilder).

[{"id": 1, 
  "name": "Item One",
  "inventory": {"quantity": 10},
  "transactions": [ {"tran_status": "Ordered", "tran_qty": 1},
                    {"tran_status": "Picked",  "tran_qty": 1},
                    {"tran_status": "Ordered", "tran_qty": 4},
                    {"tran_status": "Ordered", "tran_qty": 2} ],
  "adjustments":  [ {"adj_status": "Adjusted", "adj_qty": 1} ]
 }
]

Please note that the records with "canceled" status are excluded.

Now that this could be potentially requested for multiple items, I would like to do an eager load during querying.

However, the following query does not work. Rails generates a left-outer join query and that spoils the party.

  Item.includes(:inventory).
       includes(:transactions).
       where("transactions.status not in ('canceled')").
       references(:transactions).
       includes(:adjustments).
       where("adjustments.status not in ('canceled')").
       references(:adjustments).
       where(id: params[:item])

So, what I am try to do is to execute the queries in three steps and create a hash or Openstruct at the end for iterating in JBuilder.

  items = Item.includes(:inventory).
  item_ids =  items.collect(&:id)
  trans = Transaction.where(item_id: item_ids).where("status not in ('canceled')")
  adjs = Adjustment.where(item_id: item_ids).where("status not in ('canceled')")

Now I would like to merge all these into a single array of hashes.

I realize that Rails would be doing something like this internally, and thought of tapping into existing knowledge than building a less efficient way of doing this with my limited knowledge.

Any help would be appreciated.

UPDATE

The question was in the context of trying to build a solution myself. The core problem was to get Rails generate queries with conditions applied on associated tables. The accepted answer is the suggestion to build associations with conditions (which can be found in the discussion thread of the accepted answer).

Upvotes: 1

Views: 2566

Answers (2)

Siva
Siva

Reputation: 8058

require 'json'

hash_arr = []

Item.includes(:inventories,:transactions,:adjustments).all.each do |item|
  hash = {"id"=>item.id,
          "name"=>item.name,
           "inventory"=>item.inventories.count,
  "transactions"=>item.transactions.select("tran_status,tran_qty").collect{ |x|          
       {:tran_status=>x.tran_status,:tran_qty=>x.tran_qty} 
  },
  "adjustments"=>item.adjustments.select("adj_status,adj_qty").collect{|x| {:adj_status=>x.adj_status,:adj_qty=>x.adj_qty}
}
   }
  hash_arr.push hash
end

hash_arr.to_json

Hope this matchs your requirements

Or you can use as_json and specify your own JSON format( this would be much cleaner). Check here

Upvotes: 0

DeeY
DeeY

Reputation: 962

If I'm getting you right, you need to iterate over items:

items = Item.includes(:inventory)
result = []
items.each do |i|
  result << {
     :item => i,
     :trans => Transaction.where(item_id: i.id ).where("status not in ('canceled')"),
     :adjs => Adjustment.where(item_id: i.id).where("status not in ('canceled')")
  }    
end

Or you can get it in arrays with a single collect:

result = Item.includes(:inventory, :transactions, :adjustments).collect { |i| i, 
       i.transactions.where("status not in ('canceled')"), 
       i.adjustments.where("status not in ('canceled')") 
   }

, provided that you have correctly set up the associations

Upvotes: 1

Related Questions