如何从一个类别中排除其他类别的图书（通过交叉表） [英] how to get books from one category excluding other category (through cross table)
问题描述
现在我使用以下代码从某个类别获取图书：
For now I am using following code to get books from certain category:
$options['conditions']['Category.id'] = $category_id;
$options['joins'] = array(
array(
'table' => 'books_categories',
'alias' => 'BookCategory',
'type' => 'inner',
'conditions' => array('Book.id = BookCategory.id_book'),
),
array(
'table' => 'categories',
'alias' => 'Category',
'type' => 'inner',
'conditions' => array('BookCategory.kat = Category.id'),
),
);
$this->Book->find('all',$options);
这只是从给定category_id查找所有图书。
This just finds all books from given category_id.
因此，有3个表：类别，图书和 books_categories 。 books_categories有两个文件： book_id 和 category_id 所以基本上它的HABTM关系。问题是，一本书可能属于许多类别，我想例如找到所有类别5的书，但不包括类别5,6和7的书。我可以怎么做？
So there are 3 tables: categories,books and books_categories. books_categories has two fileds: book_id and category_id so basicly its just HABTM relation. The problem is that one book may belong to many categories and I want for example find all books from category 5 but excluding books from categories 5,6 and 7. How I can do this?
编辑--------------------------------------------- ----------------------------------
edit -------------------------------------------------------------------------------
好的所以我想出了它应该看起来在纯SQL - 条件应该是这样：
Ok so I figured out how it should look in pure SQL - the conditions should be like this:
where
category_id = <given category>
and books.book_id not in
(
select book_id from book_categories
where category_id in (<given set of cat>)
)
order by books.inserted
这将从一个类别获取所有图书，但不包括来自其他类别的图书。
现在我想强迫Cake生成类似的SQL查询。
我试过了：
this will get all books from one category but excluding books from a set of other categories. Now I want to force Cake to generate similar SQL query. I tried so far:
$options['conditions']['Category.id'] = $category_id;
$options['conditions']['AND'][] = 'Book.id NOT IN (SELECT id_book FROM book_categories WHERE kat IN (133,134))';
$options['order'] = array('Book.inserted' => 'desc');
$options['joins'] = array(
array(
'table' => 'book_categories',
'alias' => 'BookCategory',
'type' => 'inner',
'conditions' => array('Book.id = BookCategory.id_book'),
),
array(
'table' => 'categories',
'alias' => 'Category',
'type' => 'inner',
'conditions' => array('BookCategory.kat = Category.id'),
),
);
这会生成这个查询（sory - 表名称有点不同）：
This generates this query (sory - table names are little bit different):
SELECT `Book`.`id`, `Book`.`OK`, `Book`.`price`, `Book`.`link`, `Book`.`title`,
`Book`.`author`, `Book`.`img`, `Book`.`inserted`, `Book`.`checked`, `Book`.`big_img`, `Book`.`lang`, `Book`.`asin`, `Book`.`description`, `Book`.`last_update`, `Book`.`review`, `Book`.`changed`
FROM `amazon`.`linki` AS `Book`
inner JOIN `amazon`.`cross_kategorie_full` AS `BookCategory` ON (`Book`.`id` = `BookCategory`.`id_book`)
inner JOIN `amazon`.`kategorie` AS `Category` ON (`BookCategory`.`kat` = `Category`.`id`)
WHERE `Category`.`id` = 4
AND `Book`.`OK` = 'OK'
AND ((`Book`.`big_img` NOT LIKE '%no-image%')
AND (`Book`.`id` NOT IN (SELECT id_book FROM cross_kategorie_full WHERE kat IN (133,134))))
ORDER BY `Book`.`inserted` desc LIMIT 20
但是有错误：最大执行时间为30秒超过 - 所以有一些东西不能结束（循环？）在这个sql语句...
But there is error: Maximum execution time of 30 seconds exceeded - so There is something that doesnt end (loop?) in this sql statement...
推荐答案
相对于更新的问题更新
为了使sql产生正确的结果时间），您需要使用 Categories 加入，再次提供另一个别名。由于这会导致另一个问题，我建议你发布标记有 mysql 和查询优化。
结束更新
Update relative to updated question
For the sql to yield correct results (in an acceptable time) you'll need to join with Categories again giving it another alias. Since this leads to another question, I suggest you post it tagged with mysql and query-optimization.
End update
因为它是一个 HABTM关系是有点迂回 't a HABTM）。如果您在 books_categories 中的每本书类别匹配只有一行，则无法知道某本书属于哪些其他类别，因此您无法真正了解哪些类别你真的想要（即不属于那些其他类别）。它是CakePHP的数据层和模型，解决这个问题为你幕后：）
As it is, a HABTM relationship is a bit devious (since it really isn't a HABTM). If you have only one row per book-category match in books_categories you can't know to what other categories a certain book belongs to, so you can't really tell which ones you really want (i.e. don't belong in those other categories). It's CakePHP's data layer and models that solve this problem for you behind the scenes :)
我看到的唯一解决方案是使用 Set :: extract a>进一步查询您获得的结果，并过滤出属于您不想包括的类别的图书。 （例如：
The only solution I see is to use Set::extract to further query the results that you get and filter out Books that belong to Categories that you didn't want to include. (something like:
// returns all books not belonging to categories 3,4
$new_result = Set::extract('/Books/Category[id!=3][!=4]', $results);
另一方面，我发现在这样的情况下，它非常有用，查询DB和可视化SQL查询的复杂性，获得您需要的结果。此外，您应该激活CakePHP调试工具栏查看SQL
On a side note, I find it very useful in cases like this, to query the DB and visualize the complexity of the SQL query that gets you the required results. Also, you should activate the CakePHP debug toolbar to see the SQL queries that are sent to the DB so you have a better idea of what's going on behind the scenes.
CakePHP书在协会：将模型链接在一起 （强调我）。
The CakePHP book, advises the following at the end of "Associations: Linking models together" Section (emphasis mine).
使用连接允许您在CakePHP处理关联和获取数据方面具有最大的灵活性，在大多数情况下，您可以使用其他工具来实现相同的结果，例如正确定义关联，即时绑定模型并使用Containable行为。
Using joins allows you to have a maximum flexibility in how CakePHP handles associations and fetch the data, however in most cases you can use other tools to achieve the same results such as correctly defining associations, binding models on the fly and using the Containable behavior. This feature should be used with care because it could lead, in a few cases, into bad formed SQL queries if combined with any of the former techniques described for associating models.
这篇关于如何从一个类别中排除其他类别的图书（通过交叉表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
