Exporting Smart Segment doesn't work

Help me!
1

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.

Capture d’écran 2021-02-16 à 14.54.56
Capture d’écran 2021-02-16 à 14.54.56880×238 14.4 KB

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
2

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.

3

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

4

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

5

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.

6

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.