Subquery on model not working

Hello,
I am trying to do subquery like this with two Lucid models

const problemsForUser = ProblemPermission.query()
      .where('userId', userId)
      .select('problemId')
    const problems = await Problem.query()
      .whereIn('problemId', problemsForUser)
      .orderBy('problemId')

I am getting error
this[method] is not a function

But this one works:

const problemsForUser = Database.from(ProblemPermission.table)
      .where('userId', userId)
      .select('problemId')
    const problems = await Problem.query()
      .whereIn('problemId', problemsForUser)
      .orderBy('problemId')

Am I doing anything wrong on the first code?

1 Like

Is ProblemPermission a Lucid model or a database table ?

Problem and ProblemPermission both are Lucid model.

If it is a Lucid model then try to call the fetch() method to end the Query Builder chain.

As you can read from the documentation:

The fetch method is required to to execute the query ensuring results return within a serializer instance

Then it will execute two sql queries, where I want to run one sql query as a subquery. just to make you understand, I want a query like this below

select * from table1 T1 where T1.problemId in (select problemId from table2 T2)

A join is probably the better choice than a subquery

const problems = await Problem.query()
    .join('problem_permissions as pp', 'pp.problemId', 'problems.problemId')
    .where('pp.userId', userId)
    .orderBy('problems.problemId', 'asc')
    .select('problems.*')
    .fetch()
1 Like