Add filter to show records with duplicate values in a set of columns

[kgodey]

Problem

The "Working with Columns" design spec adds a filter for duplicate values for a column, to assist users with resolving non-unique values when they want to set a unique constraint for a column.

Proposed solution

We need to add a filter for this to our Record API filters.

Additional context

• Blocked by database issue: DB function to get rows with duplicate values #515
• Frontend issue: Add UI to filter on the presence of duplicate values in one column #770
• Design issue: Design for editing column properties and table constraints #324
• Design discussion: Working with Columns #436
• Mathesar's API Standards
• We're using our fork of sqlalchemy-filters to provide filtering. See: https://github.com/centerofci/sqlalchemy-filters.

[mathemancer]

I think that simply finding the rows that have duplicate values should probably be its own issue in the backend. It will be a bit complex (the best way I know of involves a window function at the DB level).

[kgodey]

@mathemancer It seems fairly simple according to this Stack Overflow answer: https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table. Do you see any issues with the recommended approach?

[mathemancer]

@mathemancer It seems fairly simple according to this Stack Overflow answer: https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table. Do you see any issues with the recommended approach?

The problem with the queries from that post will be that you won't be able to SELECT all the columns (as noted in some of the comments) in the resulting table. From the PostgreSQL docs:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

In the case where we want to find entire rows that are duplicates (shouldn't happen for tables with mathesar_id), the queries suggested (or similar) work. If we want to find rows where only some subset of the columns are duplicates, this means that to produce the rows in their entirety for the user to work on (assuming that we want to show the whole rows; otherwise, how would they know which they might want to delete or modify?), we'd need to do some more querying, or do fancy things with CTEs or window functions (or joining on a sub-select perhaps).

[kgodey]

@mathemancer Thanks! I created a separate DB issue.

[eito-fis]

What should the actual interface for this look like? Is setting get_duplicate=True on the record list endpoint, then returning the appropriately paginated and offset duplicates alright?

[kgodey]

@eito-fis I think it should be a filter like everything else, it should just be another type of filter. I think currently those are being passed in as a filters argument, right?

[eito-fis]

@kgodey I initially didn't think filters would work, but I think it might end up being pretty clean. Will give it a shot 👍

[eito-fis]

@kgodey Having gotten started, I'm again unsure on how to implement this. I think the core issue is that all of the currently supported filtering parameters are things that could fit inside of a WHERE condition, and as a result all work with our conjunctions and nesting of conditionals.

To support the duplication filter in the nested structure we could:

• Allow it to appear anywhere in the filter expression. This would raise some questions about how to define what columns to check duplicates on.
• Making it a weird special case, where we could only specify it once at the top level of the filters and no where else.

If we don't need the nested interactions, I think a separate check_duplicate_cols parameters that takes a list of columns would be best, since avoid on special cases / extra validation.

If we did want to be able to use conjunctions, I think having check_duplicate_cols would still be useful, since we wouldn't have to deal with different definitions for columns to filter on. Alternatively, we could enforce that all duplication filters look at the same columns, or we update our filtering to be able to handle multiple sets of duplication columns. The latter seems the nicest of these options, but would also be the most complicated to implement.

Sorry - this got a bit longer than expected. I guess the core question is - do we need to be able to handle the is_duplicate condition inside conjunctions? Or can it just be a single, top level filter?

[kgodey]

If it's easier to do a single top level condition (which it sounds like it is), let's do that. We can refactor if needed when we are finalizing our API v1 structure.

[eito-fis]

@kgodey as a separate parameter, or as part of the filters?

[kgodey]

If it's the same effort to implement, let's make it part of the filters.

[kgodey]

Fixed by #569. @eito-fis FYI you need to have the word "Fixes" before each issue number to auto-close issues.

[eito-fis]

I think I had 'fixes' instead of 'Fixes' ):

[kgodey]

@eito-fis You had both issues comma separated (Fixes A, B), you need to have "fixes A, fixes B".