CODEWITHSUNDEEP
javascriptsqlpostgresqlobjection.js
Hobbamok
Hobbamok

Reputation: 838

Objection.js How to use outer values in inner query?

I'm struggling with a somewhat more complex SQL Query which HAS TO BE in Objection.js. Below is the code so far

const tagEntry = await Tag_overview.query()
    .where('playable') //the playable column is a boolean
    .whereExists(
        InnerTableName.query().findById([<normal variable>, tag.id]) //<= tag.id is the id of the row in the outer query
            )
    )
    .orderBy(raw('random()'))// this randomly orders the selection
    .limit(1)

"tag.id" should be the value of the row in the top/outer query thats currently being checked. In SQL I'd solve it with a simple line like (< normal variable> is a Javascript variable passed into the query and can be trated as a hardcoded value, it and tagid are a compound key)

and EXISTS (SELECT tagid, othercolumn FROM kunstmakler_preselection WHERE tag.id = tagid AND <normal variable>  = othercolumn)

But I have absolutely no clue how to do this in Objection.js. Yes, it needs an inner Query, but HOW do I pass this tag.id in there? I'm completely lost and neither the API Reference nor the Recipe Book is any help (found here: https://vincit.github.io/objection.js/recipes/ )

Is a join necessary here? Would it be worth it? [The tagoverview Table is rather small while "InnerTableName" is quite large]. I feel like that can't really be the solution since in ülain SQL it'd be such a smooth one liner

Upvotes: 1

Views: 769

Answers (1)

Mikael Lepist&#246;
Mikael Lepist&#246;

Reputation: 19718

First make sure that you have declared composite key correctly in model InnerTableName https://vincit.github.io/objection.js/recipes/composite-keys.html

Then you should be able to do:

.whereExists(
  InnerTableName.query().findById([theJsVariable, ref("Tag_overview.id")])
)

Upvotes: 1

Related Questions