php – 交叉加入DQL

发布时间：2020-12-13 22:23:15 所属栏目：PHP教程来源：网络整理

导读：我正在尝试将此转换为我认为简单的 mysql查询到Doctrine dql,但是,我现在经历了相当困难… SELECT (c.prix-aggregates.AVG) AS test FROM immobilier_ad_blank c CROSS JOIN ( SELECT AVG(prix) AS AVG FROM immobilier_ad_blank) AS aggregates 目的：创建z

我正在尝试将此转换为我认为简单的 mysql查询到Doctrine dql,但是,我现在经历了相当困难…

SELECT (c.prix-aggregates.AVG) AS test 
FROM immobilier_ad_blank c 
CROSS JOIN (
     SELECT AVG(prix) AS AVG 
     FROM immobilier_ad_blank) 
AS aggregates

目的：创建z得分.
来自这个问题Calculating Z-Score for each row in MySQL? (simple)的原始实现

我想在实体中创建一个关联,但我的意思是它没有必要,它只用于统计数据.

编辑：顺便说一下,我不想使用原始SQL,我将使用getDQL从另一个查询构建器表达式中提取“子查询”.否则,我将不得不重写我的动态查询构建器以考虑rawSQL.

编辑2：
试过这个

$subQb = $this->_em->createQueryBuilder();
$subQb->addSelect("AVG(subC.prix) as AMEAN")
      ->from("MomoaIntegrationBundle:sourcesCommon","subC");
$subDql = $subQb->getDQL();

$dql = "SELECT c.prix FROM MomoaIntegrationBundle:sourcesCommon c INNER JOIN ($subDql) AS aggregates";

原始dql是：

SELECT c.prix FROM MomoaIntegrationBundle:sourcesCommon c INNER JOIN (SELECT AVG(subC.prix) as AMEAN FROM MomoaIntegrationBundle:sourcesCommon subC) AS aggregates

得到这个奇怪的错误：第0行,第70行附近'(SELECT AVG(subC.prix)’：错误：类'(‘未定义.

编辑3：
我发现有点强硬的方式让它发挥作用,但是学说对实体等的实现很顽固,并忘记了统计学不需要实体！

$subQb = $this->_em->createQueryBuilder();
    $subQb->addSelect("AVG(subC.prix) as AMEAN")
            ->from("MomoaIntegrationBundle:sourcesCommon","subC");

    $sql = "SELECT (c.prix-aggregates.sclr_0) AS test FROM immobilier_ad_blank c CROSS JOIN "
            . "({$subQb->getQuery()->getSQL()}) AS aggregates";
    $stm = $stm = $this->_em->getConnection()->prepare($sql);
    $stm->execute();
    $data = $stm->fetchAll();

如果你有更好的解决方案,我全都耳朵！我其实不喜欢这个解决方案.

解决方法

对于复杂查询,您可能需要考虑绕过DQL并使用本机查询 – 尤其是因为您不需要实体中的结果.

$connection = $em->getConnection();

$statement = $connection->prepare("
    select c.prix-aggregates,t1.avg 
    from immobilier_ad_blank
    cross join (
        select avg(prix) as avg
        from immobilier_ad_blank
    ) t1
");

$statement->execute();

$results = $statement->fetchAll();

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!