title: Typeorm dynamic query builder from structured object
body: <p>For use in a graphql server I have defined a structured input type where you can specify a number of filter conditions very similar to how prisma works:</p>
  <p><a href="https://i.stack.imgur.com/mRvM9.png" rel="noreferrer"><img src="https://i.stack.imgur.com/mRvM9.png" alt="enter image description here" /></a></p>
  <p>Which allows me to submit structured filters in a query like:</p>
  <pre class="lang-js prettyprint-override"><code>{
    users(
      where: {
        OR: [{ email: { starts_with: &quot;ja&quot; } }, { email: { ends_with: &quot;.com&quot; } }],
        AND: [{ email: { starts_with: &quot;ja&quot; } }, { email: { ends_with: &quot;.com&quot; } }],
        email: {contains: &quot;lowe&quot;}
      }
    ) {
      id
      email
    }
  }
  </code></pre>
  <p>Inside my resolver I feed the args.where through a function to parse the structure and utilize TypeOrm's query builder to convert it to proper sql.  The entirety of the function is:</p>
  <pre class="lang-js prettyprint-override"><code>import { Brackets } from &quot;typeorm&quot;;
  
  export const filterQuery = (query: any, where: any) =&gt; {
    if (!where) {
      return query;
    }
  
    Object.keys(where).forEach(key =&gt; {
      if (key === &quot;OR&quot;) {
        where[key].map((queryArray: any) =&gt; {
          query.orWhere(new Brackets(qb =&gt; filterQuery(qb, queryArray)));
        });
      } else if (key === &quot;AND&quot;) {
        where[key].map((queryArray: any) =&gt; {
          query.andWhere(new Brackets(qb =&gt; filterQuery(qb, queryArray)));
        });
      } else {
        const whereArgs = Object.entries(where);
  
        whereArgs.map(whereArg =&gt; {
          const [fieldName, filters] = whereArg;
          const ops = Object.entries(filters);
  
          ops.map(parameters =&gt; {
            const [operation, value] = parameters;
  
            switch (operation) {
              case &quot;is&quot;: {
                query.andWhere(`${fieldName} = :isvalue`, { isvalue: value });
                break;
              }
              case &quot;not&quot;: {
                query.andWhere(`${fieldName} != :notvalue`, { notvalue: value });
                break;
              }
              case &quot;in&quot;: {
                query.andWhere(`${fieldName} IN :invalue`, { invalue: value });
                break;
              }
              case &quot;not_in&quot;: {
                query.andWhere(`${fieldName} NOT IN :notinvalue`, {
                  notinvalue: value
                });
                break;
              }
              case &quot;lt&quot;: {
                query.andWhere(`${fieldName} &lt; :ltvalue`, { ltvalue: value });
                break;
              }
              case &quot;lte&quot;: {
                query.andWhere(`${fieldName} &lt;= :ltevalue`, { ltevalue: value });
                break;
              }
              case &quot;gt&quot;: {
                query.andWhere(`${fieldName} &gt; :gtvalue`, { gtvalue: value });
                break;
              }
              case &quot;gte&quot;: {
                query.andWhere(`${fieldName} &gt;= :gtevalue`, { gtevalue: value });
                break;
              }
              case &quot;contains&quot;: {
                query.andWhere(`${fieldName} ILIKE :convalue`, {
                  convalue: `%${value}%`
                });
                break;
              }
              case &quot;not_contains&quot;: {
                query.andWhere(`${fieldName} NOT ILIKE :notconvalue`, {
                  notconvalue: `%${value}%`
                });
                break;
              }
              case &quot;starts_with&quot;: {
                query
                  .andWhere(`${fieldName} ILIKE :swvalue`)
                  .setParameter(&quot;swvalue&quot;, `${value}%`);
                break;
              }
              case &quot;not_starts_with&quot;: {
                query
                  .andWhere(`${fieldName} NOT ILIKE :nswvalue`)
                  .setParameter(&quot;nswvalue&quot;, `${value}%`);
                break;
              }
              case &quot;ends_with&quot;: {
                query.andWhere(`${fieldName} ILIKE :ewvalue`, {
                  ewvalue: `%${value}`
                });
                break;
              }
              case &quot;not_ends_with&quot;: {
                query.andWhere(`${fieldName} ILIKE :newvalue`, {
                  newvalue: `%${value}`
                });
                break;
              }
              default: {
                break;
              }
            }
          });
        });
      }
    });
  
    return query;
  };
  </code></pre>
  <p>Which works (kinda) but does not nest the AND/OR queries like I would expect (and had previously got working in KNEX).  The above function generates the SQL:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
    &quot;user&quot;.&quot;id&quot; AS &quot;user_id&quot;,
    &quot;user&quot;.&quot;name&quot; AS &quot;user_name&quot;,
    &quot;user&quot;.&quot;email&quot; AS &quot;user_email&quot;,
    &quot;user&quot;.&quot;loginToken&quot; AS &quot;user_loginToken&quot;,
    &quot;user&quot;.&quot;loginTokenExpiry&quot; AS &quot;user_loginTokenExpiry&quot;,
    &quot;user&quot;.&quot;active&quot; AS &quot;user_active&quot;,
    &quot;user&quot;.&quot;visible&quot; AS &quot;user_visible&quot;,
    &quot;user&quot;.&quot;isStaff&quot; AS &quot;user_isStaff&quot;,
    &quot;user&quot;.&quot;isBilling&quot; AS &quot;user_isBilling&quot;,
    &quot;user&quot;.&quot;createdAt&quot; AS &quot;user_createdAt&quot;,
    &quot;user&quot;.&quot;updatedAt&quot; AS &quot;user_updatedAt&quot;,
    &quot;user&quot;.&quot;version&quot; AS &quot;user_version&quot;
  FROM &quot;user&quot; &quot;user&quot;
  WHERE (email ILIKE $1)
    AND (email ILIKE $2)
    OR (email ILIKE $3)
    OR (email ILIKE $4)
    AND email ILIKE $5
  -- PARAMETERS: [&quot;ja%&quot;,&quot;%.com&quot;,&quot;ja%&quot;,&quot;%.com&quot;,&quot;%lowe%&quot;]
  </code></pre>
  <p>But I would expect to see something more like:</p>
  <pre class="lang-js prettyprint-override"><code>..... 
  WHERE email ILIKE '%low%' 
  AND (
      email ILIKE 'ja%' AND email ILIKE '%.com'
  ) AND (
      email ILIKE 'ja%' OR email ILIKE '%.com'
  )
  </code></pre>
  <p>Forgive the nonsense, repetitive query. I'm just trying to illustrated the expected NESTED statements.</p>
  <p>How can I force the AND/OR branches of my query builder function to properly nest like expected?</p>
  <p>** Bonus points if someone can help me figure out the actual typescript typings here **</p>