Overview #

To build your desired search functionality, we'll split the solution into two core parts: Angular Frontend (handling user input and displaying results) and Backend/SQL (processing search queries across multiple tables). The goal is to let users input any combination of author, title, or publisher keywords and retrieve the matching book(s).

1. Backend Implementation (SQL + API) #

First, we need an API endpoint that accepts search terms, queries your multi-table database, and returns matching books.

Assumed Database Schema #

Let’s assume you have three related tables:

• books: id, title, author_id (foreign key to authors), publisher_id (foreign key to publishers)
• authors: id, name
• publishers: id, name

Step 1: SQL Query Logic #

We need to join the tables and ensure every search keyword matches at least one of the fields (author name, book title, publisher name).

Option A: Basic LIKE Matching (Good for Small Datasets)

This approach uses LIKE with wildcard characters to find partial matches:

SELECT 
  b.id, 
  b.title, 
  a.name AS authorName, 
  p.name AS publisherName
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN publishers p ON b.publisher_id = p.id
WHERE 
  -- For each keyword, check if it exists in any of the three fields
  (a.name LIKE ? OR b.title LIKE ? OR p.name LIKE ?)
  AND 
  (a.name LIKE ? OR b.title LIKE ? OR p.name LIKE ?)
  -- Add more AND clauses for additional keywords

Each parameter is formatted as %keyword% to enable partial matches (e.g., "jo" matches "Joe").

Option B: Full-Text Search (Better for Large Datasets)

For faster performance on large databases, use MySQL full-text indexes:

1. Add full-text indexes to relevant columns:

ALTER TABLE authors ADD FULLTEXT INDEX idx_author_name (name);
ALTER TABLE books ADD FULLTEXT INDEX idx_book_title (title);
ALTER TABLE publishers ADD FULLTEXT INDEX idx_publisher_name (name);

2. Use MATCH AGAINST for efficient keyword matching:

SELECT 
  b.id, 
  b.title, 
  a.name AS authorName, 
  p.name AS publisherName
FROM books b
JOIN authors a ON b.author_id = a.id
JOIN publishers p ON b.publisher_id = p.id
WHERE 
  -- Ensure each keyword exists in at least one field
  (MATCH(a.name) AGAINST(? IN BOOLEAN MODE) OR MATCH(b.title) AGAINST(? IN BOOLEAN MODE) OR MATCH(p.name) AGAINST(? IN BOOLEAN MODE))
  AND 
  (MATCH(a.name) AGAINST(? IN BOOLEAN MODE) OR MATCH(b.title) AGAINST(? IN BOOLEAN MODE) OR MATCH(p.name) AGAINST(? IN BOOLEAN MODE))

Each parameter uses the + prefix (e.g., +joe) to enforce the keyword must be present.

Step 2: API Endpoint Example (Node.js/Express) #

Create an API that splits the search string into keywords, builds a dynamic SQL query, and returns results:

const express = require('express');
const router = express.Router();
const mysql = require('mysql2/promise');

// Database connection (update with your credentials)
const db = mysql.createPool({
  host: 'localhost',
  user: 'db_user',
  password: 'db_password',
  database: 'book_db'
});

router.get('/books/search', async (req, res) => {
  try {
    const searchQuery = req.query.query?.trim();
    if (!searchQuery) return res.json([]);

    // Split search terms into individual keywords
    const keywords = searchQuery.split(/\s+/).filter(k => k);
    if (keywords.length === 0) return res.json([]);

    // Build SQL query with dynamic conditions
    const conditions = keywords.map(() => '(a.name LIKE ? OR b.title LIKE ? OR p.name LIKE ?)');
    const sql = `
      SELECT b.id, b.title, a.name as authorName, p.name as publisherName
      FROM books b
      JOIN authors a ON b.author_id = a.id
      JOIN publishers p ON b.publisher_id = p.id
      WHERE ${conditions.join(' AND ')}
    `;

    // Prepare parameters (each keyword repeated 3x for the three fields)
    const params = [];
    keywords.forEach(key => params.push(`%${key}%`, `%${key}%`, `%${key}%`));

    const [rows] = await db.execute(sql, params);
    res.json(rows);
  } catch (err) {
    console.error('Search error:', err);
    res.status(500).json({ error: 'Failed to process search' });
  }
});

module.exports = router;

2. Angular Frontend Implementation #

Now build the Angular search component to handle user input, call the API, and display results.

Step 1: Set Up Dependencies #

Ensure these modules are imported in app.module.ts:

import { FormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';

@NgModule({
  imports: [FormsModule, HttpClientModule]
})
export class AppModule {}

Step 2: Search Component Code #

search.component.ts

import { Component } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Subject } from 'rxjs';
import { debounceTime, switchMap } from 'rxjs/operators';

// Define a Book interface to type API responses
interface Book {
  id: number;
  title: string;
  authorName: string;
  publisherName: string;
}

@Component({
  selector: 'app-book-search',
  templateUrl: './search.component.html',
  styleUrls: ['./search.component.css']
})
export class BookSearchComponent {
  searchTerm = '';
  private searchTrigger = new Subject<string>();
  books: Book[] = [];
  isLoading = false;

  constructor(private http: HttpClient) {
    // Debounce input to avoid excessive API calls
    this.searchTrigger.pipe(
      debounceTime(300), // Wait 300ms after last keystroke
      switchMap(term => {
        this.isLoading = true;
        // Call your backend API endpoint
        return this.http.get<Book[]>(`/api/books/search?query=${encodeURIComponent(term)}`);
      })
    ).subscribe({
      next: (results) => {
        this.books = results;
        this.isLoading = false;
      },
      error: () => {
        this.isLoading = false;
        this.books = [];
      }
    });
  }

  onInputChange(): void {
    this.searchTrigger.next(this.searchTerm);
  }
}

search.component.html

<div class="search-wrapper">
  <input 
    type="text"
    [(ngModel)]="searchTerm"
    (input)="onInputChange()"
    placeholder="Search by author, title, or publisher..."
    class="search-input"
  >

  <!-- Loading state -->
  <div *ngIf="isLoading" class="loading-spinner">Loading...</div>

  <!-- Results list -->
  <ul *ngIf="!isLoading && books.length > 0" class="results-list">
    <li *ngFor="let book of books" class="result-item">
      <h4>{{ book.title }}</h4>
      <p>By {{ book.authorName }} | Published by {{ book.publisherName }}</p>
    </li>
  </ul>

  <!-- No results message -->
  <p *ngIf="!isLoading && books.length === 0" class="no-results">No books found.</p>
</div>

search.component.css (Optional Styling)

.search-wrapper {
  max-width: 600px;
  margin: 2rem auto;
  padding: 0 1rem;
}

.search-input {
  width: 100%;
  padding: 0.8rem;
  font-size: 1rem;
  border: 1px solid #ddd;
  border-radius: 4px;
}

.loading-spinner {
  text-align: center;
  margin: 1rem 0;
}

.results-list {
  list-style: none;
  padding: 0;
  margin-top: 1rem;
}

.result-item {
  padding: 1rem;
  border: 1px solid #eee;
  border-radius: 4px;
  margin-bottom: 0.5rem;
}

.result-item h4 {
  margin: 0 0 0.5rem 0;
}

.no-results {
  text-align: center;
  color: #666;
  margin-top: 1rem;
}

Key Notes #

• Debouncing: The RxJS debounceTime operator ensures we don’t make an API call on every keystroke, improving performance.
• Partial Matches: The SQL LIKE (or full-text) allows partial keyword matches (e.g., "jo" finds "Joe").
• Dynamic Query Building: The backend dynamically adds conditions for each keyword, ensuring all terms are matched across the three fields.

内容的提问来源于stack exchange，提问作者Ali.K