Spree Commerce - Search for order by sku

Question

I'm trying to create a search bar on the backend of Spree under the orders tab where you can input a product sku and find all orders that contain that product.

I've used deface to insert a "search by sku" field. The code looks like this:

Deface::Override.new(:virtual_path => 'spree/admin/orders/index',
                 :name => 'search_by_sku',
                 :insert_bottom => "div.omega",
                 :text => "
                 <div class='field'>
                 <%= label_tag nil, Spree.t(:sku) %> 
                 <%=f.text_field :line_items_cont %>
                 </div>
                 ")

line_items_cont gives me an error.

line_items_cont is what ransack uses to search for line_items, which should come back in the results, but it does not. Here's what the results return.

#<Spree::Order id: 225400, number: "R454575501", 
item_total: #<BigDecimal:7f83f7815998,'0.3194E3',18(18)>,
total: #<BigDecimal:7f83f78158f8,'0.3194E3',18(18)>,
state: "complete", adjustment_total: #<BigDecimal:7f83f7815808,'0.0',9(18)>,
user_id: nil, completed_at: "2014-01-30 07:34:38", bill_address_id: 173224,
ship_address_id: 171925, payment_total: #<BigDecimal:7f83f7815560,'0.3194E3',18(18)>,
shipping_method_id: nil, shipment_state: "shipped", payment_state: "paid", 
email: "test@email.com", special_instructions: "", created_at: "2014-01-30 05:00:49",
updated_at: "2014-01-30 16:12:31", currency: "USD", last_ip_address: "removed", 
yahoo_order_id: nil, legacy_fields: {}, legacy_order_id: nil, created_by_id: nil,
channel: "spree", approved_at: "2014-01-30 14:27:10">],

So what I'm wondering is is there a way to add the items contained in the order to the returned results?

Answer 1

The relationship between orders and SKU's is as follows:

• An order has many line items
• A line item belongs to a variant
• A variant has an attribute called SKU

So, lets look at methods of searching.

First I can find an order by using a simple search on order_id:

sandbox »  Spree::Order.search(id_eq: 1).result.first.id
   (0.1ms)  SELECT COUNT(*) FROM "spree_orders" WHERE "spree_orders"."id" = 1
=> 1

That's great. Now I need to find something by a line_item instead of by an order:

sandbox »  Spree::Order.search(line_items_id_eq: 1).result.first.id
  Spree::Order Load (0.5ms)  SELECT "spree_orders".* FROM "spree_orders" LEFT OUTER JOIN "spree_line_items" ON "spree_line_items"."order_id" = "spree_orders"."id" WHERE "spree_line_items"."id" = 1 ORDER BY "spree_orders"."id" ASC LIMIT 1
=> 1

Now I want to find something by a variant_id:

sandbox »  Spree::Order.search(line_items_variant_id_eq: 1).result.first.id
  Spree::Order Load (0.5ms)  SELECT "spree_orders".* FROM "spree_orders" LEFT OUTER JOIN "spree_line_items" ON "spree_line_items"."order_id" = "spree_orders"."id" WHERE "spree_line_items"."variant_id" = 1 ORDER BY "spree_orders"."id" ASC LIMIT 1
=> 1

But instead of searching by variant_id, I want to search by SKU:

Spree::Order.search(line_items_variant_sku_eq: "ROR-00011").result.first.id
  Spree::Order Load (0.5ms)  SELECT "spree_orders".* FROM "spree_orders" LEFT OUTER JOIN "spree_line_items" ON "spree_line_items"."order_id" = "spree_orders"."id" LEFT OUTER JOIN "spree_variants" ON "spree_variants"."id" = "spree_line_items"."variant_id" AND "spree_variants"."deleted_at" IS NULL WHERE "spree_variants"."sku" = 'ROR-00011' ORDER BY "spree_orders"."id" ASC LIMIT 1
=> 1

So, you can probably use:

<%= f.text_field :line_items_variant_sku_eq %>

to get what you desire.