php - CakePHP4 : Concatenated where on connected table not working as expected

Question

我得到了以下场景。

软件:当前 CakePHP 4 Beta(Commit 48c3f80f8f)

表的 SQL

让我们想象一下我们有 2 个表。

DROP TABLE IF EXISTS customers;
CREATE TABLE customers
(
    id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    company        VARCHAR(255),
    first_name     VARCHAR(255),
    last_name      VARCHAR(255),
    created        DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified       DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS customers_contacts;
CREATE TABLE customers_contacts
(
    id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    company        VARCHAR(255),
    first_name     VARCHAR(255),
    last_name      VARCHAR(255),
    customers_id   INT UNSIGNED NOT NULL,
    created        DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified       DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

因此我们在客户和customers_contacts之间建立了一对多的联系

// CustomersTable.php
$this->hasMany( 'CustomersContacts', [
  'foreignKey' => 'customers_id'
] );

// CustomersContactsTable.php
$this->belongsTo( 'Customers', [
  'foreignKey' => 'customers_id'
] );

搜索实现

第一步是实现搜索，我不仅可以搜索单个列值，还可以搜索连接列，例如“名字姓氏”或“姓氏名字”

这有效

我确实通过索引函数中的以下代码实现了这一点

$query = $this->Customers->find( 'all' )
                                  ->contain( ['CustomersContacts'] );
$current_request = $this->getRequest();

$search = $current_request->getQuery( 'search' );

// Default cake sort by clicking on table head
$sort_dir = $current_request->getQuery( 'direction' );
$sort_col = $current_request->getQuery( 'sort' );

if ( $current_request->is( 'get' ) ) {
  // Default sort by modified date
  if ( $sort_dir == '' && $sort_col == '' ) {
    $query->orderDesc( 'Customers.modified' );
  }

  // Change query if a search parameter is given in GET data
  if ( $search ) {

    // Search for concat fields in cakephp >=3
    // https://stackoverflow.com/questions/35405261/search-with-concat-fields-in-cakephp-3
    $query->where( function( $exp, $q ) use ( $search ) {
      /**
        * @var QueryExpression $exp
        * @var Query $q
        */

      // Concatenated Customers Name
      $conc1 = $q->func()
                  ->concat( [
                              'Customers.first_name' => 'literal',
                              ' ',
                              'Customers.last_name' => 'literal'
                            ] );
      $conc2 = $q->func()
                  ->concat( [
                              'Customers.last_name' => 'literal',
                              ' ',
                              'Customers.first_name' => 'literal'
                            ] );

      return $exp->or( [
                          'Customers.company LIKE' => "%$search%",
                          'Customers.first_name LIKE' => "%$search%",
                          'Customers.last_name LIKE' => "%$search%",
                        ] )
                  ->like( $conc1, "%$search%" )
                  ->like( $conc2, "%$search%" );
    } );

    $current_request = $current_request->withData( 'search', $search );
    $this->setRequest( $current_request );
  }
}

$Customers = $this->paginate( $query );

$this->set( compact( 'Customers' ) );

这不起作用

然后我认为我可以通过使用以下代码添加连接的客户联系人表的名字和姓氏来增强搜索:

$query = $this->Customers->find( 'all' )
                                  ->contain( ['CustomersContacts'] );
$current_request = $this->getRequest();

$search = $current_request->getQuery( 'search' );

// Default cake sort by clicking on table head
$sort_dir = $current_request->getQuery( 'direction' );
$sort_col = $current_request->getQuery( 'sort' );

if ( $current_request->is( 'get' ) ) {
  // Default sort by modified date
  if ( $sort_dir == '' && $sort_col == '' ) {
    $query->orderDesc( 'Customers.modified' );
  }

  // Change query if a search parameter is given in GET data
  if ( $search ) {

    // Search for concat fields in cakephp >=3
    // https://stackoverflow.com/questions/35405261/search-with-concat-fields-in-cakephp-3
    $query->where( function( $exp, $q ) use ( $search ) {
      /**
        * @var QueryExpression $exp
        * @var Query $q
        */

      // Concatenated Customers Name
      $conc1 = $q->func()
                  ->concat( [
                              'Customers.first_name' => 'literal',
                              ' ',
                              'Customers.last_name' => 'literal'
                            ] );
      $conc2 = $q->func()
                  ->concat( [
                              'Customers.last_name' => 'literal',
                              ' ',
                              'Customers.first_name' => 'literal'
                            ] );

      // Concatenated CustomersContacts Name
      $conc3 = $q->func()
                  ->concat( [
                              'CustomersContacts.first_name' => 'literal',
                              ' ',
                              'CustomersContacts.last_name' => 'literal'
                            ] );
      $conc4 = $q->func()
                  ->concat( [
                              'CustomersContacts.last_name' => 'literal',
                              ' ',
                              'CustomersContacts.first_name' => 'literal'
                            ] );

      return $exp->or( [
                          'Customers.company LIKE' => "%$search%",
                          'Customers.first_name LIKE' => "%$search%",
                          'Customers.last_name LIKE' => "%$search%",
                          'CustomersContacts.first_name LIKE' => "%$search%",
                          'CustomersContacts.last_name LIKE' => "%$search%"
                        ] )
                  ->like( $conc1, "%$search%" )
                  ->like( $conc2, "%$search%" )
                  ->like( $conc3, "%$search%" )
                  ->like( $conc4, "%$search%" );
    } );

    $current_request = $current_request->withData( 'search', $search );
    $this->setRequest( $current_request );
  }
}

$Customers = $this->paginate( $query );

$this->set( compact( 'Customers' ) );

错误

但是现在当我执行搜索时出现以下错误:

Column not found: 1054 Unknown column 'CustomersContacts.first_name' in 'where clause'

我不太明白，因为 Customers 和 CustomersContacts 之间的连接应该是通过

->contain( ['CustomersContacts'] );

但情况似乎并非如此，因为在调试工具包中我检查了 SQL 查询，发现 Contacts 和 ContactsCustomers 之间没有 Join。

我在期待什么

因此，我期望的结果是显示具有相关客户联系人的所有客户，其中给定的搜索词与这些客户联系人的名字或姓氏有关。

这种方法可以实现吗？还是我必须采用另一种方法？

Answer 1

我终于通过这段代码得到了我想要的东西:

// Had to remove ONLY_FULL_GROUP_BY from sql_mode so the distinct works
$query = $this->Customers->find( 'all' )
                         ->distinct( ['id'] )
                         ->contain( ['CustomersContacts'] );

// Check all the "matching" CustomersContacts if they have something in common with the given search parameter
// We have to use matching() instead of where() because we have a 1:N connection between Customers and CustomersContacts
$query->matching( 'CustomersContacts', function( $q ) use ( $search ) {
  /**
    * @var Query $q
    */
  return $q->where( function( $exp, $q ) use ( $search ) {
    /**
      * @var QueryExpression $exp
      * @var Query $q
      */

    $conc1 = $q->func()
                ->concat( [
                            'Customers.first_name' => 'literal',
                            ' ',
                            'Customers.last_name' => 'literal'
                          ] );
    $conc2 = $q->func()
                ->concat( [
                            'Customers.last_name' => 'literal',
                            ' ',
                            'Customers.first_name' => 'literal'
                          ] );

    // Concatenated CustomersContacts Name
    $conc3 = $q->func()
                ->concat( [
                            'CustomersContacts.first_name' => 'literal',
                            ' ',
                            'CustomersContacts.last_name' => 'literal'
                          ] );
    $conc4 = $q->func()
                ->concat( [
                            'CustomersContacts.last_name' => 'literal',
                            ' ',
                            'CustomersContacts.first_name' => 'literal'
                          ] );

    return $exp->or( [
                        'Customers.company LIKE' => "%$search%",
                        'Customers.first_name LIKE' => "%$search%",
                        'Customers.last_name LIKE' => "%$search%",
                        'Customers.city LIKE' => "%$search%",
                        'CustomersContacts.first_name LIKE' => "%$search%",
                        'CustomersContacts.last_name LIKE' => "%$search%"
                      ] )
                ->like( $conc1, "%$search%" )
                ->like( $conc2, "%$search%" )
                ->like( $conc3, "%$search%" )
                ->like( $conc4, "%$search%" );
  } );
} );

再次感谢 Greg Schmidt 关于匹配的通知