title: TypeORM complex Join conditions
body: <p>We are currently moving all database operations in a larger project from sequelize to typeorm. In one case we have to perform a large select in which we have to join multiple entities and perform filter conditions on them.</p>
  <p>To exemplify let's assume we have a list of users where each can have 0 or more Cars, CoffeeCups and Chairs. Each of these things having individual database tables with more detailed informations and ManyToOne relations to the User table.
  Now we want to get the users and perform filter operations on them including on their related data, like getting all users with blue coffee cups which are at least size medium and also drive a yellow car while also owning a chair costing at least 20$.</p>
  <p>Using sequelize we would perform a findAll with lots of includes that further contain where objects similar to this pseudocode, though in the actual code these would be build dynamically:</p>
  <pre><code>this.users.findAll({
      include: [
        {
          model: CoffeeCup,
          where: {
            color: 'blue',
            size: {
              [Op.gt]: Size.MEDIUM
            }
          }
        },
        {
          model: Car,
          where: {
            color: 'yellow',
          }
        },
        ...
      ]
    })
  </code></pre>
  <p>The resulting SQL would select the Users table and use inner joins to add the related models. The where filters would be part of the ON condition after the foreign key.</p>
  <p>Now to get a similar result with typeorm we could do something like this:</p>
  <pre><code>user.createQueryBuilder('user')
    .leftJoinAndSelect('user.coffeeCups', 'coffeeCups', 'coffeeCups.color = :color AND coffeeCups.size &gt; :size', {color: 'blue', size: Size.MEDIUM})
    .leftJoinAndSelect('user.cars', 'cars', 'cars.color = :color', {color: 'yellow'}
    ...
  </code></pre>
  <p>The problem is though that those filter conditions only get added when they are actually passed by the filter and the actual number of query conditions is far higher and the queries themselves more complex.</p>
  <p>Creating a query string &quot;manually&quot; using basic string operation would likely be error prone and make the code convoluted.</p>
  <p>Another option would be to use the subquery builder in the leftJoinAndSelect. Here though the issue is that typeorm doesn't automatically map the values to the Entity property this way and you have to use getRaw... methods. This way every column-&gt;property mapping would have to be custom which is a huge strain in such a big and ever-changing project.</p>
  <p>Lastly a better looking but far worse performing solution that we tried was performing all filter condition on the main querybuilder and joining all related tables as is.
  This means all filter conditions are only present in the main WHERE and thus performed after all non-filtered data is joined. This makes the query so slow that the statement will actually timeout even on small datasets.</p>
  <p>Is there a better solution that we're missing?</p>
  <p>Is there maybe a builder/factory to create a condition similar to the querybuilders? This way we could use the familar .andWhere, .orWhere, ... methods to add filter queries conditionally and at the end maybe just get the condition string and variables separately to use in the lefJoinAndSelect method. Something maybe like this:</p>
  <pre><code>const carConditionBuilder = new ConditionBuilder()
  
  if (hasColorFilter) {
    carConditionBuilder.andWhere('cars.color = :color', {color: 'blue'}
  }
  ...
  qb.innerJoinAndSelect('user.cars', 'cars', carConditionBuilder.getSQL(), carConditionBuilder.getVariables())
  ...
  </code></pre>