Join with more flexible matching criteria

[terefang]

Is your feature request related to a problem? Please describe.

Today qsv join allows a full complement of structural join options (left, right, outer, etc etc) but only a "case-insensitive" match option.

for the following use cases (join criteria), i had to import/join/export via sqlite instead of using the excellent qsv:

• right starts-with left – in sql: right_val LIKE CONCAT(left_val,'%')
• right contains left – in sql: right_val LIKE CONCAT('%',left_val,'%')
• right ends-with left – in sql: right_val LIKE CONCAT('%',left_val)
• left starts-with right – in sql: left_val LIKE CONCAT(right_val,'%')
• left contains right – in sql: left_val LIKE CONCAT('%',right_val,'%')
• left ends-with right – in sql: left_val LIKE CONCAT('%',right_val)

Describe the solution you'd like

• an option to specify the match direction — eg --match-direction=LTR|RTL
• an option to sepcify the match type — eg --match-type=startswith|contains|endswith

Describe alternatives you've considered

the current workaround is to import both csv files into sqlite and export the joined product.

[jqnatividad]

Thanks for the detailed request @terefang .

Out of curiosity, have you tried the joinp command or even doing the join using sqlp?

[terefang]

if i understand joinp correctly, it does not solve my use-cases.

[terefang]

just tried out sqlp.

The error Message is "Unsupported SQL join constraint" for select * from "report" r left join "report2" r2 on (r2.val2 like concat(r.val1,'%'))

[jqnatividad]

Hi @terefang , given that join uses a hashmap as its main data structure, it'd be a major refactor to implement pattern matching there.
I'm currently investigating implementing your request in joinp instead as it uses Polars and it has built-ins for pattern matching.

[jqnatividad]

Hi @terefang , I created some tests illustrating how to do joins per your request using sqlp and joinp...

They both use cross joins though...

[ondohotola]

Where are the tests?

[jqnatividad]

They're here @ondohotola
23938d4

[ondohotola]

Thank you.

[jqnatividad]

Closing this for now...

FYI, I did try to extend joinp to have --match-type and --match-dir but it was not feasible as it ultimately required a cross join internally as the --filter-left and --filter-right can only refer to columns on the left and right respectively.

I also attempted to do it in sqlp, but Polars SQL currently only allows literals after LIKE and ILIKE and even CONCAT caused a run-time Polars SQL error.

As Polars SQL expands over time, I'll revisit this.

[terefang]

i will test this after my vacation

[jqnatividad]

In the interim, I created a discussion based on your original request so other users can find the workarounds.

[jqnatividad]

@terefang , you may want to check out the new non-equi join support in joinp...

#2409