I have a table with articles. Articles can have one or more tags. I'd like to retrieve simular articles based on their tags.
My table structure is very simple. It basically just a many to many relation between Articles and Tags
abstract class BaseArticle extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('article');
$this->hasColumn('id', 'integer', 8, array(
'type' => 'integer',
'length' => 8,
'primary' => true,
'autoincrement' => true,
));
$this->hasColumn('title', 'string', null, array(
'type' => 'string'
));
}
public function setUp()
{
$this->hasMany('Articletag', array(
'local' => 'id',
'foreign' => 'article_id',
'cascade'=>array('delete')));
$this->hasMany('Dbtag as Dbtags', array('refClass'=&g开发者_开发知识库t;'Articletag',
'local' => 'article_id',
'foreign'=>'tag_id'
)
);
}
}
abstract class BaseDbtag extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('dbtag');
$this->hasColumn('id', 'integer', 4, array(
'type' => 'integer',
'length' => 4,
'fixed' => false,
'unsigned' => false,
'primary' => true,
'autoincrement' => true,
));
$this->hasColumn('name', 'string', 255, array(
'type' => 'string',
'length' => 255,
'fixed' => false,
'unsigned' => false,
'primary' => false,
'notnull' => false,
'autoincrement' => false,
));
}
public function setUp()
{
parent::setUp();
$this->hasMany('Articletag', array(
'local' => 'id',
'foreign' => 'tag_id'));
}
}
abstract class BaseArticletag extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('articletag');
$this->hasColumn('article_id', 'integer', 8, array(
'type' => 'integer',
'length' => 8,
'fixed' => false,
'unsigned' => false,
'primary' => true,
'autoincrement' => false,
));
$this->hasColumn('tag_id', 'integer', 4, array(
'type' => 'integer',
'length' => 4,
'fixed' => false,
'unsigned' => false,
'primary' => true,
'autoincrement' => false,
));
}
public function setUp()
{
parent::setUp();
$this->hasOne('Article', array(
'local' => 'article_id',
'foreign' => 'id'));
$this->hasOne('Dbtag', array(
'local' => 'tag_id',
'foreign' => 'id'));
}
}
What i need is to retrieve similar articles based on tag set. I want to get say 10 articles sorted by count of matched tags.
A similar problem (with movies instead of articles) is solved in native SQL here.
What is the best way to do this in Doctrine using Doctrine_Query without using native SQL?
精彩评论