Joining

Generally, joining two tables is straight forward. How two models are related is defined on the models using relationship decorators, OneToMany, OneToOne, or ManyToOne. Formn uses that metadata to join the tables together.

For example, back when we defined our models, we specified a one-to-many relationship between Person and PhoneNumber, and a many-to-one relationship in the opposite direction. With those relationships in place, we can pull all the Person records along with each Person’s PhoneNumbers. We do that with one of the FromAdapter’s join methods: leftOuterJoin or innerJoin. Here’s an example:

const query: Select<Person> = dataContext
  .from(Person, 'p')
  .leftOuterJoin(PhoneNumber, 'pn', 'p.phoneNumbers')
  .select();

That says to join from people to phone_numbers using the relationship defined on the p.phoneNumbers property. You’ll get back all Person records, and each Person will have an array of PhoneNumbers.

If you wanted to go the other direction, joining from phone_numbers to people:

const query: Select<PhoneNumber> = dataContext
  .from(PhoneNumber, 'pn')
  .innerJoin(Person, 'p', 'pn.person')
  .select();

which would give you all PhoneNumbers, each with a Person instance.

Full Example

Both examples presented above are available in the formn-example repository. Take a look at src/retrieve/join-people-to-phone-numbers.ts and src/retrieve/join-phone-numbers-to-people.ts. The former is presented below.

import { inspect } from 'util';

import { MySQLDataContext, ConnectionOptions, Select } from 'formn';

import { Person } from '../entity/person.entity';
import { PhoneNumber} from '../entity/phone-number.entity';

async function main() {
  const connOpts: ConnectionOptions = require('../../connections.json');
  const dataContext = new MySQLDataContext();

  try {
    await dataContext.connect(connOpts);

    // People with their phone numbers.
    const query: Select<Person> = dataContext
      .from(Person, 'p')
      .leftOuterJoin(PhoneNumber, 'pn', 'p.phoneNumbers')
      .select();

    console.log(query.toString());

    const people: Person[] = await query
      .execute();

    console.log(inspect(people, {depth: null, compact: false}));

    await dataContext.end();
  }
  catch(err) {
    console.error(err);
  }
}

main();

As usual, run it with ts-node.

npx ts-node ./src/retrieve/join-people-to-phone-numbers.ts

The output will look similar to the following.

SELECT  `p`.`createdOn` AS `p.createdOn`,
        `p`.`firstName` AS `p.firstName`,
        `p`.`lastName` AS `p.lastName`,
        `p`.`personID` AS `p.id`,
        `pn`.`personID` AS `pn.personId`,
        `pn`.`phoneNumber` AS `pn.phoneNumber`,
        `pn`.`phoneNumberID` AS `pn.id`,
        `pn`.`type` AS `pn.type`
FROM    `people` AS `p`
LEFT OUTER JOIN `phone_numbers` AS `pn` ON `p`.`personID` = `pn`.`personID`
[
  Person {
    id: 1,
    createdOn: 2019-02-13T03:37:23.000Z,
    firstName: 'Joe',
    lastName: 'Shmo',
    phoneNumbers: [
      PhoneNumber {
        id: 1,
        personId: 1,
        phoneNumber: '530-307-8810',
        type: 'mobile'
      },
      PhoneNumber {
        id: 2,
        personId: 1,
        phoneNumber: '916-200-1440',
        type: 'home'
      },
      PhoneNumber {
        id: 3,
        personId: 1,
        phoneNumber: '916-293-4667',
        type: 'office'
      }
    ]
  },
  Person {
    id: 2,
    createdOn: 2019-02-13T03:37:23.000Z,
    firstName: 'Rand',
    lastName: 'al\'Thor',
    phoneNumbers: [
      PhoneNumber {
        id: 4,
        personId: 2,
        phoneNumber: '666-451-4412',
        type: 'mobile'
      }
    ]
  },
  Person {
    id: 3,
    createdOn: 2019-02-13T03:37:23.000Z,
    firstName: 'Holly',
    lastName: 'Davis',
    phoneNumbers: []
  },
  Person {
    id: 4,
    createdOn: 2019-02-13T03:37:23.000Z,
    firstName: 'Jenny',
    lastName: 'Mather',
    phoneNumbers: []
  }
]