Rows in a table go missing when multiple relationships are attached to a table.

[ConorWebb96]

  Checklist

• [X] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 2.27.5
  • App Version: 2.27.5

Describe the bug Whenever multiple relationships are attached to a table, not all rows will be displayed. Additionally, you can't scroll down as you normally would to be able to get more rows. Also rows for relationship plus rows in the table don't display anywhere near the 5000 row limit that is set by default.

To Reproduce Steps to reproduce the behavior:

1. Import app attached below as a new app
2. Create new postgres database
3. Import the tables
4. Run the insert queries to generate data for the tables
5. Reconfigure datasource to work with your new locally setup one one
6. Go to the builder and select the regions table
7. See issue 10 region rows should be displayed, only one available.

Expected behavior All rows within the table should be visible. A decent amount of relationship related rows should also be visible.

Screenshots Builder row display for regions table

Database row display for regions table

Network request screenshot

App Export replicated-issue.tar.gz

CREATE TABLE regions (
    region_id SERIAL PRIMARY KEY,
    region_name VARCHAR(255) NOT NULL
);

CREATE TABLE countries (
    country_id SERIAL PRIMARY KEY,
    country_name VARCHAR(255) NOT NULL,
    region_id INT NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

CREATE TABLE cities (
    city_id SERIAL PRIMARY KEY,
    city_name VARCHAR(255) NOT NULL,
    region_id INT NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

CREATE TABLE languages (
    language_id SERIAL PRIMARY KEY,
    language_name VARCHAR(255) NOT NULL,
    region_id INT NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

CREATE TABLE tourist_attractions (
    attraction_id SERIAL PRIMARY KEY,
    attraction_name VARCHAR(255) NOT NULL,
    region_id INT NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

CREATE TABLE industries (
    industry_id SERIAL PRIMARY KEY,
    industry_name VARCHAR(255) NOT NULL,
    region_id INT NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
);

INSERT INTO regions (region_name)
SELECT 'Region ' || generate_series(1, 10);

INSERT INTO countries (country_name, region_id)
SELECT 'Country ' || generate_series(1, 10),
       regions.region_id
FROM generate_series(1, 10)
CROSS JOIN (
    SELECT region_id FROM regions ORDER BY RANDOM() LIMIT 50
) AS regions;

INSERT INTO cities (city_name, region_id)
SELECT 'City ' || generate_series(1, 10),
       (SELECT region_id FROM regions ORDER BY RANDOM() LIMIT 1)
FROM generate_series(1, 10);

INSERT INTO languages (language_name, region_id)
SELECT 'Language ' || generate_series(1, 10),
       regions.region_id
FROM generate_series(1, 10)
CROSS JOIN (
    SELECT region_id FROM regions ORDER BY RANDOM() LIMIT 50
) AS regions;

INSERT INTO tourist_attractions (attraction_name, region_id)
SELECT 'Attraction ' || generate_series(1, 10),
       regions.region_id
FROM generate_series(1, 10)
CROSS JOIN (
    SELECT region_id FROM regions ORDER BY RANDOM() LIMIT 50
) AS regions;

INSERT INTO industries (industry_name, region_id)
SELECT 'Industry ' || generate_series(1, 10),
       regions.region_id
FROM generate_series(1, 10)
CROSS JOIN (
    SELECT region_id FROM regions ORDER BY RANDOM() LIMIT 50
) AS regions;

Additional context The example table creates + insert queries should insert around 4410 rows, looking at the regions table only 153 rows are displayed out of these 4410 rows. It should be able to display them all.

[linear[bot]]

BUDI-8311 Rows in a table go missing when multiple relationships are attached to a table.

[aptkingston]

The grid requests pages of 100 so it's probably something to do with all the joins required to get all the relationships - probably getting 100 total rows including those joined, so it hits the limit when it's only got a few (or in this case 1) row from the table in question. Absolutely waffling off the top of my head here but I reckon it's possible. @mike12345567 could certainly give a more accurate answer. But my bet is the limit is applied "too early" if you like, although it probably needs to be this way to avoid performance issues. Like if you had such a ridiculous relationship setup that you needed to fetch 1 million rows in order to get 100 fully enriched rows in the table in question, then it would just never work.

[mike12345567]

Commented further explanation on this one as part of the Linear issue - going into depth on the problem but basically its due to the level of cross-joining this table has, it can generate literally billions of responses which basically cannot be retrieved in Budibase.

[benaunt]

Could this be fixed by restricting what tables return results per each defined relationship?

Eg in this example:

When you define the region -> industry relationship within Budibase. The relationship builder could have a toggle to not return results from other relationships linked to the region table. So whenever you want to use the industry data you will have access to the region table and its defined columns, but not the results from other relationships registered against the region table?

[mike12345567]

Could this be fixed by restricting what tables return results per each defined relationship?

Eg in this example:

When you define the region -> industry relationship within Budibase. The relationship builder could have a toggle to not return results from other relationships linked to the region table. So whenever you want to use the industry data you will have access to the region table and its defined columns, but not the results from other relationships registered against the region table?

Yes - this would be one possible solution to the problem - making it possible to select if a relationship is bi-directional or you only wish to reference one direction of it.