在 Zend Framework 2 上使用表达式执行多个连接 [英] Executing multiple join with expressions on Zend Framework 2
问题描述
实际上我正在做一个项目,我正在研究 Zend Framework 2 如何处理复杂的查询(特别是关于如何加入 n:m 表以及如何使用 GROUP_CONCAT 和其他函数).您知道执行此查询的最佳做法吗:
Actually I'm working on a project and I'm looking on how Zend Framework 2 handle complex queries (expecially on how to join n:m tables and how to use GROUP_CONCAT and other functions). Do you know the best practice to execute this query:
SELECT o. * , x.group_one, x.group_two
FROM table_one AS o
LEFT JOIN (
SELECT r.fk1, GROUP_CONCAT( t.field_one ) AS group_one, GROUP_CONCAT( t.field_two ) AS group_two
FROM table_three AS r
INNER JOIN table_two AS t ON r.fk2 = t.id
GROUP BY r.fk1
) AS x ON o.id = x.fk1
LIMIT 0 , 20;
使用这个数据库架构:
--
-- Database: `table-test-1`
--
-- --------------------------------------------------------
--
-- Structure of table `table_one`
--
CREATE TABLE `table_one` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`field_1` varchar(255) NOT NULL,
`field_2` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
--
-- Dump for table `table_one`
--
INSERT INTO `table_one` (`id`, `field_1`, `field_2`) VALUES
(1, 'baz', 'bat'),
(2, 'foo', 'bar'),
(3, 'foo2', 'bat2'),
(4, 'fuz', 'bar2'),
(5, 'poo', 'pee');
-- --------------------------------------------------------
--
-- Structure of table `table_three`
--
CREATE TABLE `table_three` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fk1` bigint(20) NOT NULL,
`fk2` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk1` (`fk1`,`fk2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
--
-- Dump for table `table_three`
--
INSERT INTO `table_three` (`id`, `fk1`, `fk2`) VALUES
(5, 1, 1),
(1, 1, 2),
(6, 1, 4),
(2, 2, 2),
(4, 3, 2),
(7, 3, 3),
(3, 4, 1),
(8, 5, 3),
(9, 5, 4);
-- --------------------------------------------------------
--
-- Structure of table `table_two`
--
CREATE TABLE `table_two` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`field_one` varchar(255) NOT NULL,
`field_two` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Dump for table `table_two`
--
INSERT INTO `table_two` (`id`, `field_one`, `field_two`) VALUES
(1, 'label_name_1', 'label_extended_name_1'),
(2, 'label_name_2', 'label_extended_name2'),
(3, 'label_name_3', 'label_extended_name_3'),
(4, 'label_name_4', 'label_extended_name4');
目前我使用带有手工查询的 Zend\Db\Sql\Sql 语句解决了问题,但我想知道实际上是否有一种方法可以使用本机 Select()(可能没有使用 Doctrine 或类似方法).
At the moment I solved using a Zend\Db\Sql\Sql statement with an handmade query, but I'd like to know if there is, actually, a way to do this with a native Select() (possibly without using Doctrine or similar).
先谢谢你:)
推荐答案
你必须导入 use Zend\Db\Sql\Predicate\Expression;
才能使用 group_concat.
You have to import use Zend\Db\Sql\Predicate\Expression;
to use group_concat.
例如:
$sql = new Sql($this->adapter);
$select = $sql->select();
$select->columns(array('*'));
$select->from('tblCGii');
$select->join("tblCGFieldValues", "tblCGii.id = tblCGFieldValues.Cgii_id", array("field_values"=>new Expression("Group_Concat(tblCGFieldValues.field_values)")),"LEFT");
$select->group('tblCGii.id');
这篇关于在 Zend Framework 2 上使用表达式执行多个连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!