How to Optimize Subquery in Objection JS?

Question

I want to optimise my subquery. From the mysql doc i found.

SELECT * 
FROM t1 
WHERE t1.column1 IN   ( SELECT column1 
                        FROM t2 
                        ORDER BY column1 );

SELECT * 
FROM t1 
WHERE t1.column1 IN  ( SELECT DISTINCT column1 
                       FROM t2
                      ); 
                      
SELECT * 
FROM t1 WHERE EXISTS   ( SELECT * 
                         FROM t2 LIMIT 1
                         );

I was able to achieve this format using this objection js code.

Person.query()
  .from(
    Person.query()
      .select(
        'persons.name as persons_name',
        'persons.disclaimer as persons_disclaimer',
        'persons.id as persons_id'
      )
      .as('optimised')
      .limit(40)
  )
  .select('optimised.*')
  .select((qb) => {
    qb.select(raw(`sum(act.count)`))
      .from('activity as act')
      .where('act.p_id', '=', 'optimised.persons_id')
      .as('actCountSum');
  })
  .select((qb) => {
    qb.select(raw(`count(*)`))
      .from('activity as act')
      .where('act.p_id', '=', 'optimised.persons_id')
      .as('actCount');
  })
  .debug();

But the problem is i am getting null and 0 respectively because on where clause its passing optimised.persons_id as a string.

Any solution?

Answer 1

The 3rd one looks simply wrong.

Try this:

SELECT  *
    FROM  t1
    WHERE  EXISTS (
        SELECT  1
            FROM  t2
            WHERE  t1.column1 = t2.column1 
                  );

If there aren't dups, then do simply

SELECT  t1.*
    FROM  t1
    JOIN  t2  ON t1.column1 = t2.column1