Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Left join brings value when we expect NULL/undefined value #79

Closed
calebeaires opened this issue Aug 5, 2018 · 3 comments
Closed

Left join brings value when we expect NULL/undefined value #79

calebeaires opened this issue Aug 5, 2018 · 3 comments

Comments

@calebeaires
Copy link

@calebeaires calebeaires commented Aug 5, 2018

Describe the bug
Values is not null when there is no associated link

Expected behavior
Commonly when doing LEFT JOIN, if there is no associeted entry in the RIGHT table for a particular field, we expected to get a NULL value


Database representation

Users table

userId tagId email
1 1 tagone@gmail.com

Tags table

tagId tagName
1 TagOne
2 TagTwo

Query

nSQL('Tags').('select', [
        'Tags.tagId',
        'Tags.tagName',
        'Users.userId',
        'Users.email',
      ])
      .join({
        type: 'left',
        table: 'Users',
        where: ['Users.tagId', '=', 'Tags.tagId'],
      })
      .exec().then(rows => {
        console.log(rows);
        return rows;
      });

Result

[ { 'Tags.tagId': 1,
    'Tags.tagName': 'TagONE,
    'Users.userId': 1,
'Users.email': 'tagone@gmail.com' },
  { 'Tags.tagId': 2,
    'Tags.tagName': 'TagTwo',
    'Users.userId': 1,
'Users.email': 'tagone@gmail.com'  } ]

Result in a table view

tagId tagName userId email
1 TagOne 1 tagone@gmail.com
2 TagTwo 1 tagone@gmail.com

Expected

[ { 'Tags.tagId': 1,
    'Tags.tagName': 'TagONE,
    'Users.userId': 1,
'Users.userId': 1,
'Users.email': 'tagone@gmail.com'  },
  { 'Tags.tagId': 2,
    'Tags.tagName': 'TagTwo',
    'Users.userId': undefined,
'Users.email': undefined } ]

Expected in a table view

tagId tagName userId email
1 TagOne 1 tagone@gmail.com
2 TagTwo
@only-cliches
Copy link
Owner

@only-cliches only-cliches commented Aug 7, 2018

Hello Caleb,

The issue is the where in your join command, try this instead:

nSQL('Tags').('select', [
        'Tags.tagId',
        'Tags.tagName',
        'Users.userId',
        'Users.email',
      ])
      .join({
        type: 'left',
        table: 'Users',
        where: ['Tags.tagId', '=', 'Users.tagId'], // first table is expected on the left side
      })
      .exec().then(rows => {
        console.log(rows);
        return rows;
      });

I'll be adding a check and error condition in the next version so folks don't stumble into this again, so thank you for the heads up!

@only-cliches
Copy link
Owner

@only-cliches only-cliches commented Aug 7, 2018

1.7.5 is now live on NPM and includes the join query sanity check. The query will throw an error if the where statement isn't formatted as expected.

@calebeaires
Copy link
Author

@calebeaires calebeaires commented Aug 7, 2018

Wonderfull!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants