Skip to content

Commit 775e938

Browse files
danrzeppadanrzeppa
committed
Extend query with value expressions to create allow creating select statements that can be joined together with expressions.
1 parent b0372e3 commit 775e938

4 files changed

Lines changed: 124 additions & 3 deletions

File tree

‎lib/dialect/postgres.js‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -644,7 +644,8 @@ Postgres.prototype.visitQuery = function(queryNode) {
644644
actions.push(new Select().add('*'));
645645
isSelect = true;
646646
}
647-
if(missingFrom) {
647+
if(missingFrom && queryNode.table instanceof Table) {
648+
// the instanceof handles the situation where a sql.select(some expression) is used and there should be no FROM clause
648649
targets.push(new From().add(queryNode.table));
649650
}
650651
if (createView) {

‎lib/node/index.js‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,11 @@ Node.prototype.toNode = function() {
1515

1616
Node.prototype.add = function(node) {
1717
assert(node, 'Error while trying to add a non-existant node to a query');
18-
this.nodes.push(typeof node === 'string' ? new TextNode(node) : node.toNode());
18+
var newNode
19+
if (typeof node === 'string') newNode = new TextNode(node)
20+
else if (node.toNode) newNode = node.toNode()
21+
else newNode = node
22+
this.nodes.push(newNode);
1923
return this;
2024
};
2125

‎lib/node/query.js‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,10 @@
11
'use strict';
22

3+
var _ = require('lodash');
34
var assert = require('assert');
45
var sliced = require('sliced');
56
var util = require('util');
7+
var valueExpressionMixin = require(__dirname + '/valueExpression');
68

79
var Node = require('./');
810
var Select = require('./select');
@@ -478,4 +480,16 @@ var Query = Node.define({
478480
}
479481
});
480482

483+
// Here we are extending query with valueExpressions so that it's possible to write queries like
484+
// var query=sql.select(a.select(a.x.sum()).plus(b.select(b.y.sum()))
485+
// which generates:
486+
// SELECT (SELECT SUM(a.x) FROM a) + (SELECT SUM(b.y) FROM b)
487+
// We need to remove "or" and "and" from here because it conflicts with the already existing functionality of appending
488+
// to the where clause like so:
489+
// var query=a.select().where(a.name.equals("joe")).or(a.name.equals("sam"))
490+
var valueExpressions=valueExpressionMixin();
491+
delete valueExpressions["or"];
492+
delete valueExpressions["and"];
493+
_.extend(Query.prototype, valueExpressions);
494+
481495
module.exports = Query;

‎test/dialects/select-tests.js‎

Lines changed: 103 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
var Harness = require('./support');
44
var post = Harness.definePostTable();
55
var customerAlias = Harness.defineCustomerAliasTable();
6+
var Sql = require('../../lib');
67

78
Harness.test({
89
query: post.select(post.id).select(post.content),
@@ -52,4 +53,105 @@ Harness.test({
5253
string: 'SELECT "customer"."id" "id_alias", "customer"."name" "name_alias", "customer"."age" "age_alias", "customer"."income" "income_alias", "customer"."metadata" "metadata_alias" FROM "customer"'
5354
},
5455
params: []
55-
});
56+
});
57+
58+
// Test that we can generate a SELECT claus without a FROM clause
59+
Harness.test({
60+
query: Sql.select(),
61+
pg: {
62+
text : 'SELECT ',
63+
string: 'SELECT '
64+
},
65+
sqlite: {
66+
text : 'SELECT ',
67+
string: 'SELECT '
68+
},
69+
mysql: {
70+
text : 'SELECT ',
71+
string: 'SELECT '
72+
},
73+
mssql: {
74+
text : 'SELECT ',
75+
string: 'SELECT '
76+
},
77+
oracle: {
78+
text : 'SELECT ',
79+
string: 'SELECT '
80+
},
81+
params: []
82+
});
83+
84+
Harness.test({
85+
query: Sql.select("1").where("1=1"),
86+
pg: {
87+
text : 'SELECT 1 WHERE (1=1)',
88+
string: 'SELECT 1 WHERE (1=1)'
89+
},
90+
sqlite: {
91+
text : 'SELECT 1 WHERE (1=1)',
92+
string: 'SELECT 1 WHERE (1=1)'
93+
},
94+
mysql: {
95+
text : 'SELECT 1 WHERE (1=1)',
96+
string: 'SELECT 1 WHERE (1=1)'
97+
},
98+
mssql: {
99+
text : 'SELECT 1 WHERE (1=1)',
100+
string: 'SELECT 1 WHERE (1=1)'
101+
},
102+
oracle: {
103+
text : 'SELECT 1 WHERE (1=1)',
104+
string: 'SELECT 1 WHERE (1=1)'
105+
},
106+
params: []
107+
});
108+
109+
Harness.test({
110+
query: Sql.select(post.select(post.id)),
111+
pg: {
112+
text : 'SELECT (SELECT "post"."id" FROM "post")',
113+
string: 'SELECT (SELECT "post"."id" FROM "post")'
114+
},
115+
sqlite: {
116+
text : 'SELECT (SELECT "post"."id" FROM "post")',
117+
string: 'SELECT (SELECT "post"."id" FROM "post")'
118+
},
119+
mysql: {
120+
text : 'SELECT (SELECT `post`.`id` FROM `post`)',
121+
string: 'SELECT (SELECT `post`.`id` FROM `post`)'
122+
},
123+
mssql: {
124+
text : 'SELECT (SELECT [post].[id] FROM [post])',
125+
string: 'SELECT (SELECT [post].[id] FROM [post])'
126+
},
127+
oracle: {
128+
text : 'SELECT (SELECT "post"."id" FROM "post")',
129+
string: 'SELECT (SELECT "post"."id" FROM "post")'
130+
},
131+
params: []
132+
});
133+
134+
Harness.test({
135+
query: Sql.select(post.select(post.content).plus(post.select(post.content))),
136+
pg: {
137+
text : 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))',
138+
string: 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))'
139+
},
140+
sqlite: {
141+
text : 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))',
142+
string: 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))'
143+
},
144+
mysql: {
145+
text : 'SELECT ((SELECT `post`.`content` FROM `post`) + (SELECT `post`.`content` FROM `post`))',
146+
string: 'SELECT ((SELECT `post`.`content` FROM `post`) + (SELECT `post`.`content` FROM `post`))'
147+
},
148+
mssql: {
149+
text : 'SELECT ((SELECT [post].[content] FROM [post]) + (SELECT [post].[content] FROM [post]))',
150+
string: 'SELECT ((SELECT [post].[content] FROM [post]) + (SELECT [post].[content] FROM [post]))'
151+
},
152+
oracle: {
153+
text : 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))',
154+
string: 'SELECT ((SELECT "post"."content" FROM "post") + (SELECT "post"."content" FROM "post"))'
155+
},
156+
params: []
157+
});

0 commit comments

Comments
 (0)