How can I accomplish this query between related tables without using UNION?
Let's say I have two separate tables that I am browsing for a query. Both of these tables are related to the third table. How can I query both tables with a single UNION-independent query?
Here's a theoretical example. I have a User table. This user can have both CDs and books. I want to find all these custom books and CDs with a single query matching the string ("awesome" in this example).
A UNION based query might look like this:
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
How can I execute a query like this without UNION? If I do a simple left join from user to books and CDs, we get a total of results equal to the number of matching cds timse the number of matching books. Is there a GROUP BY or some other way of writing the query to fix this?
(EDIT: The reason I would like to avoid the Union approach is that it is actually a DQL query and Doctrine does not support UNION. If there is no way to do this without UNION, I will go to the native SQL route. Additionally, the actual query contains a bunch of extra columns that don't map well to each other in this example.)
a source to share
If you are trying to avoid concatenation, one way is to create a view.
EDIT: to create a view you have two options
If your query is only for selecting records, there should be no problem with
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
If you need a view that can be refreshed, it can fly if you refactor:
- create a table that will store all data and media type
- create two views that will split the data by media type (since these views are simple 1: 1 queries for table backing, they must be updatable and you must use them in ORM mapping or other SQL queries)
EDIT2: I forgot to comment on the fact that UNION ALL is a must for UNION unless you want MySQL to start creating an index on disk every time you run the view / query (thanks to HLGEM).
a source to share
Think about how you could model this in an OO application. You would create a superclass that you distribute for books and CDs and then your user would have a collection Collectibles
. Any given item in this set is either a book or a CD (or other collectible), but only has one of these subtypes.
You can do something similar with SQL by creating a table that matches the supertype:
CREATE TABLE Collectibles (
collectible_id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
Then each subtype contains a link to make it collectible:
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)
);
Now you can make your request and be sure you don't get a Cartesian product:
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);
a source to share
Perhaps you could try something like this ... the example assumes the third table is called User:
$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();
a source to share