feat(server): infix search for originalPath

[C-Otto]

For uploaded files, the originalPath column sadly doesn't contain anything interesting. However, for users of external libraries, the path might contain very helpful information:

• /home/user/pictures/vacations/1999/southpole/
• /home/user/pictures/weddings/2024/adam_and_eve/
• /home/user/pictures/random_snapshots/
• ...

Query URLs like the following already work for originalFilename:

/search?query={"originalFileName":"IMG_123"}

For users who dare to manually change this to include originalPath instead, the pre-PR code also works, but only checks for an exact match of the value. This PR merely changes this to look for infixes, mirroring the behavior of originalFileName. This way, one can limit searches accordingly:

/search?query={"originalPath":"adam_and_eve"}

[alextran1502]

I believe you have to create similar index for the original file name column as well

[C-Otto]

I believe you have to create similar index for the original file name column as well

You mean originalPath? I see both a plain index (1705306747072-AddOriginalFileNameIndex) and a trigram index (1709608140355-AddAssetOriginalPathTrigramIndex). Should I add both?

Note that the file name of the trigram migration is misleading, it's adding an index for originalFileName, not originalPath.

[mertalev]

Just the trigram index is fine. I'm not sure what the normal index for the filename is used for, but it isn't for this.

[C-Otto]

@mertalev I added the index (and rebased). This is ready for review!

[C-Otto]

Is there anything I should do regarding the changelog?

[mertalev]

This looks good, but there will likely be a tree-structured index as part of the work toward optimizing queries for the new folder view. The ltree index for this will support full-text search along with more advanced patterns like here. I'd like to see if infix search can use this index instead so we don't need both.

[alextran1502]

@mertalev, do we have any update on your findings?

[mertalev]

I can do some testing today and make a PR for the ltree index.

[C-Otto]

We could also merge this and deal with index changes later?

[mertalev]

The relevant change is #12214, so I guess it depends on when that's ready. I'm fine with adding originalPath to the search builder now without the index, but it'd be a bit wasteful to make a bulky trigram index now only to drop it in a week or two.

[NerdyPunkDad]

@C-Otto It looks like this pull request was deleted when you deleted a branch repository. Do you mind, if I copy your code and rebase it to work with the current version of immich. I am happy to tag you in the PR when I resubmit the code...

@mertalev

• are you still planning to submit #12214 (or a similar PR)? -Do you think it would make sense for me to make the index optional that way users who search the path frequently can turn the index on without causing any bloat for other users?

[C-Otto]

@NerdyPunkDad I just pushed the code to https://github.com/immich-app/immich/tree/original-path-infix