I'm trying to optimize a report building function, and as it always does, the querying takes most of the time, so I try to speed it up a bit.
Now I found a solution for the problem, I'm just trying to implement it, but I want to use Ransack for it, so I won't have to write sql into the code.
Here is the query that is written
@search = Spree::Order.complete
.includes(:payments, :refunds).where(payments: Spree::Payment.completed)
.references(:payments, :refunds)
.ransack(@search_pattern)
and here is the SQL code that it generates
SELECT "spree_orders"."id" AS t0_r0,
"spree_orders"."number" AS t0_r1,
"spree_orders"."item_total" AS t0_r2,
"spree_orders"."total" AS t0_r3,
"spree_orders"."state" AS t0_r4,
"spree_orders"."adjustment_total" AS t0_r5,
"spree_orders"."user_id" AS t0_r6,
"spree_orders"."completed_at" AS t0_r7,
"spree_orders"."bill_address_id" AS t0_r8,
"spree_orders"."ship_address_id" AS t0_r9,
"spree_orders"."payment_total" AS t0_r10,
"spree_orders"."shipment_state" AS t0_r11,
"spree_orders"."payment_state" AS t0_r12,
"spree_orders"."email" AS t0_r13,
"spree_orders"."special_instructions" AS t0_r14,
"spree_orders"."created_at" AS t0_r15,
"spree_orders"."updated_at" AS t0_r16,
"spree_orders"."currency" AS t0_r17,
"spree_orders"."last_ip_address" AS t0_r18,
"spree_orders"."created_by_id" AS t0_r19,
"spree_orders"."shipment_total" AS t0_r20,
"spree_orders"."additional_tax_total" AS t0_r21,
"spree_orders"."promo_total" AS t0_r22,
"spree_orders"."channel" AS t0_r23,
"spree_orders"."included_tax_total" AS t0_r24,
"spree_orders"."item_count" AS t0_r25,
"spree_orders"."approver_id" AS t0_r26,
"spree_orders"."approved_at" AS t0_r27,
"spree_orders"."confirmation_delivered" AS t0_r28,
"spree_orders"."considered_risky" AS t0_r29,
"spree_orders"."token" AS t0_r30,
"spree_orders"."canceled_at" AS t0_r31,
"spree_orders"."canceler_id" AS t0_r32,
"spree_orders"."store_id" AS t0_r33,
"spree_orders"."state_lock_version" AS t0_r34,
"spree_orders"."taxable_adjustment_total" AS t0_r35,
"spree_orders"."non_taxable_adjustment_total" AS t0_r36,
"spree_orders"."abandoned_cart_email_sent_at" AS t0_r37,
"spree_orders"."price_changed" AS t0_r38,
"spree_orders"."merge_order_id" AS t0_r39,
"spree_orders"."external_id" AS t0_r40,
"spree_orders"."external_number" AS t0_r41,
"spree_orders"."external_source" AS t0_r42,
"spree_orders"."sap_customer_id" AS t0_r43,
"spree_orders"."store_owner_notification_delivered" AS t0_r44,
"spree_payments"."id" AS t1_r0,
"spree_payments"."amount" AS t1_r1,
"spree_payments"."order_id" AS t1_r2,
"spree_payments"."source_type" AS t1_r3,
"spree_payments"."source_id" AS t1_r4,
"spree_payments"."payment_method_id" AS t1_r5,
"spree_payments"."state" AS t1_r6,
"spree_payments"."response_code" AS t1_r7,
"spree_payments"."avs_response" AS t1_r8,
"spree_payments"."created_at" AS t1_r9,
"spree_payments"."updated_at" AS t1_r10,
"spree_payments"."number" AS t1_r11,
"spree_payments"."cvv_response_code" AS t1_r12,
"spree_payments"."cvv_response_message" AS t1_r13,
"spree_refunds"."id" AS t2_r0,
"spree_refunds"."payment_id" AS t2_r1,
"spree_refunds"."amount" AS t2_r2,
"spree_refunds"."transaction_id" AS t2_r3,
"spree_refunds"."created_at" AS t2_r4,
"spree_refunds"."updated_at" AS t2_r5,
"spree_refunds"."refund_reason_id" AS t2_r6,
"spree_refunds"."reimbursement_id" AS t2_r7
FROM "spree_orders"
left outer join "spree_payments"
ON "spree_payments"."order_id" = "spree_orders"."id"
left outer join "spree_payments" "payments_spree_orders_join"
ON "payments_spree_orders_join"."order_id" =
"spree_orders"."id"
left outer join "spree_refunds"
ON "spree_refunds"."payment_id" =
"payments_spree_orders_join"."id"
WHERE "spree_orders"."completed_at" IS NOT NULL
AND "spree_orders"."id" IN (SELECT "spree_payments"."order_id"
FROM "spree_payments"
WHERE "spree_payments"."state" = 'completed'
ORDER BY "spree_payments"."created_at" ASC)
AND ( "spree_orders"."completed_at" >= '2022-04-01 07:00:00'
AND "spree_orders"."completed_at" <= '2022-05-01 06:59:59.999999' )
ORDER BY "spree_orders"."completed_at" DESC
As you can see, it lists all values in the tables, while I just want a few, and one where clause.
Is there a way to select specific values from the table in Ruby or Ransack?
Any help would be greatly appreciated! Thank you!
CodePudding user response：
I believe you need to call select on the result.
For Example:
@search= Spree::Order.complete.ransack(@search_pattern)
@table_info = @search.result
.joins(:payments)
.left_joins(:refunds)
.where(payments: Spree::Payment.completed) # not sure how this is working
.select(:a,:b,:c,:d)
Note: I substituted your includes for an INNER JOIN and an OUTER JOIN because includes references select is just a JOIN and OUTER JOIN a WHERE condition on that JOIN is an INNER JOIN.
We could probably further simplify (and improve performance) by removing the subquery as follows:
@table_info = @search.result
.joins(:payments)
.left_joins(:refunds)
.where(spree_payments: {state: 'completed'})
.select(:a,:b,:c,:d)