Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@nexes/mongo-knex

Package Overview
Dependencies
Maintainers
7
Versions
10
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@nexes/mongo-knex - npm Package Compare versions

Comparing version 0.1.2 to 0.2.0

test/integration/same-table.test.js
test/integration/suite1/fixtures/many-to-many.json
test/utils/assertions.js

219

lib/convertor.js

@@ -24,2 +24,4 @@ const _ = require('lodash');

const isCompOp = key => isOp(key) && _.includes(_.keys(compOps), key);
const isNegationOp = key => isOp(key) && _.includes(['$ne', '$nin'], key);
const isStatementGroupOp = key => _.includes([compOps.$in, compOps.$nin], key);

@@ -32,3 +34,3 @@ class MongoToKnex {

// @TODO: https://github.com/NexesJS/mongo-knex/issues/4
Object.assign(this.config, {relations: {}, aliases: {}}, config);
Object.assign(this.config, {relations: {}}, config);
}

@@ -52,18 +54,9 @@

processStatement(column, op, value) {
const columnParts = column.split('.');
const [tableName, columnName] = column.split('.');
// CASE: `posts.status` -> where "posts" is the parent table
if (columnParts[0] === this.tableName) {
return {
column: column,
value: value,
isRelation: false
};
}
// CASE: relation?
if (columnParts.length > 1) {
debug(columnParts);
if (columnName) {
debug(tableName, columnName);
const table = columnParts[0];
const table = tableName;
let relation = this.config.relations[table];

@@ -77,4 +70,9 @@

// CASE: assume it's a column on the destination table
if (!relation) {
throw new Error('Can\'t find relation in config object.');
return {
column: column,
value: value,
isRelation: false
};
}

@@ -85,3 +83,3 @@

table: relation.tableName,
column: columnParts[1],
column: columnName,
operator: op,

@@ -95,4 +93,4 @@ value: value,

return {
table: columnParts[0],
column: columnParts[1],
table: tableName,
column: columnName,
operator: op,

@@ -107,3 +105,3 @@ value: value,

return {
column: this.tableName + '.' + column,
column: `${this.tableName}.${column}`,
value: value,

@@ -115,51 +113,86 @@ isRelation: false

/**
* Build queries for relations.
* We group the relations by a unique key.
* Each grouping will create a sub query.
*
* @TODO: choose a different key name (!)
*
* Returns a group structure of following format:
* {
* "groupKey": {
* innerWhereStatements: [],
* joinFilterStatements: []
* }
* }
*/
buildRelationQuery(qb, relations) {
debug(`(buildRelationQuery)`);
groupRelationStatements(statements, mode) {
const group = {};
if (debugExtended.enabled) {
debugExtended(`(buildRelationQuery) ${JSON.stringify(relations)}`);
}
// groups depend on the mode of grouping, if its and $and we need to treat a filter on
// joining table differently than we would with $or
// e.g. for $or we can create a subquery or group that filter,
// for $and we have to include joining table filter in every group
const innerWhereStatements = (mode === '$and')
? statements.filter(r => !(r.join_table))
: statements;
const groupedRelations = {};
_.each(innerWhereStatements, (statement) => {
/**
* CASE:
* - we should not use the same sub query if the column name is the same (two sub queries)
* - e.g. $and conjunction requires us to use 2 sub queries, because we have to look at each individual tag
*
* - we should also not use grouping of negated values for the same reasons as above
*/
let createSubGroup = isNegationOp(statement.operator);
/**
* We group the relations by a unique key.
* Each grouping will create a sub query.
*
* @TODO: choose a different key name (!)
*/
_.each(relations, (relation) => {
if (!groupedRelations[relation.table]) {
groupedRelations[relation.table] = [];
}
if (groupedRelations[relation.table].length) {
const columnExists = _.find(groupedRelations[relation.table], (statement) => {
/**
* CASE:
* - we should not use the same sub query if the column name is the same (two sub queries)
* - e.g. $and conjunction requires us to use 2 sub queries, because we have to look at each individual tag
*/
if (statement.column === relation.column) {
if (!createSubGroup && group[statement.table]) {
createSubGroup = _.find(group[statement.table].innerWhereStatements, (innerStatement) => {
if (innerStatement.column === statement.column) {
return true;
}
});
}
if (columnExists) {
const newKey = `${relation.table}_${Math.floor(Math.random() * 100)}`;
if (!groupedRelations[newKey]) {
groupedRelations[newKey] = [];
}
let groupKey = statement.table;
groupedRelations[newKey].push(relation);
return;
}
if (createSubGroup) {
// TODO: while this is 'good enough' approach this needs to be approached with something
// more stable than random from 1..100
groupKey = `${statement.table}_${Math.floor(Math.random() * 100)}`;
}
groupedRelations[relation.table].push(relation);
if (!group[groupKey]) {
group[groupKey] = {};
group[groupKey].innerWhereStatements = [];
}
group[groupKey].innerWhereStatements.push(statement);
});
// NOTE: filters applied on join level have to be included when they are
// a part of $and group
if (mode === '$and') {
const joinFilterStatements = statements.filter(r => (r.join_table));
_.each(Object.keys(group), (key) => {
group[key].joinFilterStatements = joinFilterStatements;
});
}
return group;
}
/**
* Build queries for relations.
*/
buildRelationQuery(qb, relations, mode) {
debug(`(buildRelationQuery)`);
if (debugExtended.enabled) {
debugExtended(`(buildRelationQuery) ${JSON.stringify(relations)}`);
}
const groupedRelations = this.groupRelationStatements(relations, mode);
if (debugExtended.enabled) {
debugExtended(`(buildRelationQuery) grouped: ${JSON.stringify(groupedRelations)}`);

@@ -172,3 +205,3 @@ }

const statements = groupedRelations[key];
const statements = groupedRelations[key].innerWhereStatements;

@@ -179,21 +212,77 @@ // CASE: any statement for the same relation should contain the same config

if (reference.config.type === 'manyToMany') {
if (isCompOp(reference.operator)) {
const comp = reference.operator === '$ne' || reference.operator === '$nin' ? 'NOT IN' : 'IN';
if (_.every(statements.map(s => s.operator), isCompOp)) {
// CASE: only negate whole group when all the operators in the group are negative,
// otherwise we cannot combine groups with negated and regular equation operators
const negateGroup = _.every(statements.map(s => s.operator), (operator) => {
return isNegationOp(operator);
});
const comp = negateGroup
? compOps.$nin
: compOps.$in;
// CASE: WHERE resource.id (IN | NOT IN) (SELECT ...)
qb[reference.whereType](`${this.tableName}.id`, comp, function () {
const joinFilterStatements = groupedRelations[key].joinFilterStatements;
let innerJoinValue = reference.config.tableName;
let innerJoinOn = `${reference.config.tableName}.id`;
// CASE: you can define a name for the join table
if (reference.config.tableNameAs) {
innerJoinValue = `${reference.config.tableName} as ${reference.config.tableNameAs}`;
innerJoinOn = `${reference.config.tableNameAs}.id`;
}
const innerQB = this
.select(`${reference.config.join_table}.${reference.config.join_from}`)
.from(`${reference.config.join_table}`)
.innerJoin(`${reference.config.tableName}`, `${reference.config.tableName}.id`, '=', `${reference.config.join_table}.${reference.config.join_to}`);
.innerJoin(innerJoinValue, function () {
this.on(innerJoinOn, '=', `${reference.config.join_table}.${reference.config.join_to}`);
_.each(statements, (value, key) => {
// CASE: when applying AND con junction and having multiple groups the filter
// related to joining table has to be applied within each group
_.each(joinFilterStatements, (joinFilter) => {
this.andOn(`${joinFilter.join_table}.${joinFilter.column}`, compOps[joinFilter.operator], joinFilter.value);
});
});
if (debugExtended.enabled) {
debug(`(buildRelationQuery) innerQB sql-pre: ${innerQB.toSQL().sql}`);
}
_.each(statements, (statement, key) => {
debug(`(buildRelationQuery) build relation where statements for ${key}`);
innerQB[value.whereType](`${value.join_table || value.table}.${value.column}`, 'IN', !_.isArray(value.value) ? [value.value] : value.value);
const statementColumn = `${statement.join_table || statement.table}.${statement.column}`;
let statementOp;
if (negateGroup) {
statementOp = compOps.$in;
} else {
if (isNegationOp(statement.operator)) {
statementOp = compOps.$nin;
} else {
statementOp = compOps[statement.operator];
}
}
let statementValue = statement.value;
// CASE: need to normalize value to array when it's a group operation
if (isStatementGroupOp(statementOp)) {
statementValue = !_.isArray(statement.value) ? [statement.value] : statement.value;
}
innerQB[statement.whereType](statementColumn, statementOp, statementValue);
});
if (debugExtended.enabled) {
debug(`(buildRelationQuery) innerQB sql-post: ${innerQB.toSQL().sql}`);
}
return innerQB;
});
} else {
debug('unknown operator');
debug(`one of ${key} group statements contains unknown operator`);
}

@@ -221,6 +310,7 @@ }

if (processedStatement.isRelation) {
processedStatement.whereType = whereType;
// CASE: if the statement is not part of a group, execute the query instantly
if (!group) {
processedStatement.whereType = whereType;
this.buildRelationQuery(qb, [processedStatement]);
this.buildRelationQuery(qb, [processedStatement], mode);
return;

@@ -234,3 +324,2 @@ }

processedStatement.whereType = whereType;
qb.relations.push(processedStatement);

@@ -292,3 +381,3 @@ return;

if (_qb.hasOwnProperty('relations')) {
this.buildRelationQuery(_qb, _qb.relations);
this.buildRelationQuery(_qb, _qb.relations, mode);
delete _qb.relations;

@@ -295,0 +384,0 @@ }

2

package.json
{
"name": "@nexes/mongo-knex",
"version": "0.1.2",
"version": "0.2.0",
"description": "tbc",

@@ -5,0 +5,0 @@ "repository": "git@github.com:NexesJS/mongo-knex.git",

1011

test/integration/relations.test.js

@@ -9,20 +9,28 @@ const utils = require('../utils');

// @TODO: the config object is not designed yet.
const makeQuery = query => convertor(knex('posts'), query, {
relations: {
tags: {
tableName: 'tags',
type: 'manyToMany',
join_table: 'posts_tags',
join_from: 'post_id',
join_to: 'tag_id',
// @TODO: tag -> tags.slug
aliases: {}
},
authors: {
type: 'oneToMany',
join_from: 'author_id'
const makeQuery = (mongoJSON) => {
const query = convertor(knex('posts'), mongoJSON, {
relations: {
tags: {
tableName: 'tags',
type: 'manyToMany',
join_table: 'posts_tags',
join_from: 'post_id',
join_to: 'tag_id'
},
authors: {
tableName: 'users',
tableNameAs: 'authors',
type: 'manyToMany',
join_table: 'posts_authors',
join_from: 'post_id',
join_to: 'author_id'
}
}
}
});
});
query.orderBy('id', 'ASC');
return query;
};
// Integration tests build a test database and

@@ -35,59 +43,6 @@ // check that we get the exact data we expect from each query

describe.skip('One-to-Many', function () {
beforeEach(utils.db.init('relations1'));
afterEach(utils.db.reset());
it('can match array in (single value)', function (done) {
const queryJSON = {'authors.slug': {$in: ['sam']}};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
query.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(2);
// Check we get the right data
// result.should.do.something;
done();
})
.catch(done);
});
it('can match array in (multiple values)', function (done) {
const queryJSON = {'authors.name': {$in: ['Sam Smith', 'Pat Taylor']}};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
// Check any intermediate values
console.log('query', query.toQuery());
// Perform the query against the DB
query.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(5);
// Check we get the right data
// result.should.do.something;
done();
})
.catch(done);
});
});
describe('Many-to-Many', function () {
before(utils.db.init('relations1'));
before(utils.db.init('many-to-many'));
describe('EQUALS', function () {
describe('EQUALS $eq', function () {
it('tags.slug equals "animal"', function () {

@@ -104,7 +59,23 @@ const mongoJSON = {

result.should.be.an.Array().with.lengthOf(3);
result.should.matchIds([2, 4, 6]);
});
});
it('tags.visibility equals "internal"', function () {
const mongoJSON = {
'tags.visibility': 'internal'
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([8]);
});
});
});
describe('NEGATION', function () {
describe('NEGATION $ne', function () {
// should return posts without tags

@@ -114,3 +85,5 @@ // if a post has more than 1 tag, if one tag is animal, do not return

const mongoJSON = {
'tags.slug': {$ne: 'animal'}
'tags.slug': {
$ne: 'animal'
}
};

@@ -124,25 +97,12 @@

result.should.be.an.Array().with.lengthOf(5);
result.should.matchIds([1, 3, 5, 7, 8]);
});
});
});
describe('Multiple where clauses for relations', function () {
it('tags.slug equals "animal" and posts_tags.sort_order is 0 and featured is true', function () {
// where primary tag is "animal"
const mongoJSON = {$and: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
}
]
},
{
featured: true
it('tags.visibility is NOT "public"', function () {
const mongoJSON = {
'tags.visibility': {
$ne: 'public'
}
]};
};

@@ -154,23 +114,13 @@ const query = makeQuery(mongoJSON);

.then((result) => {
result.should.be.an.Array().with.lengthOf(0);
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([7, 8]);
});
});
});
it('tags.slug equals "animal" and posts_tags.sort_order is 0 and featured is false', function () {
// where primary tag is "animal"
const mongoJSON = {$and: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
}
]
},
{
featured: false
}
]};
describe('COMPARISONS $gt / $gte / $lt / $lte', function () {
it('tags.created_at is > 2015-06-21', function () {
const mongoJSON = {'tags.created_at': {
$gt: '2015-06-21'
}};

@@ -180,25 +130,12 @@ const query = makeQuery(mongoJSON);

return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(1);
result.length.should.eql(1);
result.should.matchIds([8]);
});
});
it('tags.slug equals "animal" and posts_tags.sort_order is 0 OR author_id is 1', function () {
const mongoJSON = {$or: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
},
{
featured: false
}
]
},
{author_id: 1}
]};
it('tags.created_at is >= 2015-06-21', function () {
const mongoJSON = {'tags.created_at': {
$gte: '2015-06-21'
}};

@@ -208,10 +145,38 @@ const query = makeQuery(mongoJSON);

return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(6);
result.length.should.eql(2);
result.should.matchIds([3, 8]);
});
});
it('tags.created_at is < 2015-01-02', function () {
const mongoJSON = {'tags.created_at': {
$lt: '2015-01-02'
}};
const query = makeQuery(mongoJSON);
return query
.then((result) => {
result.length.should.eql(4);
result.should.matchIds([1, 4, 5, 6]);
});
});
it('tags.created_at is <= 2015-01-02', function () {
const mongoJSON = {'tags.created_at': {
$lte: '2015-01-02'
}};
const query = makeQuery(mongoJSON);
return query
.then((result) => {
result.length.should.eql(5);
result.should.matchIds([1, 2, 4, 5, 6]);
});
});
});
describe('AND', function () {
describe('AND $and', function () {
it('tags.slug is animal and classic', function () {

@@ -235,8 +200,16 @@ const mongoJSON = {

result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([4, 6]);
});
});
it('tags.slug is animal and tags.slug not in []', function () {
it('tags.slug is hash-internal and tags.visibility is private', function () {
const mongoJSON = {
$and: [{'tags.slug': 'animal'},{'tags.slug': {$nin: ['classic']}}]
$and: [
{
'tags.slug': 'hash-internal'
},
{
'tags.visibility': 'internal'
}
]
};

@@ -250,20 +223,16 @@

result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([8]);
});
});
it('(tags.slug is animal and sort_order is 0) and tags.visibility=public', function () {
it('tags.slug is animal and tags.slug NOT in [classic]', function () {
const mongoJSON = {
$and: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
}
]
'tags.slug': 'animal'
},
{
'tags.visibility': 'public'
'tags.slug': {
$nin: ['classic']
}
}

@@ -279,2 +248,3 @@ ]

result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([2]);
});

@@ -291,5 +261,2 @@ });

'posts_tags.sort_order': 0
},
{
'tags.visibility': 'public'
}

@@ -305,26 +272,9 @@ ]

result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([2]);
});
});
});
describe('OR', function () {
it('tags.slug IN (animal)', function () {
it('(tags.slug is animal and sort_order is 0) and tags.visibility=public', function () {
const mongoJSON = {
'tags.slug': {
$in: ['animal']
}
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(3);
});
});
it('(tags.slug = animal and sort_order = 0) OR visibility:internal', function () {
const mongoJSON = {
$or: [
$and: [
{

@@ -341,3 +291,3 @@ $and: [

{
'tags.visibility': 'internal'
'tags.visibility': 'public'
}

@@ -352,9 +302,10 @@ ]

.then((result) => {
result.should.be.an.Array().with.lengthOf(2);
result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([2]);
});
});
it('tags.slug = animal OR sort_order = 0 OR visibility:internal', function () {
it('tags.slug is animal and sort_order is 0 and tags.visibility=public', function () {
const mongoJSON = {
$or: [
$and: [
{

@@ -367,3 +318,3 @@ 'tags.slug': 'animal'

{
'tags.visibility': 'internal'
'tags.visibility': 'public'
}

@@ -378,15 +329,19 @@ ]

.then((result) => {
result.should.be.an.Array().with.lengthOf(7);
result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([2]);
});
});
it('featured:true AND tags.slug IN (animal)', function () {
it('tags.slug is NOT animal and tags.slug is NOT cgi', function () {
// equivalent to $nin: ['animal', 'cgi']
const mongoJSON = {
$and: [
{
featured: true
'tags.slug': {
$ne: 'animal'
}
},
{
'tags.slug': {
$in: ['animal']
$ne: 'cgi'
}

@@ -402,10 +357,12 @@ }

.then((result) => {
result.should.be.an.Array().with.lengthOf(2);
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([1, 5, 7, 8]);
});
});
it('tags.id IN (2,3)', function () {
it('tags.slug NOT equal "classic" and tags.visibility is equal "public"', function () {
const mongoJSON = {
'tags.id': {
$in: [2, 3]
'tags.visibility': 'public',
'tags.slug': {
$ne: 'classic'
}

@@ -419,263 +376,441 @@ };

.then((result) => {
result.should.be.an.Array().with.lengthOf(4);
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([2, 3]);
});
});
});
});
// NOTE: no need to support 1:1 relations just yet
describe.skip('One-to-One: Extended Cases', function () {
beforeEach(() => utils.db.init('suite1', 'one-to-one-extended-cases'));
afterEach(() => utils.db.reset());
// TODO: should be filled with cases from 'Many-to-Many: Extended Cases' suite
});
describe.skip('One-to-Many: Extended Cases', function () {
beforeEach(() => utils.db.init('suite1', 'one-to-many-extended-cases'));
afterEach(() => utils.db.reset());
// TODO: should be filled with cases from 'Many-to-Many: Extended Cases' suite
});
describe.skip('Many-to-Many: Extended Cases', function () {
before(() => utils.db.init('suite1', 'many-to-many-extended-cases'));
after(() => utils.db.reset());
describe('negation $ne and $nin', function () {
it('can match $ne (single value)', function () {
const queryJSON = {
it('tags.slug NOT IN ["classic"] and tags.visibility is equal "public"', function () {
const mongoJSON = {
'tags.visibility': 'public',
'tags.slug': {
$ne: 'animal'
$nin: ['classic']
}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([2, 3]);
});
});
// NOTE: make sure to count in posts with no tags
// do not count in posts with multiple tags containing one of the excluded tags
result.should.be.an.Array().with.lengthOf(3);
it('(tags.slug NOT IN "classic" and tags.visibility is equal "public")', function () {
// this case can be generated with:
// 'tags.slug:-classic+tags.visibility:public'
const mongoJSON = {
$and: [
{
'tags.visibility': 'public'
},
{
'tags.slug': {
$nin: ['classic']
}
}
]
};
// Check we get the right data
// result.should.do.something;
const query = makeQuery(mongoJSON);
// NOTE: this query is generating a group, this should be avoided
// as we can't group negated properties with other, unless those
// are going through connecting table
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([2, 3]);
});
});
it('can match aliased $ne (single value)', function () {
// NOTE: makeQuery needs additional configuration to be passed in for aliases
const queryJSON = {
tag: {
$ne: 'animal'
}
it('any author is pat and any tag is classic (query on multiple relations)', function () {
const mongoJSON = {
$and: [
{
'authors.slug': 'pat'
},
{
'tags.slug': 'classic'
}
]
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
// NOTE: make sure to count in posts with no tags
// do not count in posts with multiple tags containing one of the excluded tags
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([1, 4, 5, 6]);
});
});
it('can match array $nin (single value)', function () {
const queryJSON = {
'tags.slug': {
$nin: ['animal']
}
it('first author is pat and first tag is classic (query on multiple relations)', function () {
const mongoJSON = {
$and: [
{
$and: [
{
'tags.slug': 'classic'
},
{
'posts_tags.sort_order': 0
}
]
},
{
$and: [
{
'authors.slug': 'pat'
},
{
'posts_authors.sort_order': 0
}
]
}
]
};
const query = makeQuery(mongoJSON);
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
// NOTE: make sure to count in posts with no tags
// do not count in posts with multiple tags containing one of the excluded tags
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.matchIds([1, 5, 6]);
});
});
it('can match array $nin (multiple values)', function () {
const queryJSON = {
'tags.slug': {
$nin: ['animal', 'classic']
}
};
describe('Multiple conditions applied to the joining table and to the destination table', function () {
it('tags.slug equals "cgi" and posts_tags.sort_order is 0 and featured is true', function () {
// where primary tag is "cgi"
const mongoJSON = {
$and: [
{
$and: [
{
'tags.slug': 'cgi'
},
{
'posts_tags.sort_order': 0
}
]
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
},
{
featured: true
}
]
};
// Check any intermediate values
console.log('query', query.toQuery());
const query = makeQuery(mongoJSON);
// Perform the query against the DB
return query.select()
.then((result) => {
console.log(result);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([3]);
});
});
// NOTE: make sure to count in posts with no tags
// do not count in posts with multiple tags containing one of the excluded tags
result.should.be.an.Array().with.lengthOf(1);
it('tags.slug equals "animal" and posts_tags.sort_order is 0 and featured is false', function () {
// where primary tag is "animal"
const mongoJSON = {
$and: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
}
]
},
{
featured: false
}
]
};
// Check we get the right data
// result.should.do.something;
});
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(1);
result.should.matchIds([2]);
});
});
it('tags.slug NOT equal "classic" and posts_tags.sort_order is 0 and featured is true', function () {
const mongoJSON = {
$and: [
{
$and: [
{
'tags.slug': {
$ne: 'classic'
}
},
{
'posts_tags.sort_order': 0
}
]
},
{
featured: true
}
]
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
// @NOTE: This should return posts without tags, because a post without tags is not tagged
// with the primary tag "classic".
result.should.be.an.Array().with.lengthOf(3);
result.should.matchIds([3, 7, 8]);
});
});
});
});
describe('count', function () {
it('can compare by count $gt', function () {
const queryJSON = {
'authors.count': {$gt: 0}
describe('OR $or', function () {
it('any author is pat or leslie', function () {
const mongoJSON = {
$or: [
{
'authors.slug': 'leslie'
},
{
'authors.slug': 'pat'
}
]
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(7);
result.should.matchIds([1, 3, 4, 5, 6, 7, 8]);
});
});
result.should.be.an.Array().with.lengthOf(3);
it('any author is sam or any tag is cgi', function () {
const mongoJSON = {
$or: [
{
'authors.slug': 'sam'
},
{
'tags.slug': 'cgi'
}
]
};
// Check we get the right data
// result.should.do.something;
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([2, 3, 4, 8]);
});
});
it('can compare by count $lt', function () {
const queryJSON = {
'authors.count': {$lt: 2}
it('any author is not pat or any tag is in [animal]', function () {
const mongoJSON = {
$or: [
{
'authors.slug': {
$ne: 'pat'
}
},
{
'tags.slug': {
$in: ['animal']
}
}
]
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([2, 4, 6, 8]);
});
});
result.should.be.an.Array().with.lengthOf(3);
describe('Multiple conditions applied to the joining table and to the destination table', function () {
it('tags.slug equals "animal" and posts_tags.sort_order is 0 OR author_id is 1', function () {
const mongoJSON = {
$or: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
},
{
featured: false
}
]
},
{
author_id: 1
}
]
};
// Check we get the right data
// result.should.do.something;
});
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(6);
result.should.matchIds([1, 2, 3, 5, 6, 7]);
});
});
it('(tags.slug = animal and sort_order = 0) OR visibility:internal', function () {
const mongoJSON = {
$or: [
{
$and: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
}
]
},
{
'tags.visibility': 'internal'
}
]
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([2, 8]);
});
});
it('tags.slug = animal OR sort_order = 0 OR visibility:internal', function () {
const mongoJSON = {
$or: [
{
'tags.slug': 'animal'
},
{
'posts_tags.sort_order': 0
},
{
'tags.visibility': 'internal'
}
]
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(7);
result.should.matchIds([1, 2, 3, 4, 5, 6, 8]);
});
});
});
});
describe('conjunction $and', function () {
it('can match multiple values of same attribute', function () {
const queryJSON = {
$and: [
{'author.slug': 'pat'},
{'authors.slug': 'sam'}
]
describe('IN $in', function () {
it('tags.slug IN (animal)', function () {
const mongoJSON = {
'tags.slug': {
$in: ['animal']
}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.matchIds([2, 4, 6]);
});
});
it('can match multiple values of different attributes', function () {
const queryJSON = {
$and: [
{'authors.slug': 'pat'},
{'tags.slug': 'classic'}
]
it('tags.slug IN (animal, cgi)', function () {
const mongoJSON = {
'tags.slug': {
$in: ['animal', 'cgi']
}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([2, 3, 4, 6]);
});
});
result.should.be.an.Array().with.lengthOf(3);
it('tags.id IN (2,3)', function () {
const mongoJSON = {
'tags.id': {
$in: [2, 3]
}
};
// Check we get the right data
// result.should.do.something;
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([2, 3, 4, 6]);
});
});
it('can match multiple values of same aliased attribute', function () {
// NOTE: makeQuery needs additional configuration to be passed in for aliases
const queryJSON = {
it('tags.slug IN (animal) AND featured:true', function () {
const mongoJSON = {
$and: [
{author: 'pat'},
{author: 'sam'}
{
'tags.slug': {
$in: ['animal']
}
},
{
featured: true
}
]
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.be.an.Array().with.lengthOf(2);
result.should.matchIds([4, 6]);
});

@@ -685,79 +820,75 @@ });

describe('conjunction $or', function () {
it('can match values of same attributes', function () {
const queryJSON = {
$or: [
{'authors.slug': 'joe'},
{'authors.slug': 'pat'}
]
describe('NOT IN $nin', function () {
it('tags.slug NOT IN (animal)', function () {
const mongoJSON = {
'tags.slug': {
$nin: ['animal']
}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(5);
result.should.matchIds([1, 3, 5, 7, 8]);
});
});
result.should.be.an.Array().with.lengthOf(3);
it('tags.slug NOT IN (animal, cgi)', function () {
const mongoJSON = {
'tags.slug': {
$nin: ['animal', 'cgi']
}
};
// Check we get the right data
// result.should.do.something;
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([1, 5, 7, 8]);
});
});
it('can match values of different attributes', function () {
const queryJSON = {
$or: [
{'authors.slug': 'joe'},
{'tags.slug': 'photo'}
]
it('tags.id NOT IN (2,3)', function () {
const mongoJSON = {
'tags.id': {
$nin: [2, 3]
}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.be.an.Array().with.lengthOf(4);
result.should.matchIds([1, 5, 7, 8]);
});
});
it('can match values of same aliased attributes', function () {
// NOTE: makeQuery needs additional configuration to be passed in for aliases
const queryJSON = {
$or: [
{author: 'joe'},
{author: 'pat'}
it('tags.slug NOT IN (classic, animal) AND featured:true', function () {
const mongoJSON = {
$and: [
{
'tags.slug': {
$nin: ['classic', 'animal']
}
},
{
featured: true
}
]
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
result.should.matchIds([3, 7, 8]);
});

@@ -767,29 +898,28 @@ });

describe('combination of extended cases', function () {
it('should be filled with a mix of all the above cases', function () {
describe('COUNT', function () {
it.skip('can compare by count $gt', function () {
const mongoJSON = {
'authors.count': {$gt: 0}
};
const query = makeQuery(mongoJSON);
return query
.select()
.then((result) => {
result.should.be.an.Array().with.lengthOf(3);
});
});
it('can match values of different attributes combining with negation', function () {
const queryJSON = {
$or: [
{'authors.slug': {$ne: 'joe'}},
{'tags.slug': {$in: ['photo']}}
]
it.skip('can compare by count $lt', function () {
const mongoJSON = {
'authors.count': {$lt: 2}
};
// Use the queryJSON to build a query
const query = makeQuery(queryJSON);
const query = makeQuery(mongoJSON);
// Check any intermediate values
console.log(query.toQuery());
// Perform the query against the DB
return query.select()
return query
.select()
.then((result) => {
console.log(result);
result.should.be.an.Array().with.lengthOf(3);
// Check we get the right data
// result.should.do.something;
});

@@ -799,2 +929,5 @@ });

});
describe('[NOT IMPLEMENTED] One-to-One', function () {});
describe('[NOT IMPLEMENTED] One-to-Many', function () {});
});

20

test/integration/suite1/fixtures/base.json

@@ -26,2 +26,3 @@ {

"status": "published",
"published_at": "2015-01-01",
"author_id": 1

@@ -35,2 +36,3 @@ },

"status": "draft",
"published_at": null,
"author_id": 2

@@ -44,2 +46,3 @@ },

"status": "published",
"published_at": "2015-01-03",
"author_id": 1

@@ -53,2 +56,3 @@ },

"status": "published",
"published_at": "2015-06-01",
"author_id": 2

@@ -62,2 +66,3 @@ },

"status": "published",
"published_at": "2015-06-02",
"author_id": 1

@@ -71,2 +76,3 @@ },

"status": "published",
"published_at": "2015-06-03",
"author_id": 1

@@ -80,2 +86,3 @@ },

"status": "published",
"published_at": "2015-06-04",
"author_id": 1

@@ -89,2 +96,3 @@ },

"status": "published",
"published_at": "2015-06-05",
"author_id": 2

@@ -98,3 +106,4 @@ }

"slug": "classic",
"visibility": "public"
"visibility": "public",
"created_at": "2015-01-01"
},

@@ -105,3 +114,4 @@ {

"slug": "animal",
"visibility": "public"
"visibility": "public",
"created_at": "2015-01-02"
},

@@ -112,3 +122,4 @@ {

"slug": "cgi",
"visibility": "public"
"visibility": "public",
"created_at": "2015-06-21"
},

@@ -119,5 +130,6 @@ {

"slug": "hash-internal",
"visibility": "internal"
"visibility": "internal",
"created_at": "2015-06-22"
}
]
}

10

test/integration/suite1/schema.js
const Promise = require('bluebird');
const TABLES = ['posts_tags', 'posts', 'tags', 'users'];
const TABLES = ['posts_tags', 'posts_authors', 'posts', 'tags', 'users'];

@@ -18,2 +18,3 @@ module.exports.up = function (knex) {

table.string('status', 191).nullable();
table.dateTime('published_at').nullable();
table.integer('author_id').unsigned().references('users.id');

@@ -26,2 +27,3 @@ }))

table.string('visibility', 191).defaultTo('public');
table.dateTime('created_at');
}))

@@ -33,2 +35,8 @@ .then(() => knex.schema.createTable('posts_tags', (table) => {

table.integer('sort_order').defaultTo(0);
}))
.then(() => knex.schema.createTable('posts_authors', (table) => {
table.increments('id').primary();
table.integer('post_id').unsigned().references('posts.id');
table.integer('author_id').unsigned().references('users.id');
table.integer('sort_order').defaultTo(0);
}));

@@ -35,0 +43,0 @@ };

10

test/unit/convertor.test.js

@@ -17,2 +17,12 @@ require('../utils');

});
it('should accept any table input and interprets it as destination where clause', function () {
runQuery({'posts.title': 'Second post'})
.should.eql('select * from `posts` where `posts`.`title` = \'Second post\'');
});
it('should accept any table input and interprets it as destination where clause', function () {
runQuery({'count.posts': '3'})
.should.eql('select * from `posts` where `count`.`posts` = \'3\'');
});
});

@@ -19,0 +29,0 @@

3

test/utils/index.js

@@ -15,2 +15,5 @@ /**

// "should" overrides
require('./assertions');
module.exports.db = require('./db');
.vscode/launch.json
test/integration/suite1/fixtures/many-to-many-extended-cases.json
test/integration/suite1/fixtures/many-to-many-simple-cases.json
test/integration/suite1/fixtures/one-to-many-extended-cases.json
test/integration/suite1/fixtures/one-to-one-extended-cases.json
test/integration/suite1/fixtures/relations1.json
test/integration/where.test.js
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc