[eluser]Dracos[/eluser]
I have part of a query where I need to limit the results of table1 by whether they are related at least one of a given set of related table2 ids. The relationship of table1 to table2 is many to many, so there is a table1_table2 join table. I think the way to do this is a subquery, but I'm not sure how to achieve the result I need.
This query gives me the counts of the related table2 objects from table1:
Code: SELECT
table1_alias.id,
table1_alias.title,
(
SELECT
count(*)
FROM
table1_table2 table1_table2_alias
WHERE
table1_table2_alias.table1_id = table1_alias.id
AND
table1_table2.table2_id in (398, 503, 718)
) AS tag_count
FROM
table1 table1_alias
HAVING
tag_count > 0;
This query accomplishes the same by moving the subquery to a where clause:
Code: SELECT
table1_alias.id,
table1_alias.title
FROM
table1 table1_alias
WHERE
(
SELECT
count(*)
FROM
table1_table2 table1_table2_alias
WHERE
table1_table2_alias.table1_id = table1_alias.id
AND
table1_table2_alias.table2_id in (398, 503, 718)
) > 0;
I don't need the table2 counts in the result, just to limit the results according to the table1 objects being related to the given table2 ids.
[eluser]Unknown[/eluser]
Hi Dracos,
As far understand this is your table schema (I don't include the foreign keys for simplicity):
Code: CREATE DATABASE test_tables;
USE test_tables;
CREATE TABLE `table1` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `table1` VALUES (1,'A'),(2,'B'),(3,'C');
CREATE TABLE `table2` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `table2` VALUES (1,'D'),(2,'E'),(3,'F'),(398,'G'),(503,'H'),(718,'I');
CREATE TABLE `table1_table2` (
`table1_id` int(10) unsigned NOT NULL,
`table2_id` int(10) unsigned NOT NULL,
PRIMARY KEY(table1_id, table2_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `table1_table2` VALUES (1,3),(2,3),(2,2),(3,1),(1,398),(2,503),(3,718);
If you execute EXPLAIN in both queries, you will see that it reply the same subcommands, so they are equals:
Code: EXPLAIN SELECT
-> table1_alias.id,
-> table1_alias.title,
-> (
-> SELECT
-> count(*)
-> FROM
-> table1_table2 table1_table2_alias
-> WHERE
-> table1_table2_alias.table1_id = table1_alias.id
-> AND
-> table1_table2_alias.table2_id in (398, 503, 718)
-> ) AS tag_count
-> FROM
-> table1 table1_alias
-> HAVING
-> tag_count > 0;
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
| 1 | PRIMARY | table1_alias | ALL | NULL | NULL | NULL | NULL | 3 | |
| 2 | DEPENDENT SUBQUERY | table1_table2_alias | ref | PRIMARY | PRIMARY | 4 | test_tables.table1_alias.id | 1 | Using where; Using index |
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT
-> table1_alias.id,
-> table1_alias.title
-> FROM
-> table1 table1_alias
-> WHERE
-> (
-> SELECT
-> count(*)
-> FROM
-> table1_table2 table1_table2_alias
-> WHERE
-> table1_table2_alias.table1_id = table1_alias.id
-> AND
-> table1_table2_alias.table2_id in (398, 503, 718)
-> ) > 0;
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
| 1 | PRIMARY | table1_alias | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | table1_table2_alias | ref | PRIMARY | PRIMARY | 4 | test_tables.table1_alias.id | 1 | Using where; Using index |
+----+--------------------+---------------------+------+---------------+---------+---------+-----------------------------+------+--------------------------+
2 rows in set (0.00 sec)
In perfomance you will get the same result. The main reason for using WHERE clause is to select rows that are to be included in the query. In this case, you get the same, so they are identical.
Regards
Angel
[eluser]Dracos[/eluser]
Thanks for the answer, but it didn't address the question: how do I build the second query in DataMapper?
[eluser]Dracos[/eluser]
I worked this out in DataMapper.
Code: $t1 = new table1();
$t2 = new table2();
$t2->select_func('COUNT', '*', 'count');
$t2->where_related('table1', 'id', '${parent}.id');
$t1->where_subquery('0 < ', $t2);
Once you realize that DM forces static values to be on the left side of an operator (which is not common practice for me), a lot of things become clearer. You can go further when you also realize that $field can also refer to a static value with an optional operator. I would naturally write this as "... WHERE (subquery) > 0 ..." but DM forces it to be "... WHERE 0 < (subquery) ...".
|
