join or preload

[ccapeng]

I have the followings 2 methods(join/preload) to get my books data.
I prefer to use method 2 (preload), but I not sure how underneath data work.
I print out the sql, but it only show one select. Not seeing table join.

Method 1: Join table

const books = await Database
    .from("books")
    .leftJoin("categories", "books.category_id", "categories.id")
    .leftJoin("publishers", "books.publisher_id", "publishers.id")
    .leftJoin("authors", "books.author_id", "authors.id")
    .select("books.id as id")
    .select("books.title as title")
    .select("books.category_id as categoryId")
    .select("books.publisher_id as publisherId")
    .select("books.author_id as authorId")
    .select("categories.name as categoryName")
    .select("publishers.name as publisherName")
    .select("authors.last_name as authorLastName")
    .select("authors.first_name as authorFirstName")
let results:IBookDetail[] = [];
books.forEach(book=>{
    results.push(Book.getQueryJson(book));
});
return response.json(results);

Method 2: preload

const books = await Book
    .query()
    .select("id", "title", "category_id", "publisher_id", "author_id")
    .preload("category", (profileQuery) => {
        profileQuery
            .select("id","name")
    })
    .preload("publisher", (profileQuery) => {
        profileQuery
            .select("id","name")
    })
    .preload("author", (profileQuery) => {
        profileQuery
            .select("id","last_name", "first_name")
    });
return response.json(books);

Book Model

export interface IBook {
  id: number,
  title: string,
  categoryId: number,
  publisherId: number,
  authorId: number
}

export interface IBookDetail {
  id: number,
  title: string,
  category: ICategory,
  publisher: IPublisher,
  author: IAuthor
}


export default class Book extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public title: string

  @column()
  public categoryId: number

  @belongsTo(() => Category, {
    foreignKey: 'categoryId',
  })
  public category: BelongsTo<typeof Category>
  
  @column()
  public publisherId: number

  @belongsTo(() => Publisher, {
    foreignKey: 'publisherId',
  })
  public publisher: BelongsTo<typeof Publisher>

  @column()
  public authorId: number

  @belongsTo(() => Author, {
    foreignKey: 'authorId',
  })
  public author: BelongsTo<typeof Author>

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime

  public getJson():IBook {
    return {
      id: this.id,
      title: this.title,
      categoryId: this.categoryId,
      publisherId: this.publisherId,
      authorId: this.authorId
    } as IBook
  }

  static getQueryJson(obj:any):IBookDetail {
    return {
      id: obj.id,
      title: obj.title,
      category: {
          id: obj.categoryId,
          name: obj.categoryName
      },
      publisher: {
          id: obj.publisherId,
          name: obj.publisherName
      },
      author: {
          id: obj.authorId,
          lastName: obj.authorLastName,
          firstName: obj.authorFirstName
      }
    }
  }

}

[thetutlage]

How do you print the SQL?

[ccapeng]

I try to print out sql in this way:

        const books = await Book
            .query()
            .select("id", "title", "category_id", "publisher_id", "author_id")
            .preload("category", (profileQuery) => {
                profileQuery
                    .select("id","name")
            })
            .preload("publisher", (profileQuery) => {
                profileQuery
                    .select("id","name")
            })
            .preload("author", (profileQuery) => {
                profileQuery
                    .select("id","last_name", "first_name")
            }).toSQL();
        console.log(books);

So the console.log print out

{
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: 'YE7J1a5lMgIqntiQQV3gx',
  sql: 'select `id`, `title`, `category_id`, `publisher_id`, `author_id` from `books`'
}

I don't see the sql doing table join.

Also, I am looking a way to have sql log print out all the time.
In the database.ts, I turn debug to true. But, nothing happen.

    sqlite: {
      client: 'sqlite',
      connection: {
        filename: Application.tmpPath('db.sqlite3'),
      },
      useNullAsDefault: true,
      healthCheck: false,
			//debug: false,
      debug: true,
    },

[Vishwas-verma]

Hey @thetutlage , what if someone want to get SQL statement that is being executed while querying?
How can we achieve it since debug is not returning response and also toQuery method or toSQL method are returning only outer query and not complete query like mentioned above , is there a way to get full query that is executed including subQueries ?

[thetutlage]

The preload queries are separate queries which are executed using the results of the outer query. Since outer query is never executed, there is no result and hence the preload queries cannot be executed.

[skrenek]

FWIW, I've always found it easiest to debug queries with either:
DEBUG=knex:query node ace serve
or more broadly with:
DEBUG=knex:* node ace serve

[Vishwas-verma]

@skrenek but the problem with this is lets suppose we have a query to execute-

  public async index({response}: HttpContextContract) {
    const projects = await Project.query().where('description','LIKE',`%nd`).preload('members', query => query.wherePivot('role','project_leader').orWherePivot('role','intern')).preload('creator');
    return response.json(await (new ProjectTransformer().transformList(projects,["createdBy","members"])))
  }

It will produce output -

select `users`.*, `project_users`.`project_id` as `pivot_project_id`, `project_users`.`user_id` as `pivot_user_id`, `project_users`.`role` as `pivot_role` from `users` inner join `project_users` on `users`.`id` = `project_users`.`user_id` where `project_users`.`role` = ? or `project_users`.`role` = ? and `project_users`.`project_id` in (?, ?, ?, ?, ?) 

It places question mark i.e ( ? ) in places of values how to overcome this problem??
Also along with this many outer queries are debugged ??

Example

 knex:query select * from `projects` where `description` like ? undefined +0ms
  knex:query select `users`.*, `project_users`.`project_id` as `pivot_project_id`, `project_users`.`user_id` as `pivot_user_id`, `project_users`.`role` as `pivot_role` from `users` inner join `project_users` on `users`.`id` = `project_users`.`user_id` where `project_users`.`role` = ? or `project_users`.`role` = ? and `project_users`.`project_id` in (?, ?, ?, ?, ?) undefined +42ms
  knex:query select * from `users` where `id` in (?) undefined +7ms
  knex:query select * from `todos` where `created_by` = ? undefined +12ms
  knex:query select * from `todos` where `created_by` = ? undefined +4ms
  knex:query select * from `todos` where `created_by` = ? undefined +4ms
  knex:query select * from `todos` where `created_by` = ? undefined +3ms
  knex:query select * from `todos` where `created_by` = ? undefined +2ms
  knex:query select * from `todos` where `created_by` = ? undefined +7ms
  knex:query select * from `todos` where `created_by` = ? undefined +2ms
  knex:query select * from `todos` where `created_by` = ? undefined +1ms
  knex:query select * from `todos` where `created_by` = ? undefined +1ms

[skrenek]

Using the DEBUG environment variable does indeed log all queries, and I don't believe there is a way around that. I've never found it to be a problem, as I only turn it on when I'm trying to figure out why a query isn't working during development and I don't mind combing through the output.

Regarding viewing the values placed in the question marks, the second form I mentioned (DEBUG=knex:*) will include knex messages of all types. The two of interest are knex:query and knex:bindings. I've included some sample output from one of my projects below as an example.

knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select * from "tenants" where "url_key" = ? limit ? undefined +0ms
knex:bindings [ 'example_tenant_name', 1 ] undefined +0ms
knex:client releasing connection to pool: __knexUid1 +36ms

As you can see, the array items in the bindings match the quesiton marks in the query above it.

This may or may not be of help to you. I just mentioned it as another tool to put in the box for those who might not be aware of it.