Hello all, I have a question that seems like it has an obvious solution, yet I can't really find an answer anywhere.
Suppose that I have a database of books and book authors. Each book can have multiple authors, and each author can have multiple books. I would want to search both book titles and author names at the same time, and show as results a list of books and their authors, regardless of whether a keyword is found in a book title or an author name. In SQL parlance, I'd like to do something like: SELECT * FROM books, books_authors, authors WHERE books.id = books_authors.bookid AND books_authors.authorid = authors.id AND ( books.title LIKE :search_key OR authors.name LIKE :search_key ) About the only way of doing this I can think of is to perform the search, get all the found books and authors, then perform another query that fetches all the books and authors referenced by any of books or authors in the first query. Is there a smarter way of doing this? What are the best practices?
