Exporting Smart Segment doesn't work

Expected behavior

Upon click on “Export invoices” in my Smart Segment “:postbox: Credits to refund and send”, I expect to download a CSV file with all the records in this segment.

Actual behavior

A CSV is exported with only 1 line (headers). Zero records are included in the CSV.
My records do show in the Smart Segment and the popup gives the correct number of records.

Failure Logs

No failure logs.

Context

Please provide any relevant information about your setup.

My smart segment code inside my Invoice collection:

segment '📮 Credits to refund and send' do
    sub_request =
      ::Invoice.credit_memo
               .eager_load(:refunds, :payment_mean)
               .where.not(refunds: nil)
               .where.not(payment_means: { iban: nil })
               .select('DISTINCT ON(refunds.invoice_id) invoices.*, refunds.status AS refunds_status')
               .order('refunds.invoice_id, refunds.id')
    { id: ::Invoice.from(sub_request, :invoices).where('invoices.refunds_status != 12').ids }
  end
  • Package Version: Forest Liana v5
  • Database Dialect: Postgres 11
  • Project Name: okarito

Hello @remi_okarito,

I just did a quick test on my end, but I’m sadly not able to reproduce. Exporting a smart segment from the rails app seems to work on my end.

I just discussed with @Steve_Bunlon and I’ll give another shot at this today once we’ve got all the needed data to reproduce.

I have this line in my logs: “Scoped order is ignored, it’s forced to be batch order.”
Does it help ?

Hi @remi_okarito,

Can you please log the SQL request from the export process (this request looks returning 0 records)?
Also, can you please log the SQL request which fetch the segments records that are displayed on the UI (this request is working)?

Comparing them should be useful.

Best

Hey:

SQL request for fetching the segment

SQL (90.7ms)  SELECT  "invoices"."id" AS t0_r0, "invoices"."number" AS t0_r1, "invoices"."url" AS t0_r2, "invoices"."created_at" AS t0_r3, "invoices"."updated_at" AS t0_r4, "invoices"."base_64_file" AS t0_r5, "invoices"."billing_entity_id" AS t0_r6, "invoices"."payment_mean_id" AS t0_r7, "invoices"."kind" AS t0_r8, "billing_entities"."id" AS t1_r0, "billing_entities"."billing_address_id" AS t1_r1, "billing_entities"."organization_id" AS t1_r2, "billing_entities"."vat_number" AS t1_r3, "billing_entities"."billing_email_addresses" AS t1_r4, "billing_entities"."created_at" AS t1_r5, "billing_entities"."updated_at" AS t1_r6, "billing_entities"."name" AS t1_r7, "billing_entities"."quickbooks_id" AS t1_r8, "billing_entities"."billing_frequency" AS t1_r9, "payment_means"."id" AS t2_r0, "payment_means"."organization_id" AS t2_r1, "payment_means"."payment_method_type" AS t2_r2, "payment_means"."mandate_number" AS t2_r3, "payment_means"."created_at" AS t2_r4, "payment_means"."updated_at" AS t2_r5, "payment_means"."name" AS t2_r6, "payment_means"."iban" AS t2_r7 FROM "invoices" LEFT OUTER JOIN "billing_entities" ON "billing_entities"."id" = "invoices"."billing_entity_id" LEFT OUTER JOIN "payment_means" ON "payment_means"."id" = "invoices"."payment_mean_id" WHERE "invoices"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) ORDER BY "invoices"."id" DESC LIMIT $12 OFFSET $13  [["id", 2331], ["id", 2341], ["id", 2366], ["id", 2369], ["id", 2370], ["id", 2384], ["id", 2385], ["id", 2393], ["id", 2396], ["id", 2401], ["id", 2407], ["LIMIT", 210], ["OFFSET", 0]]

SQL request for the export:

Scoped order is ignored, it's forced to be batch order.
  SQL (11.5ms)  SELECT  "invoices"."id" AS t0_r0, "invoices"."number" AS t0_r1, "invoices"."url" AS t0_r2, "invoices"."created_at" AS t0_r3, "invoices"."updated_at" AS t0_r4, "invoices"."base_64_file" AS t0_r5, "invoices"."billing_entity_id" AS t0_r6, "invoices"."payment_mean_id" AS t0_r7, "invoices"."kind" AS t0_r8, "billing_entities"."id" AS t1_r0, "billing_entities"."billing_address_id" AS t1_r1, "billing_entities"."organization_id" AS t1_r2, "billing_entities"."vat_number" AS t1_r3, "billing_entities"."billing_email_addresses" AS t1_r4, "billing_entities"."created_at" AS t1_r5, "billing_entities"."updated_at" AS t1_r6, "billing_entities"."name" AS t1_r7, "billing_entities"."quickbooks_id" AS t1_r8, "billing_entities"."billing_frequency" AS t1_r9, "payment_means"."id" AS t2_r0, "payment_means"."organization_id" AS t2_r1, "payment_means"."payment_method_type" AS t2_r2, "payment_means"."mandate_number" AS t2_r3, "payment_means"."created_at" AS t2_r4, "payment_means"."updated_at" AS t2_r5, "payment_means"."name" AS t2_r6, "payment_means"."iban" AS t2_r7 FROM "invoices" LEFT OUTER JOIN "billing_entities" ON "billing_entities"."id" = "invoices"."billing_entity_id" LEFT OUTER JOIN "payment_means" ON "payment_means"."id" = "invoices"."payment_mean_id" WHERE "invoices"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) AND (LOWER("invoices"."url") LIKE '%%' OR LOWER("invoices"."base_64_file") LIKE '%%') ORDER BY "invoices"."id" ASC LIMIT $12  [["id", 2331], ["id", 2341], ["id", 2366], ["id", 2369], ["id", 2370], ["id", 2384], ["id", 2385], ["id", 2393], ["id", 2396], ["id", 2401], ["id", 2407], ["LIMIT", 1000]]

Some differences indeed at the end of the query. I don’t understand why these differences happen.

Looking at the WHERE clauses we can see differences (and it should not since the same data is required)

working one:

WHERE "invoices"."id" 
  IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
ORDER BY "invoices"."id" DESC
LIMIT $12
OFFSET $13  
    [["id", 2331], ["id", 2341], ["id", 2366], ["id", 2369], ["id", 2370], ["id", 2384], ["id", 2385], ["id", 2393], ["id", 2396], ["id", 2401], ["id", 2407], ["LIMIT", 210], ["OFFSET", 0]]

failing one:

WHERE "invoices"."id"
  IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
  AND (LOWER("invoices"."url") LIKE '%%'
  OR LOWER("invoices"."base_64_file") LIKE '%%')
ORDER BY "invoices"."id" ASC
LIMIT $12
    [["id", 2331], ["id", 2341], ["id", 2366], ["id", 2369], ["id", 2370], ["id", 2384], ["id", 2385], ["id", 2393], ["id", 2396], ["id", 2401], ["id", 2407], ["LIMIT", 1000]]

There are few differences (LIKE %% looks strange for example) we should analyse to understand the issue.