如何比较条件中的两个字段/列? [英] How to compare two fields/columns in a condition?

问题描述

我很难弄清楚如何使子查询正常工作.

I am having a hard time trying to figure out how to get a sub-query working.

想象我有:

    $schools
        ->select($this->Schools)
        ->select([
            'pupilcount' => $this->Pupils
                ->find()
                ->select([
                    $this->Pupils->find()->func()->count('*')
                ])
                ->where([
                    'Pupils.school_id' => 'Schools.id', 

                ]),

我遇到的问题(我认为)是Schools.id始终为0，因此计数返回为0.我可以拔出Pupils联接并显示那里的Pulps.

The problem I am experiencing (I think) is that Schools.id is always 0 and so the count is returned as 0. I can pull out the Pupils join and it shows Pupils there.

我尝试更改代码以添加:

I tried changing my code to add a:

->select(['SCID' => 'Schools.id'])

并在子查询中引用该引用但不起作用，它将始终为pupilcount返回0.

and reference that in the sub-query but doesn't work, it will always return 0 for the pupilcount.

我在做什么错了?

推荐答案

每当遇到查询问题时，请检查实际生成的查询(例如，使用

Whenever encountering query problems, check what queries are actually being generated (for example using DebugKit). Unless being an expression object, the right hand side of a condition will always be bound as a parameter, ie you're comparing against a string literal:

Pupils.school_id = 'Schools.id'

通常，为了获得适当的自动报价兼容性，列名称应为标识符表达式.虽然左侧会自动正确处理，但右侧则需要手动处理.

Generally for proper auto quoting compatibility, column names should be identifier expressions. While the left hand side will automatically be handled properly, the right hand side would require to be handled manually.

在您的特定情况下，您可以轻松地利用QueryExpression::equalFields()，这正是您要尝试做的事情，它可以比较字段/列:

In your specific case you could easily utilize QueryExpression::equalFields(), which is ment for exactly what you're trying to do, comparing fields/columns:

->where(function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
    return $exp->equalFields('Pupils.school_id', 'Schools.id');
})

还可以通过简单地实例化它们来手动创建标识符表达式:

It's also possible to create identifier expressions manually by simply instantiating them:

->where([
    'Pupils.school_id' => new \Cake\Database\Expression\IdentifierExpression('Schools.id')
])

或从CakePHP 3.6版本开始，通过Query::identifier()方法:

or as of CakePHP 3.6 via the Query::identifier() method:

->where([
    'Pupils.school_id' => $query->identifier('Schools.id')
])

最后，您还可以始终传递单个字符串值，该字符串值基本上作为原始SQL插入查询中，但是在这种情况下，标识符将不受自动标识符引用的约束:

And finally you could also always pass a single string value, which is basically inserted into the query as raw SQL, however in that case the identifiers will not be subject to automatic identifier quoting:

->where([
    'Pupils.school_id = Schools.id'
])

另请参见

• 菜谱>数据库访问& ORM>查询生成器>高级条件
• API> \ Cake \ Database \ Expression \ QueryExpression :: equalFields()
• API> \ Cake \ Database \ Expression \ IdentifierExpression

这篇关于如何比较条件中的两个字段/列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！