title: How can I optimze my queries to select things using queryBuilder in Typeorm?
body: <p>I have 3 entities User, Spot and Thing.
  An User can have many spots and a spot can have many things.</p>
  
  <p>Currently I'm writing two queries, one to validate that the spot exists on the user and then one to get the things from the spot. (See <code>index.js</code>).</p>
  
  <p>How can I write one query using <code>createQueryBuilder</code> (not using the <code>repo.find</code> ) to select all things based on <code>user.id</code> and <code>spot.id</code>? I know there is some joining involved but I can't wrap my head around it.</p>
  
  <p>This is the relevant part of the code:</p>
  
  <pre class="lang-js prettyprint-override"><code>const spot = await spotRepo.createQueryBuilder('spot')
    .innerJoin('spot.user', 'user')
    .where('user.id = :id', { id: 1 })
    .andWhere('spot.id = :id', { id: 1 })
    .getOne();
  if (spot !== undefined) {
    console.log(spot);
    console.log('Got the spot');
    const spotWithThings = await spotRepo.createQueryBuilder('spot')
      .leftJoinAndSelect('spot.things', 'things')
      .where('spot.id = :id', { id: spot.id })
      .getOne();
    console.log(spotWithThings);
  }
  </code></pre>
  
  <hr>
  
  <p>Steps to run this project:</p>
  
  <ol>
  <li><code>git clone https://github.com/fabianmoronzirfas/typeorm-how-to-write-smarter-queries-questionmark.git ./test-repo &amp;&amp; cd test-repo</code></li>
  <li>Run <code>npm i</code> command</li>
  <li>Setup database settings inside <code>ormconfig.json</code> file</li>
  <li>start database <code>docker-compose up</code></li>
  <li>Run <code>npm start</code> command</li>
  </ol>
  
  <p>This is <code>index.ts</code></p>
  
  <pre class="lang-js prettyprint-override"><code>import "reflect-metadata";
  import { createConnection, getRepository } from "typeorm";
  import { User } from "./entity/User";
  import { Spot } from './entity/Spot';
  import { Thing } from './entity/Thing';
  
  createConnection().then(async connection =&gt; {
      {
          console.log("Inserting a new data into the database...");
          const user = new User();
          const spot = new Spot();
          // const things = [];
          for (let i = 0; i &lt; 5; i++) {
              const thing = new Thing();
              if (spot.things === undefined) {
                  spot.things = [thing];
              } else {
                  spot.things.push(thing);
              }
              await connection.manager.save(thing);;
          }
          user.spots = [spot];
          await connection.manager.save(user);
          await connection.manager.save(spot);
          console.log('setup done');
      }
      const spotRepo = getRepository(Spot);
      const spot = await spotRepo.createQueryBuilder('spot')
      .innerJoin('spot.user', 'user')
      .where('user.id = :id', { id: 1 })
      .andWhere('spot.id = :id', {id: 1})
      .getOne();
      if (spot !== undefined) {
          console.log(spot);
          console.log('Got the spot');
          const spotWithThings = await spotRepo.createQueryBuilder('spot')
          .leftJoinAndSelect('spot.things', 'things')
          .where('spot.id = :id', { id: spot.id })
          .getOne();
          console.log(spotWithThings);
      } else {
          console.log(`No spot? with user id ${1}`);
      }
  }).catch(error =&gt; console.log(error));
  </code></pre>
  
  <p>this is <code>Spot.ts</code>.</p>
  
  <pre class="lang-js prettyprint-override"><code>import {Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany} from "typeorm";
  import { User } from './User';
  import { Thing } from './Thing';
  @Entity()
  export class Spot {
      @PrimaryGeneratedColumn()
      id: number;
      @ManyToOne( _type =&gt; User, user =&gt; user.spots)
      public user: User;
      @OneToMany(_type =&gt; Thing, (thing) =&gt; thing.spot, {
          eager: true,
        })
        public things!: Thing[];
  }
  </code></pre>
  
  <p>This is Thing.ts</p>
  
  <pre class="lang-js prettyprint-override"><code>import {Entity, PrimaryGeneratedColumn, ManyToOne, JoinColumn} from "typeorm";
  import { Spot } from './Spot';
  
  @Entity()
  export class Thing {
  
      @PrimaryGeneratedColumn()
      id: number;
  
      @ManyToOne( _type =&gt; Spot, spot =&gt; spot.things , {
          cascade: true,
          // eager: true,
        })
        @JoinColumn()
        public spot!: Spot;
  }
  
  </code></pre>
  
  <p>This is <code>User.ts</code></p>
  
  <pre class="lang-js prettyprint-override"><code>import {Entity, PrimaryGeneratedColumn, OneToMany} from "typeorm";
  import { Spot } from './Spot';
  
  @Entity()
  export class User {
  
      @PrimaryGeneratedColumn()
      id: number;
      @OneToMany(_type =&gt; Spot, spot =&gt; spot.user, {
          cascade: true,
        })
        public spots: Spot[];
  }
  
  </code></pre>
  
  <p>this is ormconfig.json</p>
  
  <pre><code>{
     "type": "postgres",
     "host": "127.0.0.1",
     "port": 5432,
     "username": "postgres",
     "password": "postgres_password",
     "database": "postgres",
     "synchronize": true,
     "dropSchema": true,
     "logging": false,
     "entities": [
        "src/entity/**/*.ts"
     ],
     "migrations": [
        "src/migration/**/*.ts"
     ],
     "subscribers": [
        "src/subscriber/**/*.ts"
     ],
     "cli": {
        "entitiesDir": "src/entity",
        "migrationsDir": "src/migration",
        "subscribersDir": "src/subscriber"
     }
  }
  </code></pre>
  
  <p>This is my docker-compose.yml</p>
  
  <pre><code>version: '3'
  services:
    postgres:
      container_name: 'pgdb'
      image: 'mdillon/postgis:10'
      ports:
        - '5432:5432'
  </code></pre>
  
  <p>This is package.json</p>
  
  <pre><code>{
     "name": "typeorm-smarter-req",
     "version": "1.0.0",
     "description": "",
     "main": "index.js",
     "scripts": {
        "test": "echo \"Error: no test specified\" &amp;&amp; exit 1",
        "start": "ts-node src/index.ts"
     },
     "author": "",
     "license": "ISC",
     "dependencies": {
        "pg": "^7.11.0",
        "reflect-metadata": "^0.1.10",
        "typeorm": "0.2.18"
     },
     "devDependencies": {
        "ts-node": "3.3.0",
        "@types/node": "^8.0.29",
        "typescript": "3.3.3333"
     }
  }
  
  </code></pre>
  
  <p>this is tsconfig.json</p>
  
  <pre><code>{
     "compilerOptions": {
        "lib": [
           "es5",
           "es6",
           "dom"
  
        ],
        "target": "es5",
        "module": "commonjs",
        "moduleResolution": "node",
        "outDir": "./build",
        "emitDecoratorMetadata": true,
        "experimentalDecorators": true,
        "sourceMap": true
     }
  }
  </code></pre>