Ситуация:
Таблицы:
Объекты
Свойства
Справочник значений свойств
Значения свойств объектов
Нужен полноценный поиск по любому набору полей типа
Россия, 27-32 года, без детей, владеет С++ и C#, опыт не менее 2-х лет.
Сейчас я это реализовал, но как-то не слишком здорово:
| users | CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`id_usertype` int(11) NOT NULL default '3',
`login` varchar(255) NOT NULL default 'anonym',
`pw` varchar(255) NOT NULL default 'anonym',
`email` varchar(255) default NULL,
`_order` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| profilefields | CREATE TABLE `profilefields` (
`id` int(11) NOT NULL auto_increment,
`id_message` int(11) NOT NULL default '0',
`type` varchar(25) NOT NULL default 'input',
`id_group` int(11) NOT NULL default '0',
`_order` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `i_message` (`id_message`),
KEY `i_group` (`id_group`),
KEY `i_order` (`_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| profiledata | CREATE TABLE `profiledata` (
`id` int(11) NOT NULL auto_increment,
`id_user` int(11) NOT NULL default '0',
`fieldname` varchar(255) default NULL,
`value` text,
`thetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
чтобы организовать поиск по полю "возраст", сейчас рождается примерно следующее
select distinct u.id from ((profiledata as pd left join profilefields as pf on concat("f-",pf.id)=pd.fieldname) inner join users as u on u.id=pd.id_user) where (pd.fieldname="f-6" and pd.value > "10") and (pd.fieldname="f-6" and pd.value < "15")
чтобы искать по двум полям: страна и возраст — придется ДВАЖДЫ джоинить на таблицу значений полей
select distinct u.id from
((((users as u left join photos as p on u.id=p.id_user )
)
inner join (profiledata as pd1
inner join profilefields as pf1 on pd1.fieldname = concat("f-",pf1.id) and pf1.id=6) on pd1.id_user=u.id )
inner join (profiledata as pd2
inner join profilefields as pf2 on concat"f-",pf2.id)=pd2.fieldname and pf2.id=13) on pd2.id_user=u.id)
а вот код
не слишком отточено, но идея тут
// Класс "Запрос"
class request
{
var $units;
function request()
{
$this->units = array();
}
// порождает sql-запрос типа Select на выборку
function render()
{
global $global;
$table = ' '.$global['tables']['users'].' as u ';
$res = '';
foreach ($this->units as $num=>$unit)
{
$fragment = $unit->render($num);
$glue = ($res) ? ' and ' : '';
$res .= ($fragment ? $glue.$fragment : '');
$table = '('.$table.' inner join ('.$global['tables']['profiledata'].' as pd'.$num.' inner join '.$global['tables']['profilefields'].' as pf'.$num.' on concat("f-",pf'.$num.'.id)=pd'.$num.'.fieldname) on u.id=pd'.$num.'.id_user)';
}
$res = 'select distinct u.id from '.$table.($res ? (' where '.$res) : '');
// убираем парные пробелы
while (strpos($res,' ')!=false) $res = str_replace(' ',' ',$res);
return $res;
}
}
// Класс "запросная единица "
class unit
{
var $field;
var $constraint;
function unit($field,$constraint)
{
$this->field = $field;
$this->constraint = $constraint;
}
function render($num)
{
$res = '';
if ( ($this->field) && ($this->field->name) && ($this->constraint) &&
(($this->constraint->value>'') || ($this->constraint->min>'')
|| ($this->constraint->max>''))
)
{
// Есть что рендерить
$comma = ($this->field->isstring) ? '"' : '';
// значение
if ($this->constraint->type=='value')
{
// "=" или "like"
$sign = ($this->constraint->comparison == 'strong') ? ' = ' :
(($this->constraint->comparison == 'like') ? ' like ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->value.$comma.')';
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->value.$comma.')';
}
}
// интервал
if ($this->constraint->type=='interval')
{
// левая граница
if ($this->constraint->min>'')
{
// ">" или ">="
$sign = ($this->constraint->comparison == 'strong') ? ' > ' :
(($this->constraint->comparison == 'not strong') ? ' >= ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->min.$comma.')';
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->min.$comma.')';
}
}
// " and "
if ( ($this->constraint->max>'') && ($this->constraint->min>'') ) $res .= ' and ';
// правая граница
if ($this->constraint->max>'')
{
// "<" или "<="
$sign = ($this->constraint->comparison == 'strong') ? ' < ' :
(($this->constraint->comparison == 'not strong') ? ' <= ' : '');
if ($sign)
{
if ($this->field->virtual)
$res .= ' (pd'.$num.'.fieldname="'.$this->field->name.'" and pd'.$num.'.value'.$sign.' '.
$comma.$this->constraint->max.$comma.')';
else
$res .= ' (`'.$this->field->name.'` '.$sign.' '.
$comma.$this->constraint->max.$comma.')';
}
}
}
}
return $res;
}
}
class field
{
var $isstring;
var $name;
var $caption;
var $virtual; // =1 if this field was added in editor and is not physically exists in `users` table
function field($name, $isstring='1', $virtual='0')
{
$this->name = $name;
$this->isstring = $isstring;
$this->virtual = $virtual;
}
}
// ограничение.
class constraint
{
var $value;
var $min;
var $max;
// 'strong' => '<'
// 'not strong' =? '<='
// 'like' =? 'like'
var $comparison;
// тип: value или interval
var $type;
// Есть два варианта вызова конструктора
// при type=value передают одно значение
// при type=interval передают два значения: меньшее, потом большее
function constraint($type,$value1,$value2 = '',$comparison = 'strong')
{
$this->type = $type;
$this->comparison = $comparison;
if ($this->type=='value')
{
$this->value = $value1;
}
if ($this->type=='interval')
{
$this->min = $value1;
$this->max = $value2;
}
}
}
Теперь собственно вопрос. Это разрабатывается для очень посещаеемого сайта, где поиском буду пользоваться часто.
Будет примерно 200-500 полей профиля, десятки тысяч пользователей, тысячи значений справочников, частота поиска — 1 запрос в секунду.
Насколько оптимально предложенное решение (гибкость профиля — очень важна, поэтому советы хранить все в одной flat таблице — не очень разумны).
Все join — inner, все индексы — присутствуют, все, что можно — будет кешироваться.
Обновление профиля происходит на два порядка реже, чем поиск по нему
