我正在尝试优化报表构建功能，并且与往常一样，查询花费了大部分时间，因此我尝试加快一点速度。
现在我找到了问题的解决方案，我只是想实现它，但我想使用Ransack，所以我不必在代码中编写sql。
下面是写为
的查询@search = Spree::Order.complete
.includes(:payments, :refunds).where(payments: Spree::Payment.completed)
.references(:payments, :refunds)
.ransack(@search_pattern)
下面是生成
的SQL代码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
如您所见，它列出了表中的所有值，而我只想要几个和一个where子句。
是否有一种方法从Ruby或Ransack表中选择特定的值?
任何帮助都将非常感激!谢谢你！
我认为您需要在result上调用select。
例如:
@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)
注意:我将您的includes替换为INNER JOIN和OUTER JOIN，因为includes+references+select只是一个JOIN和OUTER JOIN +该JOIN的WHERE条件是一个INNER JOIN。
我们可以通过删除子查询进一步简化(并提高性能)，如下所示:
@table_info = @search.result
.joins(:payments)
.left_joins(:refunds)
.where(spree_payments: {state: 'completed'})
.select(:a,:b,:c,:d)