Exports only export a partial subset of the collection

Help me!
1

Expected behavior

I have an Organization which has_many Roundtrips.

When I go on this URL {my forest admin URL}/data/Organization/index/record/Organization/405/has-many/Organization-roundtrips and I click ‘Export roundtrips’, I’d expect all the Roundtrips of this Organization to be exported.

Actual behavior

Only 569 roundtrips out of 606 are exported.

Failure Logs

No errors.

Context

Please provide any relevant information about your setup.

  • Package Version: Forest liana 3.3.0
  • Database Dialect: Postgres
  • Database Version: 11
  • Project Name: okarito
2

Hi @remi_okarito,

Thanks for reaching out.

Your forest-rails is in V3, I’d suggest that you try to upgrade first to V5 to see if the problem persists.
Here are the documentation to help you with those operations:

Let us know :pray: Thanks!

3

Hello @anon34731316 and thanks for your quick reply.

I upgraded my forest-rails version to 5.40 and the problem persists. Can you please try to reproduce it ?

Happy to jump on a call if needed - this is blocking some exports we need to create reports for some of our customers.

Let me know,

Remi

4

Hi @remi_okarito,

Nice to meet you.
You found 569 lines in the exported file, right?
Was the total of 606 records coming from the database or from the UI?

Please, if the total number of records was found on the Forest UI, is it possible for you to check the database directly?

Regards

5

Hello Sliman, happy to meet you too.

This is worse than expected:

  • From the UI: 606 records
  • From the database: 1082 records
  • From the export: 569 lines

How can I help you fix this ?

Remi

6

wow

Do you have any scope configured for one or the other collection?
What is the name of your project and the collections involved in this behavior?

It would help by double or maybe triple :slight_smile: checking these numbers.

Can you also share the forest models involved in the has_many, and also the roundtrip count request made against the database.

If you are using paranoid tables, can you check if the request made against the database is including the (eventual) deleted rows (if having a value for the deleteAt field)?

Last but not least: Can you log out the SQL requests made by the API ?

Thanks

7

wow

Troubling indeed

Do you have any scope configured for one or the other collection?
What is the name of your project and the collections involved in this behavior?

Name of the project: okarito
Collections involved: Organization & Roundtrip (couldn’t reproduce on other collections, even with the same setup and an approching number of records)

We have Active Record scopes indeed but I don’t see why it would be linked.

It would help by double or maybe triple :slight_smile: checking these numbers.

Double checked - the discrepancy in the count was about distinct values - sorry for the mistake.

Capture d’écran 2021-01-19 à 14.48.44
Capture d’écran 2021-01-19 à 14.48.442880×552 168 KB

Capture d’écran 2021-01-19 à 14.51.03
Capture d’écran 2021-01-19 à 14.51.032880×490 143 KB

Can you also share the forest models involved in the has_many, and also the roundtrip count request made against the database.

class Forest::Roundtrip
include ForestLiana::Collection
collection :Roundtrip

** many actions and smart fields **
end

class Forest::Organization
include ForestLiana::Collection
collection :Organization

** many actions and smart fields **
end

If you are using paranoid tables, can you check if the request made against the database is including the (eventual) deleted rows (if having a value for the deleteAt field)?

We’re not using paranoid.

Last but not least: Can you log out the SQL requests made by the API ?

I could not get the logs on production, but I’ll send you the SQL request in development via email if that’s ok for you ?

[Edit: sent via the community.forestadmin messenger]

8

Thanks for these inputs.

On our side, we do not reproduce any issue on hasMany count in the UI and in exports.

I am not sure of the issue description now.
It looks that the UI display and exports distinct hasMany records instead of all of the records. Can you confirm this?
Does the export provide you 621 records? (as notified before the export)

Also, I ask myself how the count and the distinct count can be different. As I understand from your models, roundtrips refers to an organization and roundtrips have their own ids.

9

On our side, we do not reproduce any issue on hasMany count in the UI and in exports.

Happy to jump on a call to show you.
In the SQL request, I see the query is limited to 1000 rows - maybe it could be the root cause ? Can I change this limit somewhere to test ?

I am not sure of the issue description now.
It looks that the UI display and exports distinct hasMany records instead of all of the records. Can you confirm this?
Does the export provide you 621 records? (as notified before the export)

The issue is still here: I have 621 records in my database, and the export provides me 569 lines (still, even though the Roundtrip count increased since Friday).

Also, I ask myself how the count and the distinct count can be different. As I understand from your models, roundtrips refers to an organization and roundtrips have their own ids.

Yes no worries forget about this - the organization has 621 roundtrips.

10

Additional information: all Roundtrips created after Jan 5 are missing from the export. It seems there’s a problem of hard limit. Can you try to seed a collection with 1000+ records and try to export it to see if you can reproduce ?

11

Ok, The UI show the 621 records but the export gives 569 records. Interesting to see that the displayed count of 621 does not match the number of exported rows.
Can you catch the log of the count request? This happen when the view is accessed.

Yes we succeed to export more than 1000 records.
I sent you a meet invite to have a call.

12

Hello Sliman, as discussed during our call I managed to reproduce locally with a dump of our production database (only 569 records got exported out of 626 in database and in the UI).

I sent you the SQL count request log via direct message.

13

Hello, do you have any update on this ? I’d be happy to show how it behaves locally to help you identify the bug :slight_smile:

14

Hi @remi_okarito, I see one small difference between the sql count query and the sql export query, there is a

LEFT OUTER JOIN "searches" ON "searches"."id" = "roundtrips"."search_id"

more on the count query.

Can you launch this query below and give me the result please?

SELECT COUNT(DISTINCT "roundtrips"."id") FROM "roundtrips" LEFT OUTER JOIN "users" "validators_roundtrips" ON "validators_roundtrips"."id" = "roundtrips"."validator_id" LEFT OUTER JOIN "roundtrips" "parents_roundtrips" ON "parents_roundtrips"."id" = "roundtrips"."parent_id" INNER JOIN "trips" ON "roundtrips"."id" = "trips"."roundtrip_id" INNER JOIN "user_bookings" ON "trips"."id" = "user_bookings"."trip_id" INNER JOIN "users" ON "user_bookings"."user_id" = "users"."id" INNER JOIN "teams" ON "users"."team_id" = "teams"."id" WHERE "teams"."organization_id" = 405;
15

Result is 626 (correct count)

16

:thinking: ok so now if you launch the export request how many record have you?

SELECT "roundtrips"."id" AS t0_r0, "roundtrips"."created_at" AS t0_r1, "roundtrips"."updated_at" AS t0_r2, "roundtrips"."status" AS t0_r3, "roundtrips"."price_cents" AS t0_r4, "roundtrips"."traveller_id" AS t0_r5, "roundtrips"."validator_id" AS t0_r6, "roundtrips"."cancellation_reason" AS t0_r7, "roundtrips"."search_id" AS t0_r8, "roundtrips"."skyscanner_outbound_leg_id" AS t0_r9, "roundtrips"."skyscanner_inbound_leg_id" AS t0_r10, "roundtrips"."special_request" AS t0_r11, "roundtrips"."additional_baggage_fee_cents" AS t0_r12, "roundtrips"."additional_baggage_fee_currency" AS t0_r13, "roundtrips"."booking_reference" AS t0_r14, "roundtrips"."explanation_for_price" AS t0_r15, "roundtrips"."aerticket_verify_log_id" AS t0_r16, "roundtrips"."tsa" AS t0_r17, "roundtrips"."trip_reason" AS t0_r18, "roundtrips"."aerticket_id" AS t0_r19, "roundtrips"."supplier" AS t0_r20, "roundtrips"."created_by_validator_id" AS t0_r21, "roundtrips"."agent_remark" AS t0_r22, "roundtrips"."booked_at" AS t0_r23, "roundtrips"."booking_links" AS t0_r24, "roundtrips"."paid_price_cents" AS t0_r25, "roundtrips"."provider_attributes" AS t0_r26, "roundtrips"."currency" AS t0_r27, "roundtrips"."flexibility" AS t0_r28, "roundtrips"."parent_id" AS t0_r29, "roundtrips"."ota_name" AS t0_r30, "roundtrips"."parent_segment_ids" AS t0_r31, "validators_roundtrips"."id" AS t1_r0, "validators_roundtrips"."email" AS t1_r1, "validators_roundtrips"."encrypted_password" AS t1_r2, "validators_roundtrips"."reset_password_token" AS t1_r3, "validators_roundtrips"."reset_password_sent_at" AS t1_r4, "validators_roundtrips"."remember_created_at" AS t1_r5, "validators_roundtrips"."sign_in_count" AS t1_r6, "validators_roundtrips"."current_sign_in_at" AS t1_r7, "validators_roundtrips"."last_sign_in_at" AS t1_r8, "validators_roundtrips"."current_sign_in_ip" AS t1_r9, "validators_roundtrips"."last_sign_in_ip" AS t1_r10, "validators_roundtrips"."created_at" AS t1_r11, "validators_roundtrips"."updated_at" AS t1_r12, "validators_roundtrips"."team_id" AS t1_r13, "validators_roundtrips"."name" AS t1_r14, "validators_roundtrips"."surname" AS t1_r15, "validators_roundtrips"."position" AS t1_r16, "validators_roundtrips"."birth_date" AS t1_r17, "validators_roundtrips"."passport_number" AS t1_r18, "validators_roundtrips"."phone_number" AS t1_r19, "validators_roundtrips"."rewards_pool_cents" AS t1_r20, "validators_roundtrips"."is_cfo" AS t1_r21, "validators_roundtrips"."passport_expiration_date" AS t1_r22, "validators_roundtrips"."gender" AS t1_r23, "validators_roundtrips"."is_unmanaged" AS t1_r24, "validators_roundtrips"."avatar" AS t1_r25, "validators_roundtrips"."favorite_language" AS t1_r26, "validators_roundtrips"."deprecated_active" AS t1_r27, "validators_roundtrips"."passport_country_of_issue" AS t1_r28, "validators_roundtrips"."nationality" AS t1_r29, "validators_roundtrips"."status" AS t1_r30, "validators_roundtrips"."VIP" AS t1_r31, "validators_roundtrips"."invited_by_id" AS t1_r32, "parents_roundtrips"."id" AS t2_r0, "parents_roundtrips"."created_at" AS t2_r1, "parents_roundtrips"."updated_at" AS t2_r2, "parents_roundtrips"."status" AS t2_r3, "parents_roundtrips"."price_cents" AS t2_r4, "parents_roundtrips"."traveller_id" AS t2_r5, "parents_roundtrips"."validator_id" AS t2_r6, "parents_roundtrips"."cancellation_reason" AS t2_r7, "parents_roundtrips"."search_id" AS t2_r8, "parents_roundtrips"."skyscanner_outbound_leg_id" AS t2_r9, "parents_roundtrips"."skyscanner_inbound_leg_id" AS t2_r10, "parents_roundtrips"."special_request" AS t2_r11, "parents_roundtrips"."additional_baggage_fee_cents" AS t2_r12, "parents_roundtrips"."additional_baggage_fee_currency" AS t2_r13, "parents_roundtrips"."booking_reference" AS t2_r14, "parents_roundtrips"."explanation_for_price" AS t2_r15, "parents_roundtrips"."aerticket_verify_log_id" AS t2_r16, "parents_roundtrips"."tsa" AS t2_r17, "parents_roundtrips"."trip_reason" AS t2_r18, "parents_roundtrips"."aerticket_id" AS t2_r19, "parents_roundtrips"."supplier" AS t2_r20, "parents_roundtrips"."created_by_validator_id" AS t2_r21, "parents_roundtrips"."agent_remark" AS t2_r22, "parents_roundtrips"."booked_at" AS t2_r23, "parents_roundtrips"."booking_links" AS t2_r24, "parents_roundtrips"."paid_price_cents" AS t2_r25, "parents_roundtrips"."provider_attributes" AS t2_r26, "parents_roundtrips"."currency" AS t2_r27, "parents_roundtrips"."flexibility" AS t2_r28, "parents_roundtrips"."parent_id" AS t2_r29, "parents_roundtrips"."ota_name" AS t2_r30, "parents_roundtrips"."parent_segment_ids" AS t2_r31 FROM "roundtrips" LEFT OUTER JOIN "users" "validators_roundtrips" ON "validators_roundtrips"."id" = "roundtrips"."validator_id" LEFT OUTER JOIN "roundtrips" "parents_roundtrips" ON "parents_roundtrips"."id" = "roundtrips"."parent_id" INNER JOIN "trips" ON "roundtrips"."id" = "trips"."roundtrip_id" INNER JOIN "user_bookings" ON "trips"."id" = "user_bookings"."trip_id" INNER JOIN "users" ON "user_bookings"."user_id" = "users"."id" INNER JOIN "teams" ON "users"."team_id" = "teams"."id" WHERE "teams"."organization_id" = 405 ORDER BY "roundtrips"."id" ASC LIMIT 1000;
17

I’m not sure on how to run this command.

Where do you want me to run it ?

I ran the first COUNT request in psql, but the result of the second (export) request in psql is unreadable. Sorry, I’m not the best SQL expert here.

18

Ok so run this please :pray:

SELECT COUNT(*) FROM (SELECT "roundtrips"."id" AS t0_r0, "roundtrips"."created_at" AS t0_r1, "roundtrips"."updated_at" AS t0_r2, "roundtrips"."status" AS t0_r3, "roundtrips"."price_cents" AS t0_r4, "roundtrips"."traveller_id" AS t0_r5, "roundtrips"."validator_id" AS t0_r6, "roundtrips"."cancellation_reason" AS t0_r7, "roundtrips"."search_id" AS t0_r8, "roundtrips"."skyscanner_outbound_leg_id" AS t0_r9, "roundtrips"."skyscanner_inbound_leg_id" AS t0_r10, "roundtrips"."special_request" AS t0_r11, "roundtrips"."additional_baggage_fee_cents" AS t0_r12, "roundtrips"."additional_baggage_fee_currency" AS t0_r13, "roundtrips"."booking_reference" AS t0_r14, "roundtrips"."explanation_for_price" AS t0_r15, "roundtrips"."aerticket_verify_log_id" AS t0_r16, "roundtrips"."tsa" AS t0_r17, "roundtrips"."trip_reason" AS t0_r18, "roundtrips"."aerticket_id" AS t0_r19, "roundtrips"."supplier" AS t0_r20, "roundtrips"."created_by_validator_id" AS t0_r21, "roundtrips"."agent_remark" AS t0_r22, "roundtrips"."booked_at" AS t0_r23, "roundtrips"."booking_links" AS t0_r24, "roundtrips"."paid_price_cents" AS t0_r25, "roundtrips"."provider_attributes" AS t0_r26, "roundtrips"."currency" AS t0_r27, "roundtrips"."flexibility" AS t0_r28, "roundtrips"."parent_id" AS t0_r29, "roundtrips"."ota_name" AS t0_r30, "roundtrips"."parent_segment_ids" AS t0_r31, "validators_roundtrips"."id" AS t1_r0, "validators_roundtrips"."email" AS t1_r1, "validators_roundtrips"."encrypted_password" AS t1_r2, "validators_roundtrips"."reset_password_token" AS t1_r3, "validators_roundtrips"."reset_password_sent_at" AS t1_r4, "validators_roundtrips"."remember_created_at" AS t1_r5, "validators_roundtrips"."sign_in_count" AS t1_r6, "validators_roundtrips"."current_sign_in_at" AS t1_r7, "validators_roundtrips"."last_sign_in_at" AS t1_r8, "validators_roundtrips"."current_sign_in_ip" AS t1_r9, "validators_roundtrips"."last_sign_in_ip" AS t1_r10, "validators_roundtrips"."created_at" AS t1_r11, "validators_roundtrips"."updated_at" AS t1_r12, "validators_roundtrips"."team_id" AS t1_r13, "validators_roundtrips"."name" AS t1_r14, "validators_roundtrips"."surname" AS t1_r15, "validators_roundtrips"."position" AS t1_r16, "validators_roundtrips"."birth_date" AS t1_r17, "validators_roundtrips"."passport_number" AS t1_r18, "validators_roundtrips"."phone_number" AS t1_r19, "validators_roundtrips"."rewards_pool_cents" AS t1_r20, "validators_roundtrips"."is_cfo" AS t1_r21, "validators_roundtrips"."passport_expiration_date" AS t1_r22, "validators_roundtrips"."gender" AS t1_r23, "validators_roundtrips"."is_unmanaged" AS t1_r24, "validators_roundtrips"."avatar" AS t1_r25, "validators_roundtrips"."favorite_language" AS t1_r26, "validators_roundtrips"."deprecated_active" AS t1_r27, "validators_roundtrips"."passport_country_of_issue" AS t1_r28, "validators_roundtrips"."nationality" AS t1_r29, "validators_roundtrips"."status" AS t1_r30, "validators_roundtrips"."VIP" AS t1_r31, "validators_roundtrips"."invited_by_id" AS t1_r32, "parents_roundtrips"."id" AS t2_r0, "parents_roundtrips"."created_at" AS t2_r1, "parents_roundtrips"."updated_at" AS t2_r2, "parents_roundtrips"."status" AS t2_r3, "parents_roundtrips"."price_cents" AS t2_r4, "parents_roundtrips"."traveller_id" AS t2_r5, "parents_roundtrips"."validator_id" AS t2_r6, "parents_roundtrips"."cancellation_reason" AS t2_r7, "parents_roundtrips"."search_id" AS t2_r8, "parents_roundtrips"."skyscanner_outbound_leg_id" AS t2_r9, "parents_roundtrips"."skyscanner_inbound_leg_id" AS t2_r10, "parents_roundtrips"."special_request" AS t2_r11, "parents_roundtrips"."additional_baggage_fee_cents" AS t2_r12, "parents_roundtrips"."additional_baggage_fee_currency" AS t2_r13, "parents_roundtrips"."booking_reference" AS t2_r14, "parents_roundtrips"."explanation_for_price" AS t2_r15, "parents_roundtrips"."aerticket_verify_log_id" AS t2_r16, "parents_roundtrips"."tsa" AS t2_r17, "parents_roundtrips"."trip_reason" AS t2_r18, "parents_roundtrips"."aerticket_id" AS t2_r19, "parents_roundtrips"."supplier" AS t2_r20, "parents_roundtrips"."created_by_validator_id" AS t2_r21, "parents_roundtrips"."agent_remark" AS t2_r22, "parents_roundtrips"."booked_at" AS t2_r23, "parents_roundtrips"."booking_links" AS t2_r24, "parents_roundtrips"."paid_price_cents" AS t2_r25, "parents_roundtrips"."provider_attributes" AS t2_r26, "parents_roundtrips"."currency" AS t2_r27, "parents_roundtrips"."flexibility" AS t2_r28, "parents_roundtrips"."parent_id" AS t2_r29, "parents_roundtrips"."ota_name" AS t2_r30, "parents_roundtrips"."parent_segment_ids" AS t2_r31 FROM "roundtrips" LEFT OUTER JOIN "users" "validators_roundtrips" ON "validators_roundtrips"."id" = "roundtrips"."validator_id" LEFT OUTER JOIN "roundtrips" "parents_roundtrips" ON "parents_roundtrips"."id" = "roundtrips"."parent_id" INNER JOIN "trips" ON "roundtrips"."id" = "trips"."roundtrip_id" INNER JOIN "user_bookings" ON "trips"."id" = "user_bookings"."trip_id" INNER JOIN "users" ON "user_bookings"."user_id" = "users"."id" INNER JOIN "teams" ON "users"."team_id" = "teams"."id" WHERE "teams"."organization_id" = 405 ORDER BY "roundtrips"."id" ASC LIMIT 1000) as test;
19

The result for this request is 1000

20

Hi @remi_okarito,

I just did a quick test on my end to try reproducing your issue, with Customer has many Orders (~2000 per customer), with random created_at, to be as close as possible of

Additional information: all Roundtrips created after Jan 5 are missing from the export. It seems there’s a problem of hard limit. Can you try to seed a collection with 1000+ records and try to export it to see if you can reproduce ?

Still, no luck, I’m able to export all the request associated.

Also,

Hello Sliman, as discussed during our call I managed to reproduce locally with a dump of our production database (only 569 records got exported out of 626 in database and in the UI).

Would you mind sharing the roundtrips model structure? I could do a test to have a setup as similar as possible to yours.