cakephp v2 - 使用名称字段查找重复项 [英] cakephp v2 - Finding Duplicates using name field

问题描述

我的Sql技能是缺乏，将真正感谢帮助。什么是最有效的方式来拉取字段相同的所有记录？

例如，我的表名称是games，并且与表'console '

如何使用相同的（重复的）名称获取所有游戏记录，保留关联并访问所有字段。

基本上修改为只显示重复项：

  $ this-> set '，$ this-> Game-> find（'all'，array（'order'=> array（'Game.name'=>'asc'））））; 
  

有一个简单的方法来做这个使用蛋糕或如何通过sql使用query ）

非常感谢！

注意，
Ash

解决方案

试图使用Konerak查询，它只工作，但只有在小表，在我的测试基础与2k记录它崩溃对sql客户端，并给出一个内存错误on php（我使用非索引字段进行测试）。如果它适用于您：

  $ options = array（）; 
 $ options ['joins'] [0] ['table'] ='games'; 
 $ options ['joins'] [0] ['alias'] ='Game2'; 
 $ options ['joins'] [0] ['conditions'] = array（1 => 1）; 
 $ options ['conditions'] ='Game.name = Game2.name'; 
 $ options ['conditions'] ['NOT'] ='Game.id = Game2.id'; 
 $ options ['order'] ='Game.name ASC'; 
 
 // $ this-> Game-> contain（）; 
 $ this-> set（'games'，$ this-> Game-> find（'all'，$ options））; 
  

/ *

但我设法测试它只是很好：

  $ options = array（）; 
 $ options ['fields'] ='Game.name'; 
 $ options ['group'] ='Game.name HAVING COUNT（*）> 1'; 
 
 //获取所有名称> 1 
 // $ this-> Game-> contain（）; 
 $ regs = $ this-> Game-> find（'all'，$ options）; 
 
 //使用一个很好的数组
 $ names = array（）; 
 foreach（$ regs as $ reg）{
 $ names [] = $ reg ['Game'] ['name']; 
} 
 
 //只搜索那些游戏
 // $ this-> Game-> contains（）; 
 $ this-> set（'games'，$ this-> Game-> find（'all'，array（
'conditions'=> array（'Game.name'= > $ names），
'order'=>'name ASC'）））; 
  

让我知道，如果这不会为你工作，你遇到什么问题。 >

编辑。我评论了包含行。看来你想要所有的结构，所以你不需要他们反正。

edit2。删除字段名称可能存在的歧义。

My Sql skills are lacking and would really appreciate the help. What is the most efficient way to pull all records where a field is the same?

For example my tables name is 'games' and has an association with the table 'consoles'

How do I pull all record of games with the same (duplicate) 'name', retain associations and be able to access all fields.

Basically modify this to just show duplicates:

$this->set('games', $this->Game->find('all', array('order' => array('Game.name' => 'asc'))));

Is there an easy way to do this using cake or how would I do it through sql using query()?

Much appreciated in advance!

Regards, Ash

解决方案

Tried to use Konerak query, it worked but only on small tables, on my test base with 2k records it crashed on sql client and gives a memory error on php (I used a non indexed field for testing). If it works for you:

$options = array();
$options['joins'][0]['table'] = 'games';
$options['joins'][0]['alias'] = 'Game2';
$options['joins'][0]['conditions'] = array(1 => 1);
$options['conditions'] = 'Game.name = Game2.name';
$options['conditions']['NOT'] = 'Game.id = Game2.id';
$options['order'] = 'Game.name ASC';

//$this->Game->contain();
$this->set('games', $this->Game->find('all', $options));

/*

This solutions use 2 queries but I managed to test it just fine:

$options = array();
$options['fields'] = 'Game.name';
$options['group'] = 'Game.name HAVING COUNT(*) > 1';

// get all the names > 1
//$this->Game->contain();
$regs = $this->Game->find('all', $options);

// make a nice array with them
$names = array();
foreach ($regs as $reg) {
$names[] = $reg['Game']['name'];
}

// and search for only those games
//$this->Game->contain();
$this->set('games', $this->Game->find('all', array(
                                     'conditions' => array('Game.name' => $names), 
                                     'order' => 'name ASC')));

Let me know if that won't work for you and what problems you are having.

edit. I commented the contain lines. Seems you want all the structure so you wouldn't need them anyway.

edit2. remove possible ambiguity on field names.

这篇关于cakephp v2 - 使用名称字段查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！