时间戳

首页 > 解决方案 > CakePHP4：连接表上的连接位置未按预期工作

php - CakePHP4：连接表上的连接位置未按预期工作

问题描述

我得到了以下场景。

软件：当前 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;

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

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

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

搜索实现

第一步是实现搜索，我不仅可以搜索单列值，还可以搜索连接列，如“firstname lastname”或“lastname firstname”

这有效

我确实在我的索引函数中使用以下代码完成了这一点

$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'

我真的不明白，因为客户和客户联系人之间的加入应该发生在

->contain( ['CustomersContacts'] );

但这似乎并非如此，因为在 Debug Kit 中我检查了 SQL Query，Contacts 和 ContactsCustomers 之间没有 Join。

我在期待什么

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

这种方法是否可行，还是我必须采用另一种方法？

标签： phpmysqlsearchcakephp

解决方案

我终于用这段代码得到了我想要的东西：

// 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 关于matching

推荐阅读