Question: How to make query with relation between pets and users about express-typescript-boilerplate

[foxthefox]

After many hours I could find a way to get a relational query running.
I am not sure if this is approprioate way, so please indicate if I am doing something against the idea!

First I figured out that in Bookshelf always a hasMany is declared for the opposite direction of belongsTo.
This always gave errors within User.ts until I created a collection of Pets out of Pet.

What did I do in particular:

1 new resource Owner

creating a new resource owner should give me the assignemnt of user to the pet

npm run console make:resource Owner
without migration, but with IoC

2 OwnerController.ts

delete everything what is CRUD, hence we are only querying

`
import { inject, named } from 'inversify';
import { controller, httpGet, response, requestParam } from 'inversify-express-utils';
import { Types, Targets } from '../../constants';
import { app } from '../../app';
import { OwnerService } from '../services/OwnerService';

// Get middlewares
const authenticate = app.IoC.getNamed<interfaces.Middleware>(Types.Middleware, Targets.Middleware.AuthenticateMiddleware);

@controller('/owners', authenticate.use)
export class OwnerController {

constructor( @inject(Types.Service) @named(Targets.Service.OwnerService) private ownerService: OwnerService) { }

@httpGet('/')
public async findAll( @response() res: myExpress.Response): Promise<any> {
    const owners = await this.ownerService.findAll();
    return res.found(owners.toJSON());
}

@httpGet('/:id')
public async findOne( @response() res: myExpress.Response, @requestParam('id') id: string): Promise<any> {
    const owner = await this.ownerService.findOne(parseInt(id, 10));
    return res.found(owner.toJSON());
}

// Implement your routes here

}
`

OwnerRepository.ts

delete everything related to CRUD

`
import { inject, named } from 'inversify';
import { Types, Targets } from '../../constants';
import { Owner } from '../models/Owner';
import { Pet } from '../../api/models/Pet';
import { Pets } from '../../api/models/Pets';

export class OwnerRepository {

constructor(
    @inject(Types.Model) @named(Targets.Model.Owner) public OwnerModel: typeof Owner,
    @inject(Types.Model) @named(Targets.Model.Pets) public PetsModel: typeof Pets
) { }

public async findAll(): Promise<Pet> {
    return this.PetsModel.fetchAllPet();

}

public async findOne(id: number): Promise<Pet> {
    return this.OwnerModel.fetchById(id);
}

}
`

OwnerService.ts

delete everything related to CRUD

`
import { inject, named } from 'inversify';
import { Logger as LoggerType } from '../../core/Logger';
import { Types, Core, Targets } from '../../constants';
import { NotFoundException } from '../exceptions/NotFoundException';
import { OwnerRepository } from '../repositories/OwnerRepository';
import { Owner } from '../models/Owner';
import { Pet } from '../../api/models/Pet';

export class OwnerService {

public log: LoggerType;

constructor(
    @inject(Types.Repository) @named(Targets.Repository.OwnerRepository) public ownerRepo: OwnerRepository,
    @inject(Types.Core) @named(Core.Logger) public Logger: typeof LoggerType
) {
    this.log = new Logger(__filename);
}

public async findAll(): Promise<Pet> {
    return this.ownerRepo.findAll();
}

public async findOne(id: number): Promise<Owner> {
    const owner = await this.ownerRepo.findOne(id);
    if (owner === null) {
        this.log.warn(`Owner with the id=${id} was not found!`);
        throw new NotFoundException(id);
    }
    return owner;
}

}
`

User.ts

adding the following:

`
import { Pets } from '../../api/models/Pets';
import { Pet } from '../../api/models/Pet';

public pet(): Pets {
    return this.hasMany(Pet);
}

`

Owner.ts

delete all things what relates to table, add the fetchById method
(maybe this can be also included into Pet.ts with a different name, hence here nothing releates to Owner)

`
import { Bookshelf } from '../../config/Database';
import { Pet } from '../../api/models/Pet';

export class Owner extends Bookshelf.Model {

public static async fetchById(value: number): Promise<Pet> {
    return await Pet.where<Pet>({ id: value }).fetch({withRelated: ['user']});
}

}
`

Pets.ts

create new file Pets.ts with following:

`
import { Bookshelf } from '../../config/Database';
import { Pet } from '../../api/models/Pet';
import { User } from '../../api/models/User';

export class Pets extends Bookshelf.Collection {

public static async fetchAllPet(): Promise<Pet> {
    return await User.forge<Pet>().fetch({withRelated: ['pet']});
}

}
`

result

http://localhost:3000/api/owners can be called

• it gives only the first record of the user table (and if there the user has pets assigned, they are comming along)
• it does not provide all user records and their pets!
• if I change "return await Pet.forge({id: 2 }).fetch({withRelated: ['user']});" then the user with id=2 and all his pets are listed

http://localhost:3000/api/owners/3 can be called

• it provides the user with id=3 and his pets
• if I change in Owner. ts "return await Pet.forge({id: 2 }).fetch({withRelated: ['user']});" it gives the same result

conclusion

• it worked somehow for single users but not a query to all users and their pets
• I thought forge should do that?! but fetchAll can not be used with .fetch(withRelated)???
• I am still stuck in understanding how to make it right and put the things together and need HELP

[foxthefox]

After more hours I could resolve the relational query.
I am not sure if this is approprioate way, so please indicate if I am doing something against the idea!

What did I do in particular:

1 new resource Owner

creating a new resource owner should give me the assignemnt of user to the pet

npm run console make:resource Owner
without migration, but with IoC

2 OwnerController.ts

delete everything what is CRUD, hence we are only querying

`
import { inject, named } from 'inversify';
import { controller, httpGet, response, requestParam } from 'inversify-express-utils';
import { Types, Targets } from '../../constants';
import { app } from '../../app';
import { OwnerService } from '../services/OwnerService';

// Get middlewares
const authenticate = app.IoC.getNamed<interfaces.Middleware>(Types.Middleware, Targets.Middleware.AuthenticateMiddleware);

@controller('/owners', authenticate.use)
export class OwnerController {

constructor( @inject(Types.Service) @named(Targets.Service.OwnerService) private ownerService: OwnerService) { }

@httpGet('/')
public async findAll( @response() res: myExpress.Response): Promise<any> {
    const owners = await this.ownerService.findAll();
    return res.found(owners.toJSON());
}

@httpGet('/:id')
public async findOne( @response() res: myExpress.Response, @requestParam('id') id: string): Promise<any> {
    const owner = await this.ownerService.findOne(parseInt(id, 10));
    return res.found(owner.toJSON());
}

// Implement your routes here

}
`

OwnerRepository.ts

deleted everything which relates to CRUD

`
import { inject, named } from 'inversify';
import { Types, Targets } from '../../constants';
import { Owner } from '../models/Owner';
import { Pet } from '../../api/models/Pet';
import { Pets } from '../../api/models/Pets';

export class OwnerRepository {

constructor(
    @inject(Types.Model) @named(Targets.Model.Pets) public OwnerModel: typeof Pets,
    @inject(Types.Model) @named(Targets.Model.Pet) public PetModel: typeof Pet
) { }

public async findAll(): Promise<Pets> {
    return this.PetModel.fetchAllPet();

}

public async findOne(id: number): Promise<Pet> {
    return this.PetModel.fetchPetByUserId(id);
}

}
`

OwnerService.ts

deleted everything which relates to CRUD

`
import { inject, named } from 'inversify';
import { Logger as LoggerType } from '../../core/Logger';
import { Types, Core, Targets } from '../../constants';
import { NotFoundException } from '../exceptions/NotFoundException';
import { OwnerRepository } from '../repositories/OwnerRepository';
import { Owner } from '../models/Owner';
import { Pet } from '../../api/models/Pet';
import { Pets } from '../../api/models/Pets';

export class OwnerService {

public log: LoggerType;

constructor(
    @inject(Types.Repository) @named(Targets.Repository.OwnerRepository) public ownerRepo: OwnerRepository,
    @inject(Types.Core) @named(Core.Logger) public Logger: typeof LoggerType
) {
    this.log = new Logger(__filename);
}

public async findAll(): Promise<Pets> {
    return this.ownerRepo.findAll();
}

public async findOne(id: number): Promise<Pet> {
    const owner = await this.ownerRepo.findOne(id);
    if (owner === null) {
        this.log.warn(`Owner with the id=${id} was not found!`);
        throw new NotFoundException(id);
    }
    return owner;
}

}
`

User.ts

adding the following:

`
import { Pets } from '../../api/models/Pets';
import { Pet } from '../../api/models/Pet';

public pet(): Pets {
    return this.hasMany(Pet);
}

`

Pet.ts

add the custom functions

`
public static async fetchPetByUserId(value: number): Promise {
return await User.where({ id: value }).fetch({withRelated: ['pet']});
}

public static async fetchAllPet(): Promise<Pets> {
    // return await Pet.forge<Pet>().fetchAll({withRelated: ['user']}); // lists all pets with user,
    return await User.forge<Pet>().fetchAll({withRelated: ['pet']}); // lists all user with their pets
}

`

Owner.ts

deleted this file

Pets.ts

create new file Pets.ts which creates a collection from the model

`
import { Bookshelf } from '../../config/Database';
import { Pet } from '../../api/models/Pet';

export class Pets extends Bookshelf.Collection {
}
`

result

http://localhost:3000/api/owners can be called

• it provides all user records and their pets

http://localhost:3000/api/owners/3 can be called

• it provides the user with id=3 and his pets

conclusion

• I am a bit further, but am I right?

[hirsch88]

Hi,

Looks oaky for me. You could also just add the pets as a collection to the user queries.