在PHP MySQL查询中获取外键对象的干净方法 [英] Clean way to get foreign key objects in PHP MySQL query

问题描述

我使用以下代码从我的MySQL数据库中获取一个图书对象.

I use the following code to get a book object from my MySQL database.

$q = $pdo->prepare('SELECT 
                        book_id "id", 
                        book_title "title", 
                        book_slug "slug"
                    FROM book 
                    WHERE book_id=:id');
$q->bindParam(':id', $id, PDO::PARAM_INT);
$q->execute();
$book = $q->fetchObject('Book');

书籍与歌曲有多对多的关系，它们具有song_id，song_title和song_lyrics，所以我有一个包含book_id和song_id的book_song表.

Books have a many-to-many relation to songs, which have a song_id, song_title and song_lyrics, so I have a book_song table with book_id and song_id.

我想把所有的歌曲都归一本书.有一个干净的方法可以在一个查询中执行此操作吗?

I'd like to get all the songs belonging to a book. Is there a clean way to do this in one query?

我在这里寻找的是如何以一种简洁的方式编写查询，以及如何处理结果以最终得到一个Book对象，该对象具有一个Song对象数组，而没有太多代码.

What I'm looking for here is how to write the query in a clean way and also how to process the results to end up with a Book object which has an array of Song objects without too much code.

推荐答案

gillyspy建议的JOIN将是ORM的处理方式，但是如果您仅使用PDO，则必须自己处理所有映射(和循环，以及检测何时创建新对象等.)在我看来，您已经超出了考虑ORM框架(例如

The JOIN that gillyspy suggested would be how an ORM would handle this, but if you're using just PDO, then you have to handle all the mappings yourself (and the looping, and detecting when to create a new object, etc.) This looks to me like you've crossed the threshold where it makes sense to consider an ORM framework (such as http://www.phpactiverecord.org/). If you add even one more one-to-many association, the mappings are going to make your brain hurt.

这篇关于在PHP MySQL查询中获取外键对象的干净方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！