Query Operations Examples

import { articles, authors, categories } from './schema'

// Article service with soft delete for draft/published states
const articleService = drizzleService(db)(articles, {
  soft: {
    field: 'status',
    deletedValue: 'archived',
    notDeletedValue: 'published'
  }
})

const authorService = drizzleService(db)(authors)

// Get published articles with pagination
const publishedArticles = await articleService.findAll({
  orderBy: { publishedAt: 'desc' },
  limit: 20
})

// Get featured articles for homepage
const featuredArticles = await articleService.findBy({
  featured: true,
  status: 'published'
}, {
  orderBy: { publishedAt: 'desc' },
  limit: 5
})

// Search articles by category and tags
const techArticles = await articleService.findBy({
  categoryId: 'technology',
  tags: { contains: ['javascript', 'typescript'] }
})

// Get articles by specific author
const authorArticles = await articleService.findByField(
  'authorId',
  'author-123'
)

// Get recent articles (last 30 days)
const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
const recentArticles = await articleService.findBy({
  publishedAt: { gte: thirtyDaysAgo }
})

// Get most viewed articles
const popularArticles = await articleService.findBy({
  viewCount: { gte: 1000 }
}, {
  orderBy: { viewCount: 'desc' },
  limit: 10
})

// Get articles with high engagement
const engagingArticles = await articleService.findBy({
  commentCount: { gte: 10 },
  likeCount: { gte: 50 }
})

// Find articles needing review (old unpublished drafts)
const sixMonthsAgo = new Date(Date.now() - 180 * 24 * 60 * 60 * 1000)
const staleDrafts = await articleService.findBy({
  status: 'draft',
  updatedAt: { lt: sixMonthsAgo }
})

// Get articles by reading time for content planning
const quickReads = await articleService.findBy({
  readingTimeMinutes: { lte: 5 }
})

const longFormContent = await articleService.findBy({
  readingTimeMinutes: { gte: 15 }
})

import { orders, orderItems, customers } from './schema'

// Order service with soft delete for canceled orders
const orderService = drizzleService(db)(orders, {
  soft: {
    field: 'status',
    deletedValue: 'canceled',
    notDeletedValue: 'active'
  }
})

// Get recent orders
const recentOrders = await orderService.findAll({
  orderBy: { createdAt: 'desc' },
  limit: 50
})

// Get high-value orders
const highValueOrders = await orderService.findBy({
  total: { gte: 1000 },
  status: { in: ['pending', 'processing', 'shipped'] }
})

// Get orders by date range
const startOfMonth = new Date(new Date().getFullYear(), new Date().getMonth(), 1)
const monthlyOrders = await orderService.findBy({
  createdAt: { gte: startOfMonth }
})

// Get orders by payment method
const creditCardOrders = await orderService.findBy({
  paymentMethod: 'credit_card',
  paymentStatus: 'confirmed'
})

// Find orders needing fulfillment
const pendingOrders = await orderService.findBy({
  status: 'pending',
  paymentStatus: 'confirmed'
})

// Get orders for specific customer
const customerOrders = await orderService.findByField(
  'customerId',
  'customer-456'
)

// Get repeat customers (multiple orders)
async function getRepeatCustomers() {
  // This would typically involve a more complex query with grouping
  // For demonstration, we'll show the basic pattern
  const allOrders = await orderService.findAll()
  
  const customerOrderCounts = allOrders.reduce((acc, order) => {
    acc[order.customerId] = (acc[order.customerId] || 0) + 1
    return acc
  }, {} as Record<string, number>)
  
  const repeatCustomers = Object.entries(customerOrderCounts)
    .filter(([, count]) => count > 1)
    .map(([customerId]) => customerId)
  
  return repeatCustomers
}

// Get orders by shipping location
const californiaOrders = await orderService.findBy({
  shippingState: 'CA'
})

// Get urgent orders (express shipping)
const expressOrders = await orderService.findBy({
  shippingMethod: 'express',
  status: { in: ['pending', 'processing'] }
})

import { supportTickets, agents, customers } from './schema'

// Support ticket service with priority handling
const ticketService = drizzleService(db)(supportTickets, {
  soft: {
    field: 'status',
    deletedValue: 'closed',
    notDeletedValue: 'open'
  }
})

// Get open tickets by priority
const criticalTickets = await ticketService.findBy({
  priority: 'critical',
  status: { in: ['open', 'in_progress'] }
}, {
  orderBy: { createdAt: 'asc' } // Oldest first for critical issues
})

// Get tickets assigned to specific agent
const agentTickets = await ticketService.findByField(
  'assignedAgentId',
  'agent-123'
)

// Get unassigned tickets
const unassignedTickets = await ticketService.findBy({
  assignedAgentId: null,
  status: 'open'
})

// Get overdue tickets
const twoDaysAgo = new Date(Date.now() - 2 * 24 * 60 * 60 * 1000)
const overdueTickets = await ticketService.findBy({
  createdAt: { lt: twoDaysAgo },
  status: { in: ['open', 'in_progress'] }
})

// Get tickets by category
const technicalIssues = await ticketService.findBy({
  category: 'technical',
  status: { in: ['open', 'in_progress'] }
})

import { systemLogs, performanceMetrics, errorLogs } from './schema'

const logService = drizzleService(db)(systemLogs)
const metricsService = drizzleService(db)(performanceMetrics)
const errorService = drizzleService(db)(errorLogs)

// Get recent system errors
const recentErrors = await errorService.findBy({
  createdAt: { gte: new Date(Date.now() - 60 * 60 * 1000) }, // Last hour
  severity: { in: ['error', 'critical'] }
}, {
  orderBy: { createdAt: 'desc' }
})

// Get performance metrics for monitoring
const slowQueries = await metricsService.findBy({
  metricType: 'query_duration',
  value: { gte: 1000 } // Over 1 second
})

// Get system logs by service
const apiLogs = await logService.findBy({
  service: 'api',
  level: { in: ['warn', 'error'] }
}, {
  orderBy: { timestamp: 'desc' },
  limit: 100
})

// Complex multi-field sorting
const sortedProducts = await productService.findAll({
  orderBy: [
    { featured: 'desc' },
    { rating: 'desc' }, 
    { price: 'asc' },
    { name: 'asc' }
  ],
  limit: 50
})

// Range queries with multiple conditions
const midRangeProducts = await productService.findBy({
  price: { gte: 100, lte: 500 },
  rating: { gte: 3.5 },
  inStock: true,
  categoryId: { in: ['electronics', 'accessories'] }
})

// Text search patterns
const searchQuery = 'wireless bluetooth'
const searchResults = await productService.findBy({
  or: [
    { name: { ilike: `%${searchQuery}%` } },
    { description: { ilike: `%${searchQuery}%` } },
    { tags: { contains: searchQuery.split(' ') } }
  ]
})

// Date-based queries for analytics
const lastQuarter = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000)
const quarterlyOrders = await orderService.findBy({
  createdAt: { gte: lastQuarter },
  status: { ne: 'canceled' }
}, {
  orderBy: { total: 'desc' }
})

// Cursor-based pagination for large datasets
async function getPaginatedProducts(cursor?: string, limit = 20) {
  const conditions: any = { status: 'active' }
  
  if (cursor) {
    conditions.id = { gt: cursor }
  }
  
  const products = await productService.findBy(conditions, {
    orderBy: { id: 'asc' },
    limit: limit + 1 // Get one extra to check if there are more
  })
  
  const hasMore = products.length > limit
  const items = hasMore ? products.slice(0, -1) : products
  const nextCursor = hasMore ? items[items.length - 1].id : null
  
  return {
    items,
    nextCursor,
    hasMore
  }
}

// Offset-based pagination for smaller datasets
async function getProductPage(page = 1, pageSize = 20) {
  const offset = (page - 1) * pageSize
  
  const products = await productService.findAll({
    orderBy: { createdAt: 'desc' },
    limit: pageSize,
    offset
  })
  
  return {
    items: products,
    page,
    pageSize,
    hasMore: products.length === pageSize
  }
}

// Efficient counting with conditions
async function getProductStats() {
  const total = await productService.count()
  const inStock = await productService.count({ inStock: true })
  const featured = await productService.count({ featured: true })
  
  return { total, inStock, featured }
}