TypeORM version: 0.3.21

I've noticed that when specifying nested relations in TypeORM's findOptions where clause, it automatically generates the necessary JOINs. I'd like to confirm if this is an intended feature or an undocumented side effect.

I've searched through the documentation and issues but couldn't find any clear description of this specific behavior. I'm new to TypeORM, so I apologize if this is already documented somewhere that I missed. Any insights from someone familiar with TypeORM's internal workings would be greatly appreciated.

Code example:

const user = await manager.findOne(User, {
  where: {
    department: {
      company: {
        name: 'test',
      },
    },
  },
})

Generated SQL:

SELECT DISTINCT
  `distinctAlias`.`User_id` AS `ids_User_id`
FROM
  (
    SELECT
      /* only User table columns */
    FROM
      `user` `User`
      LEFT JOIN `department` `User__User_department` ON `User__User_department`.`department_id` = `User`.`department_id`
      AND (
        `User__User_department`.`deleted_at` IS NULL
      )
      LEFT JOIN `company` `c5f78c10915c4173cfd9951235c1b6b329194b87` ON `c5f78c10915c4173cfd9951235c1b6b329194b87`.`company_id` = `User__User_department`.`company_id`
      AND (
        `c5f78c10915c4173cfd9951235c1b6b329194b87`.`deleted_at` IS NULL
      )
    WHERE
      (
        (
          (
            (
              (
                (
                  (
                    `c5f78c10915c4173cfd9951235c1b6b329194b87`.`name` = 'test'
                  )
                )
              )
            )
          )
        )
      )
      AND (`User`.`deleted_at` IS NULL)
  ) `distinctAlias`
ORDER BY
  `User_id` ASC
LIMIT
  1

As shown above, even without explicitly specifying relations, JOINs are automatically generated just by using nested objects in the where clause.

Yes, the JOINs are intentional. You can find some examples in the Find Options documentation. Here's one relevant example:

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:

userRepository.find({
    relations: {
        project: true,
    },
    where: {
        project: {
            name: "TypeORM",
            initials: "TORM",
        },
    },
})

will execute following query:

SELECT * FROM "user"
LEFT JOIN "project" ON "project"."id" = "user"."projectId"
WHERE "project"."name" = 'TypeORM' AND "project"."initials" = 'TORM'

It's useful to think about the underlying database tables when it comes to relations -- I'm not a recent TypeORM user, but I knew the answer was "yes" just given the relations and was able to find the relevant documentation to confirm that.
For instance, in your case, user, department, and, company are likely three separate tables (as far as I can tell without seeing the schema as you didn't provide it). Based on the resulting query, you can tell that:

1. Your user table has a department_id column,
2. Your department table has a company_id column, and
3. Your company table has a name column

So to get a company name given a user, you'd need to traverse the user->department relation and the department->company relation.
To do so, you can either JOIN the tables together in one query or¹ you could use multiple, sequential queries (possibly in a transaction if atomicity is needed). TypeORM has the relationLoadStrategy option to choose between the two, defaulting to "join".

_{1. Which one to use is a different question you can search, but in brief, the former tends to be more conventional and which option is faster tends to be a big "it depends". Here's one existing SO question on the topic with 14 answers at time of writing: JOIN queries vs multiple queries}