loopbackio / loopbackio/loopback-connector-postgresql

Support for overlaps json operator

Open
#570 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

feature
Dominant language
JavaScript
Stars
118
Forks
184
Avg merge
1d 22h
Merged PRs (30d)
5

Description

Suggestion

The connector support the "contains" json/array operator. This operator select rows which contain ALL value in the array.
I propose the support the "overlaps" json/array operator. This operator select rown which contain ANY value in the array.

Use Cases

With this feature, you will be able to use the JSON Array [array operator &&] operator from PostgreSQL(https://www.postgresql.org/docs/current/functions-array.html).

Examples

Row 1: [ A, B]
Row 2: [C, D]
Row 3: [E, A, B ]
Look for any row which overlaps with [C, F] => Should return Row 2

Acceptance criteria

  • Doc
  • Test
  • Code

It is very easy to implement. Unfortunately, I don't know how to use correctly GitHub for PR. So, I can provide code, and i hope someone will be able to PR it.

/test/postgresql.test.js line 294 -------------
 it('should support the "overlaps" where filter for array type field', async () => {
    await Post.create({
      title: 'Overlaps: LoopBack Participates in Hacktoberfest',
      categories: ['OL_LoopBack', 'OL_Announcements'],
    });
    await Post.create({
      title: 'Overlaps: Growing LoopBack Community',
      categories: ['OL_LoopBack', 'OL_Community'],
    });

    const found = await Post.find({where: {and: [
      {
        categories: {'overlaps': ['OL_Super', 'OL_Community']},
      },
    ]}});
    found.map(p => p.title).should.deepEqual(['Overlaps: Growing LoopBack Community']);
  });

/lib/postgresql.js, line 553 ------------------------------------------------------
case 'overlaps':
  return new ParameterizedSQL(columnName + ' && array[' + operatorValue.map(() => '?') + ']::'
    + propertyDefinition.postgresql.dataType,
  operatorValue);
case 'contains':
  return new ParameterizedSQL(columnName + ' @> array[' + operatorValue.map(() => '?') + ']::'
    + propertyDefin

README.md line 601---------------------------------------------------

Operator overlaps
The overlaps operator allow you to query array properties and pick only
rows where the stored value contains any of the items specified by the query.
The operator is implemented using PostgreSQL array operator &&.
Note The fields you are querying must be setup to use the postgresql array data type - see Defining models above.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the existing array operator handling near line 553 of lib/postgresql.js and the related cases in test/postgresql.test.js. Add coverage for the overlaps where filter using the issue's example, then update the operator documentation around README.md line 601. Done means the test passes and the PostgreSQL array overlap behavior is documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, postgresql
Domain
databases
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.