Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb190930

Browse files
committed
Support querying against hasMany relations using WHERE EXISTS.
1 parent89ede85 commitb190930

File tree

2 files changed

+79
-52
lines changed

2 files changed

+79
-52
lines changed

‎src/index.js‎

Lines changed: 59 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -18,50 +18,6 @@ function getTable (resourceConfig) {
1818
returnresourceConfig.table||underscore(resourceConfig.name)
1919
}
2020

21-
/**
22-
* Lookup and apply table joins to query if field contains a `.`
23-
*@param {string} field - Field defined in where filter
24-
*@param {object} query - knex query to modify
25-
*@param {object} resourceConfig - Resource of primary query/table
26-
*@param {string[]} existingJoins - Array of fully qualitifed field names for
27-
* any existing table joins for query
28-
*@returns {string} - field updated to perspective of applied joins
29-
*/
30-
functionapplyTableJoins(field,query,resourceConfig,existingJoins){
31-
if(DSUtils.contains(field,'.')){
32-
letparts=field.split('.')
33-
letlocalResourceConfig=resourceConfig
34-
35-
letrelationPath=[]
36-
while(parts.length>=2){
37-
letrelationName=parts.shift()
38-
letrelationResourceConfig=resourceConfig.getResource(relationName)
39-
relationPath.push(relationName)
40-
41-
if(!existingJoins.some(t=>t===relationPath.join('.'))){
42-
let[relation]=localResourceConfig.relationList.filter(r=>r.relation===relationName)
43-
if(relation){
44-
lettable=getTable(localResourceConfig)
45-
letlocalId=`${table}.${relation.localKey}`
46-
47-
letrelationTable=getTable(relationResourceConfig)
48-
letforeignId=`${relationTable}.${relationResourceConfig.idAttribute}`
49-
50-
query.join(relationTable,localId,foreignId)
51-
existingJoins.push(relationPath.join('.'))
52-
}else{
53-
// hopefully a qualified local column
54-
}
55-
}
56-
localResourceConfig=relationResourceConfig
57-
}
58-
59-
field=`${getTable(localResourceConfig)}.${parts[0]}`
60-
}
61-
62-
returnfield
63-
}
64-
6521
functionloadWithRelations(items,resourceConfig,options){
6622
lettasks=[]
6723
letinstance=Array.isArray(items) ?null :items
@@ -291,6 +247,7 @@ class DSSqlAdapter {
291247

292248
filterQuery(resourceConfig,params,options){
293249
lettable=getTable(resourceConfig)
250+
letjoinedTables=[]
294251
letquery
295252

296253
if(paramsinstanceofObject.getPrototypeOf(this.query.client).QueryBuilder){
@@ -308,8 +265,6 @@ class DSSqlAdapter {
308265
params.orderBy=params.orderBy||params.sort
309266
params.skip=params.skip||params.offset
310267

311-
letjoinedTables=[]
312-
313268
DSUtils.forEach(DSUtils.keys(params),k=>{
314269
letv=params[k]
315270
if(!DSUtils.contains(reserved,k)){
@@ -331,16 +286,68 @@ class DSSqlAdapter {
331286
'==':criteria
332287
}
333288
}
334-
335-
DSUtils.forOwn(criteria,(v,op)=>{
336-
// Apply table joins (if needed)
289+
290+
letprocessRelationField=(field)=>{
291+
letparts=field.split('.')
292+
letlocalResourceConfig=resourceConfig
293+
letrelationPath=[]
294+
295+
while(parts.length>=2){
296+
letrelationName=parts.shift()
297+
letrelationResourceConfig=resourceConfig.getResource(relationName)
298+
relationPath.push(relationName)
299+
300+
if(localResourceConfig.relationList){
301+
let[relation]=localResourceConfig.relationList.filter(r=>r.relation===relationName)
302+
if(relation){
303+
if(relation.type==='belongsTo'||relation.type==='hasOne'){
304+
// Apply table join for belongsTo/hasOne property (if not done already)
305+
if(!joinedTables.some(t=>t===relationPath.join('.'))){
306+
lettable=getTable(localResourceConfig)
307+
letlocalId=`${table}.${relation.localKey}`
308+
309+
letrelationTable=getTable(relationResourceConfig)
310+
letforeignId=`${relationTable}.${relationResourceConfig.idAttribute}`
311+
312+
query.join(relationTable,localId,foreignId)
313+
joinedTables.push(relationPath.join('.'))
314+
}
315+
}elseif(relation.type==='hasMany'){
316+
// Perform `WHERE EXISTS` subquery for hasMany property
317+
lettable=getTable(localResourceConfig)
318+
letlocalId=`${table}.${localResourceConfig.idAttribute}`
319+
320+
letrelationTable=getTable(relationResourceConfig)
321+
letforeignId=`${relationTable}.${relation.foreignKey}`
322+
323+
letexistsParams={
324+
[foreignId]:{'===':knex.raw(localId)},
325+
[parts[0]]:criteria
326+
};
327+
query.whereExists(this.filterQuery(relationResourceConfig,existsParams,options));
328+
criteria=null;// criteria handled by EXISTS subquery
329+
}
330+
}else{
331+
// hopefully a qualified local column
332+
}
333+
334+
localResourceConfig=relationResourceConfig
335+
}
336+
}
337+
338+
return`${getTable(localResourceConfig)}.${parts[0]}`
339+
}
340+
341+
if(DSUtils.contains(field,'.')){
337342
if(DSUtils.contains(field,',')){
338343
letsplitFields=field.split(',').map(c=>c.trim())
339-
field=splitFields.map(splitField=>applyTableJoins(splitField,query,resourceConfig,joinedTables)).join(',')
344+
field=splitFields.map(splitField=>processRelationField(splitField)).join(',')
340345
}else{
341-
field=applyTableJoins(field,query,resourceConfig,joinedTables)
346+
field=processRelationField(field,query,resourceConfig,joinedTables)
342347
}
343-
348+
}
349+
350+
DSUtils.forOwn(criteria,(v,op)=>{
344351
if(op==='=='||op==='==='){
345352
if(v===null){
346353
query=query.whereNull(field)

‎test/findAll.spec.js‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,26 @@ describe('DSSqlAdapter#findAll', function () {
4545
assert.equal(posts[1].content,'bar');
4646
assert.equal(posts[2].content,'baz');
4747
});
48+
49+
it('should filter using a hasMany relation',function*(){
50+
letuser1=yieldadapter.create(User,{name:'Sean'});
51+
letpost1=yieldadapter.create(Post,{userId:user1.id,content:'foo'});
52+
letpost2=yieldadapter.create(Post,{userId:user1.id,content:'bar'});
53+
letpost3=yieldadapter.create(Post,{userId:user1.id,content:'baz'});
54+
55+
letuser2=yieldadapter.create(User,{name:'Jason'});
56+
letpost4=yieldadapter.create(Post,{userId:user2.id,content:'foo'});
57+
letpost5=yieldadapter.create(Post,{userId:user2.id,content:'bar'});
58+
59+
letuser3=yieldadapter.create(User,{name:'Ed'});
60+
letpost6=yieldadapter.create(Post,{userId:user3.id,content:'bar'});
61+
letpost7=yieldadapter.create(Post,{userId:user3.id,content:'baz'});
62+
63+
letusers=yieldadapter.findAll(User,{where:{'post.content':{'==':'foo'}}});
64+
assert.equal(users.length,2);
65+
assert.equal(users[0].name,'Sean');
66+
assert.equal(users[1].name,'Jason');
67+
});
4868

4969
describe('near',function(){
5070
beforeEach(function*(){

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp