SELECT with JOIN

Question

Hi all,

I want to build a query using Zend_db_select and JOIN. The database is as follows:

[AUTHOR table] pk: 'id' attr: 'name'

[BOOK table] pk: 'id' attr: 'name' fk: 'author.id'

I have author.name as a parameter. I want to retrieve all related books (only book.name, not the other columns)

Please help. Thanks

Answer 1

If you really need to use Zend_Db_Select, you can do this:

$select = $db->select()
    ->from(array("a"=>"AUTHOR"), array("author_name"=>"name"))
    ->join(array("b"=>"BOOK"), "a.id = b.`author.id`", array("book_name"=>"name"))
    ->where("a.name = ?", $authorName);

$stmt = $select->query();

However, this comes with the usual caveat: there's no requirement to use Zend_Db_Select for every query. Zend_Db_Select is best used when your query must be built dynamically based on application conditions. In this case, there's no reason to use Zend_Db_Select.

So I would do this more plainly by just using literal SQL:

$sql = "
    SELECT a.name AS author_name, b.name AS book_name
    FROM AUTHOR AS a JOIN BOOK AS b ON a.id = b.`author.id`
    WHERE a.name = ?";
$books = $db->fetchAll($sql, $authorName);

---

Re your comment: Zend_Db_Select doesn't provide anything in terms of security or performance that you can't get just as easily in other ways. Whether you use Zend_Db_Select or just a single literal string for your SQL, the end result is the same thing: a string that you subsequently prepare as an SQL query. The database has no idea of the different ways you created that string.

It's common for applications to add columns, tables, or expressions to an SQL query conditionally, depending on application logic. In these scenarios, it's handy to have a class like Zend_Db_Select that helps you add clauses to the SQL query.

But if you have a query that doesn't need any dynamic clauses, it's simpler, more readable, easier to debug, and easier to maintain to express the SQL query in one string literal, as in my second example above.

As for security, you can use a parameter placeholder for the author name, and supply that value as an argument after you prepare the query. You can do this just as easily with a literal SQL query as you can with Zend_Db_Select.