Drizzle Team•3w ago

How to use subqueries with query

  const jobsDb = await db.query.jobs_table.findMany({
    where: notExists(
      db
        .select()
        .from(matches_table)
        .where(
          and(
            eq(matches_table.user_id, userId),
            eq(matches_table.job_id, jobs_table.id),
          ),
        ),
    ),
    with: {
      working_days: true,
      location: true,
      location_image_file: true,
      required_languages: true,
      required_skills: true,
      matches: true,
    },
    // orderBy: sql`RAND()`,
  });

  const jobsDb = await db.query.jobs_table.findMany({
    where: notExists(
      db
        .select()
        .from(matches_table)
        .where(
          and(
            eq(matches_table.user_id, userId),
            eq(matches_table.job_id, jobs_table.id),
          ),
        ),
    ),
    with: {
      working_days: true,
      location: true,
      location_image_file: true,
      required_languages: true,
      required_skills: true,
      matches: true,
    },
    // orderBy: sql`RAND()`,
  });

Returns the error "invalid reference to FROM-clause entry for table jobs" Suppose it's because it's not joining matches. Saw this image but not sure if this means that subqueries are just not acceptable. Is there a way to make the implementation I want using queries? Can make this work with select but didn't want to make a massive function to parse everything in the correct schema. Quite litteraly the only thing left in my API, would really appreciate some help

6 Replies

DavidOP•2w ago

Mario564•2w ago

You haven't applied a join to jobs table on the notExists subquery

db
  .select()
  .from(matches_table)
  .leftJoin(jobs_table, eq(jobs_table.id, matches_table.job_id)) // you can change this to innerJoin if that's more appropiate for your use case
  .where(
    and(
      eq(matches_table.user_id, userId),
      eq(matches_table.job_id, jobs_table.id),
    ),
  ),

db
  .select()
  .from(matches_table)
  .leftJoin(jobs_table, eq(jobs_table.id, matches_table.job_id)) // you can change this to innerJoin if that's more appropiate for your use case
  .where(
    and(
      eq(matches_table.user_id, userId),
      eq(matches_table.job_id, jobs_table.id),
    ),
  ),

@David

DavidOP•2w ago

Wasn't aware that I had to add that leftJoin, because this was a rework of my previous select that had the join outside the subquery. though the join was on the query here but guess not. Wouldn't this be highly unperformant due to having to go through the jobs table again? Thanks nontheless

Mario564•2w ago

The performance is only a concern depending on the context. Is it a query that gets used very often? How many rows does the select have to read to get the desired output? If performance is a real concern, you should index the job ID on the matches table

DavidOP•2w ago

It's the main part of the project really. It can have hundreds of thousands of rows. The idea is to only show jobs that haven't been accepted by both the employee and the job seeker.

Mario564•2w ago

An index should suffice. If you have scaling issues, you could rewrite the query to use the more SQL-like syntax for better performance

Gaming

Programming

How to use subqueries with query