代码之家 › 专栏 › 技术社区 › Jeremy Kauffman

在不使用UNION的情况下,如何在相关表之间执行此查询?

union doctrine sql

Jeremy Kauffman · 技术社区 · 14 年前

假设我有两个单独的表要查询。这两个表都与第三个表有关系。如何使用一个不基于联合的查询来查询两个表?

这是一个理论上的例子。我有一个用户表。该用户可以同时拥有CD和书籍。我想找到该用户的所有书籍和CD,其中只有一个查询与一个字符串相匹配(本例中的“棒极了”)。

基于联合的查询可能如下所示:

SELECT "book" AS model, name, ranking 
 FROM book 
WHERE name LIKE 'Awesome%' 
UNION 
SELECT "cd" AS model, name, ranking 
  FROM cd 
 WHERE name LIKE 'Awesome%' 
ORDER BY ranking DESC

如果没有联合,如何执行这样的查询?如果我做一个简单的从用户到图书和CD的左连接,我们最终得到的结果总数等于匹配的CD数量乘以匹配的图书数量。是否有GroupBy或其他写入查询的方法来解决此问题?

(编辑:我想避免使用联合方法的原因是因为这实际上是一个DQL查询,并且条令不支持联合。如果没有联合就无法做到这一点,我将使用本机SQL路由。此外,实际查询还包含一组额外的列,这些列在上面的示例中无法很好地相互映射。)

4 回复 | 直到 14 年前

Unreason 14 年前

如果您试图避免联合,一种方法是创建视图。

编辑:到 create view 你有两个选择

如果您的查询仅用于选择记录,那么

CREATE VIEW media AS
SELECT "book" AS model, name, ranking 
 FROM book 
WHERE name LIKE 'Awesome%' 
UNION
SELECT "cd" AS model, name, ranking 
  FROM cd 
 WHERE name LIKE 'Awesome%' 
ORDER BY ranking DESC

如果您需要可以更新的视图,那么如果重构,它可能会飞起来:

创建将保存所有数据和媒体类型的表
创建两个视图,用于拆分媒体类型上的数据(由于这些视图是简单的1:1查询到参考表,因此它们应该是可更新的,并且您应该能够在ORM映射或其他SQL查询中使用它们)

edit2:我忘了评论union all是一个必须优先于union的事实,除非您希望mysql在每次运行视图/查询时都开始在磁盘上构建索引(谢谢hlgem)。

Bill Karwin 14 年前

想想如何在一个OO应用程序中建模。您将创建一个为书籍和CD扩展的超类,然后您的用户将拥有一组 Collectibles . 该集合中的任何给定对象要么是一本书,要么是一张CD(或者其他类型的可收集的),但它恰好具有这些子类型中的一个。

通过创建一个桌子对应于父类型:

CREATE TABLE Collectibles (
  collectible_id SERIAL PRIMARY KEY,
  user_id        INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

然后,每个子类型都包含一个引用,使其具有可收集性:

CREATE TABLE Books (
  book_id   BIGINT UNSIGNED PRIMARY KEY
  book_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (book_id) REFERENCES Collectibles(collectible_id)
);

CREATE TABLE CDs (
  cd_id   BIGINT UNSIGNED PRIMARY KEY
  cd_name VARCHAR(100) NOT NULL,
  FOREIGN KEY (cd_id) REFERENCES Collectibles(collectible_id)
);

现在,您可以进行查询,并确保不会得到笛卡尔产品:

SELECT u.*, COALESCE(b.book_name, d.cd_name) AS media_name
FROM Users u
JOIN Collectibles c ON (u.user_id = c.user_id)
LEFT OUTER JOIN Books b ON (b.book_id = c.collectible_id)
LEFT OUTER JOIN CDs d ON (d.cd_id = c.collectible_id);

KM. 14 年前

除非有令人信服的理由不使用工会,否则只使用工会。

Tom 14 年前

也许你可以试试这样的…该示例假定第三个表称为用户:

$q = Doctrine_Query::create()
  ->select('c.cd, b.book')
  ->from('User u')
  ->LeftJoin('Cd c ON u.user_id = c.user_id AND c.name LIKE ?, 'Awesome%')
  ->LeftJoin('Book b ON u.user_id = b.user_id AND b.name LIKE ?, 'Awesome%');
$result = $q->execute();