Query Building

/**
 * Access or modify underlying query builder
 * @param {Function} [callback] - Function to modify query builder
 * @returns {this} Instance for chaining (Model or Collection)
 */
query(callback?: (queryBuilder: Knex.QueryBuilder) => void): this;

/**
 * Add where clause to query
 * @param {...*} args - Where clause arguments (column, operator, value) or (object) or (callback)
 * @returns {this} Instance for chaining
 */
where(...args: any[]): this;

/**
 * Add order by clause  
 * @param {string} column - Column to order by
 * @param {string} [direction='asc'] - Sort direction ('asc' or 'desc')
 * @returns {this} Instance for chaining
 */
orderBy(column: string, direction?: 'asc' | 'desc'): this;

// Simple where clause
const users = await User.where('active', true).fetchAll();

// Multiple where clauses  
const posts = await Post
  .where('status', 'published')
  .where('created_at', '>', '2023-01-01')
  .fetchAll();

// Order by
const sortedUsers = await User
  .orderBy('created_at', 'desc')
  .fetchAll();

// Query builder callback
const complexQuery = await User.query(qb => {
  qb.where('age', '>', 18)
    .andWhere('active', true)
    .orWhere('role', 'admin');
}).fetchAll();

// Available through query builder callback

/**
 * Standard where operations
 */
where(column: string, operator: string, value: any): QueryBuilder;
where(column: string, value: any): QueryBuilder; // assumes '=' operator
where(object: object): QueryBuilder; // where clauses from object properties
where(callback: (qb: QueryBuilder) => void): QueryBuilder; // nested where

/**
 * Where variants
 */
whereNot(column: string, value: any): QueryBuilder;
whereIn(column: string, values: any[]): QueryBuilder;
whereNotIn(column: string, values: any[]): QueryBuilder;
whereNull(column: string): QueryBuilder;
whereNotNull(column: string): QueryBuilder;
whereBetween(column: string, range: [any, any]): QueryBuilder;
whereNotBetween(column: string, range: [any, any]): QueryBuilder;
whereLike(column: string, pattern: string): QueryBuilder;
whereILike(column: string, pattern: string): QueryBuilder; // case insensitive

/**
 * Logical operators
 */
andWhere(...args: any[]): QueryBuilder;
orWhere(...args: any[]): QueryBuilder;

// Object-based where
const users = await User.query(qb => {
  qb.where({
    active: true,
    role: 'user',
    'age': ['>', 18]
  });
}).fetchAll();

// Where in array
const posts = await Post.query(qb => {
  qb.whereIn('category_id', [1, 2, 3, 4]);
}).fetchAll();

// Null checks
const incompleteProfiles = await User.query(qb => {
  qb.whereNull('profile_image')
    .orWhereNull('bio');
}).fetchAll();

// Range queries
const recentPosts = await Post.query(qb => {
  qb.whereBetween('created_at', ['2023-01-01', '2023-12-31']);
}).fetchAll();

// Pattern matching
const searchUsers = await User.query(qb => {
  qb.whereLike('name', '%john%')
    .orWhereLike('email', '%gmail.com');
}).fetchAll();

// Complex nested conditions
const complexSearch = await User.query(qb => {
  qb.where(function() {
    this.where('role', 'admin')
        .orWhere('permissions', '>', 5);
  }).andWhere(function() {
    this.where('active', true)
        .whereNotNull('last_login');
  });
}).fetchAll();

// Join methods available through query builder

/**
 * Join types
 */
join(table: string, column1: string, operator: string, column2: string): QueryBuilder;
join(table: string, callback: (join: JoinBuilder) => void): QueryBuilder;
leftJoin(table: string, ...args: any[]): QueryBuilder;
rightJoin(table: string, ...args: any[]): QueryBuilder;
innerJoin(table: string, ...args: any[]): QueryBuilder;
leftOuterJoin(table: string, ...args: any[]): QueryBuilder;
rightOuterJoin(table: string, ...args: any[]): QueryBuilder;
fullOuterJoin(table: string, ...args: any[]): QueryBuilder;
crossJoin(table: string, ...args: any[]): QueryBuilder;

// Simple join
const usersWithProfiles = await User.query(qb => {
  qb.join('profiles', 'users.id', 'profiles.user_id')
    .select('users.*', 'profiles.bio', 'profiles.avatar');
}).fetchAll();

// Left join with null check
const usersOptionalProfiles = await User.query(qb => {
  qb.leftJoin('profiles', 'users.id', 'profiles.user_id')
    .select('users.*', 'profiles.bio');
}).fetchAll();

// Complex join with callback
const postAuthors = await Post.query(qb => {
  qb.join('users', function() {
    this.on('posts.user_id', '=', 'users.id')
        .andOn('users.active', '=', true);
  }).select('posts.*', 'users.name as author_name');
}).fetchAll();

// Multiple joins
const postCommentAuthors = await Post.query(qb => {
  qb.join('users as post_authors', 'posts.user_id', 'post_authors.id')
    .join('comments', 'posts.id', 'comments.post_id')
    .join('users as comment_authors', 'comments.user_id', 'comment_authors.id')
    .select(
      'posts.*',
      'post_authors.name as post_author',
      'comment_authors.name as comment_author'
    );
}).fetchAll();

// Aggregation methods

/**
 * Aggregate functions
 */
count(column?: string): QueryBuilder;
countDistinct(column: string): QueryBuilder;
sum(column: string): QueryBuilder;
avg(column: string): QueryBuilder;
min(column: string): QueryBuilder;
max(column: string): QueryBuilder;

/**
 * Grouping and having
 */
groupBy(...columns: string[]): QueryBuilder;
having(column: string, operator: string, value: any): QueryBuilder;
havingRaw(raw: string, bindings?: any[]): QueryBuilder;

// Count records
const userCount = await User.query(qb => {
  qb.count('* as total');
}).fetch();

// Group by with aggregation
const postsByUser = await Post.query(qb => {
  qb.join('users', 'posts.user_id', 'users.id')
    .groupBy('users.id', 'users.name')
    .select('users.name', qb.raw('count(*) as post_count'))
    .orderBy('post_count', 'desc');
}).fetchAll();

// Having clause
const activePosters = await User.query(qb => {
  qb.join('posts', 'users.id', 'posts.user_id')
    .groupBy('users.id', 'users.name')
    .select('users.name', qb.raw('count(posts.id) as post_count'))
    .having('post_count', '>', 5);
}).fetchAll();

// Statistical aggregation
const userStats = await User.query(qb => {
  qb.select(
    qb.raw('count(*) as total_users'),
    qb.raw('avg(age) as average_age'),
    qb.raw('min(created_at) as first_user'),
    qb.raw('max(last_login) as most_recent_login')
  );
}).fetch();

// Raw query methods

/**
 * Raw SQL expressions
 */
raw(sql: string, bindings?: any[]): RawQueryBuilder;
whereRaw(sql: string, bindings?: any[]): QueryBuilder;
orWhereRaw(sql: string, bindings?: any[]): QueryBuilder;
havingRaw(sql: string, bindings?: any[]): QueryBuilder;
orderByRaw(sql: string): QueryBuilder;

// Raw where clause
const users = await User.query(qb => {
  qb.whereRaw('age > ? AND created_at > DATE_SUB(NOW(), INTERVAL ? MONTH)', [18, 6]);
}).fetchAll();

// Raw select with calculations
const userMetrics = await User.query(qb => {
  qb.select(
    'id',
    'name',
    qb.raw('DATEDIFF(NOW(), created_at) as days_since_signup'),
    qb.raw('CASE WHEN last_login > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN "active" ELSE "inactive" END as status')
  );
}).fetchAll();

// Raw order by
const customSort = await Post.query(qb => {
  qb.orderByRaw('CASE WHEN featured = 1 THEN 0 ELSE 1 END, created_at DESC');
}).fetchAll();

// Complex raw query
const monthlyStats = await bookshelf.knex.raw(`
  SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as user_count,
    COUNT(CASE WHEN active = 1 THEN 1 END) as active_count
  FROM users 
  WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
  GROUP BY DATE_FORMAT(created_at, '%Y-%m')
  ORDER BY month DESC
`);

// Subquery methods

/**
 * Subquery support
 */
whereIn(column: string, subquery: QueryBuilder | Function): QueryBuilder;
whereNotIn(column: string, subquery: QueryBuilder | Function): QueryBuilder;  
whereExists(subquery: QueryBuilder | Function): QueryBuilder;
whereNotExists(subquery: QueryBuilder | Function): QueryBuilder;

// Users who have posts
const usersWithPosts = await User.query(qb => {
  qb.whereExists(function() {
    this.select('*')
        .from('posts')
        .whereRaw('posts.user_id = users.id');
  });
}).fetchAll();

// Posts with above-average comment count
const popularPosts = await Post.query(qb => {
  qb.where('comment_count', '>', function() {
    this.avg('comment_count').from('posts');
  });
}).fetchAll();

// Users in specific cities
const cityUsers = await User.query(qb => {
  qb.whereIn('city_id', function() {
    this.select('id')
        .from('cities')  
        .where('country', 'USA');
  });
}).fetchAll();

// Complex subquery with joins
const topContributors = await User.query(qb => {
  qb.whereIn('id', function() {
    this.select('user_id')
        .from('posts')
        .join('comments', 'posts.id', 'comments.post_id')
        .groupBy('user_id')
        .having(qb.raw('count(comments.id)'), '>', 100);
  });
}).fetchAll();

// Limiting and pagination methods

/**
 * Result limiting
 */
limit(count: number): QueryBuilder;
offset(count: number): QueryBuilder;
first(): QueryBuilder; // limit(1)

/**
 * Pagination (built into Bookshelf)
 */
fetchPage(options: {
  page?: number,
  pageSize?: number,
  limit?: number,
  offset?: number
}): Promise<{models: Collection, pagination: object}>;

// Simple limit
const recentPosts = await Post
  .orderBy('created_at', 'desc')
  .query(qb => qb.limit(10))
  .fetchAll();

// Offset for manual pagination
const page2Users = await User.query(qb => {
  qb.limit(20).offset(20);
}).fetchAll();

// Built-in pagination
const {models: posts, pagination} = await Post
  .orderBy('created_at', 'desc')
  .fetchPage({
    page: 1,
    pageSize: 10
  });

console.log(pagination);
// {
//   page: 1,
//   pageSize: 10, 
//   rowCount: 156,
//   pageCount: 16
// }

// Pagination with complex query
const {models: users, pagination} = await User.query(qb => {
  qb.join('posts', 'users.id', 'posts.user_id')
    .groupBy('users.id')
    .having(qb.raw('count(posts.id)'), '>', 5)
    .select('users.*', qb.raw('count(posts.id) as post_count'));
}).fetchPage({
  page: 2,
  pageSize: 15
});

// Union operations
union(queries: QueryBuilder[], wrap?: boolean): QueryBuilder;
unionAll(queries: QueryBuilder[]): QueryBuilder;

// Common Table Expressions (CTE)  
with(alias: string, query: QueryBuilder | Function): QueryBuilder;
withRecursive(alias: string, query: QueryBuilder | Function): QueryBuilder;

// Union queries
const allContent = await bookshelf.knex
  .select('title', 'created_at', qb.raw('"post" as type'))
  .from('posts')
  .union([
    bookshelf.knex
      .select('name as title', 'created_at', qb.raw('"user" as type'))
      .from('users')
  ])
  .orderBy('created_at', 'desc');

// Common Table Expression
const userHierarchy = await bookshelf.knex
  .with('user_hierarchy', qb => {
    qb.select('id', 'name', 'manager_id', qb.raw('1 as level'))
      .from('users')
      .where('manager_id', null)
      .unionAll(function() {
        this.select('u.id', 'u.name', 'u.manager_id', qb.raw('uh.level + 1'))
            .from('users as u')
            .join('user_hierarchy as uh', 'u.manager_id', 'uh.id');
      });
  })
  .select('*')
  .from('user_hierarchy')
  .orderBy('level', 'name');

// Transaction support
const result = await bookshelf.transaction(async (t) => {
  // All queries use the same transaction
  const user = await User.query(qb => {
    qb.where('email', email);
  }).fetch({transacting: t});
  
  const posts = await Post.query(qb => {
    qb.where('user_id', user.id);
  }).fetchAll({transacting: t});
  
  return {user, posts};
});

// Index usage
const optimizedQuery = await User.query(qb => {
  // Use indexed columns in WHERE clauses
  qb.where('email', email) // assuming email has index
    .where('active', true)  // assuming active has index
    .orderBy('created_at'); // assuming created_at has index
}).fetchAll();

// Avoid N+1 queries with eager loading
const usersWithPosts = await User.fetchAll({
  withRelated: ['posts'] // Single query instead of N+1
});

// Use select() to limit columns
const lightweightUsers = await User.query(qb => {
  qb.select('id', 'name', 'email'); // Only needed columns
}).fetchAll();

// Use exists() instead of joins when you only need to check existence
const usersWithAnyPost = await User.query(qb => {
  qb.whereExists(function() {
    this.select('*').from('posts').whereRaw('posts.user_id = users.id');
  });
}).fetchAll();