Home > Net >  Is there a way to query specific values from psql in Ruby Ransack?
Is there a way to query specific values from psql in Ruby Ransack?

Time:05-10

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)
  • Related