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

Comments

Projects
None yet
2 participants
@calebeaires
Copy link

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
@ClickSimply

This comment has been minimized.

Copy link
Owner

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!

@ClickSimply

This comment has been minimized.

Copy link
Owner

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.

@ClickSimply ClickSimply closed this Aug 7, 2018

@calebeaires

This comment has been minimized.

Copy link
Author

commented Aug 7, 2018

Wonderfull!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.