VPF::FTS наоборот - Форум программистов

tishaishii

Дата 28.12.2009, 17:34 (ссылка)

(нет голосов)

Загрузка ...

Создатель

Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8

Есть таблица баннеров:

Код


create table firm_banner(
   id_firm_banner,  -- ключ
   keywords,  -- ключевые слова, по которым баннер надо показывать
   PRIMARY KEY (`id_firm_banner`)
) ENGINE = MyISAM;

CREATE FULLTEXT INDEX `firm_banner_index02`
  ON `firm_banner`
  (`keywords`);

Каждая запись keywords - это набор из отдельных слов и словосочетаний.
Например: баннер надо показывать, когда в запросе пользователя было слово "КИРПИЧ" или набор из слов "САХАР, РАФИНАД".
Т. е., в таком случае, при запросе пользователя "САХАР" или "САХАР-ПЕСОК" соответствия для данной записи не будет.
А если спросят "КИРПИЧИ", или "КИРПИЧИ И САХАР", или "РАФИНАД-САХАР" - то такому запросу будет соответствовать приведённая запись.

Вопрос по алгоритму реализации...

По смыслу подходит полнотекстовый поиск по пользовательскому запросу запросом из ключевых слов баннера, вроде:

Код


select * from match(<пользовательский запрос>) against(keywords in boolean mode)
/*
 К примеру, одна из записей keywords='(+САХАР +РАФИНАД) (+КИРПИЧ)', а <пользователский запрос>='САХАР-ПЕСОК'
*/

Но в match() должно быть поле таблицы, а в against() - переменная или константа - выражение или столбец не подходят для движка FTS (???).

У меня получилось такое вот корявое решение:

Код


CREATE TABLE `firm_banner_tmp` (
  `idt`             char(36) NOT NULL,
  `id_firm_banner`  bigint UNSIGNED NOT NULL,
  /* Keys */
  PRIMARY KEY (`idt`, `id_firm_banner`)
) ENGINE = InnoDB
  ROW_FORMAT = DYNAMIC;

CREATE INDEX `Index_2`
  ON `firm_banner_tmp`
  (`idt`);

CREATE INDEX `Index_3`
  ON `firm_banner_tmp`
  (`id_firm_banner`);
-- Table: firm_banner_tmp2

-- DROP TABLE IF EXISTS `firm_banner_tmp2`;

CREATE TABLE `firm_banner_tmp2` (
  `idt`    char(36) NOT NULL,
  `query`  varchar(1024) NOT NULL,
  /* Keys */
  PRIMARY KEY (`idt`)
) ENGINE = MyISAM
  ROW_FORMAT = DYNAMIC;

CREATE FULLTEXT INDEX `Index_2`
  ON `firm_banner_tmp2`
  (`idt`, `query`);

Код


DELIMITER |

CREATE DEFINER = 'tishaishii'@'%' PROCEDURE `fit_banners` 
(
  IN `in_query` varchar(1024)
)
  DETERMINISTIC
BEGIN
     declare `v_kw` varchar(1024);
     declare v_id bigint(20);
     declare v_idt  char(36) default uuid();
     declare done boolean default false;
     declare c cursor for select keywords, id_firm_banner from firm_banner;
     declare continue handler for not found set done=true;

     insert into firm_banner_tmp2(idt, `query`) values(v_idt, in_query);

     set done=false;
     open c;
     while not done do
           fetch c into `v_kw`, v_id;
           insert into
                  `firm_banner_tmp`(
                     idt,
                     id_firm_banner
                  )
           select
               v_idt,
               v_id
           from
               firm_banner_tmp2
           where
               match(`query`) against(`v_kw` in boolean mode) and
               idt=v_idt;
     end while;
     close c;

     select
         fb.*
     from
         `firm_banner_tmp` fbt
         
         inner join firm_banner fb on
         fbt.id_firm_banner=fb.id_firm_banner
     where
         idt=v_idt;
     delete from `firm_banner_tmp` where idt=v_idt;
     delete from `firm_banner_tmp2` where idt=v_idt;
END|

DELIMITER ;

Т.е. существуют две таблицы "на подхвате", которые заполняются и чистятся каждый вызов процедуры.
Есть предложения как выровнять решение?

Это сообщение отредактировал(а) tishaishii - 28.12.2009, 17:35

Veve

Дата 28.12.2009, 18:54 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 291
Регистрация: 1.1.2005
Где: Латвия, Рига

Репутация: 3
Всего: 13

Честно говоря ничего не понял. Приведи пример набора данных в таблице, запрос пользователя и выходной результат, который должен получиться.

tishaishii

Дата 31.12.2009, 12:36 (ссылка)

(нет голосов)

Загрузка ...

Создатель

Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8

Код


insert into firm_banner(
    id_firm_banner,
    keywords
) values (
   1,
   '(+САХАР +РАФИНАД) (+КИРПИЧ)'
), (
   2,
   '(+САХАР)
), (
   3,
   '(+САХАР +ПЕСОК)
);

Код

call fit_banners('САХАР-ПЕСОК');
/*
Результат:
2      (+САХАР)
3      (+САХАР +ПЕСОК)
*/

Код

call fit_banners('САХАР');
/*
Результат:
2      (+САХАР)
*/

Код

call fit_banners('КРАСНЫЙ КИРПИЧ');
/*
Результат:
1      (+САХАР +РАФИНАД) (+КИРПИЧ)
*/

Т. е. если в ключевых словах к слову есть уточнения, то баннер показывается только если в запросе пользователя есть эти уточнения. А коли уточнений в ключевых словах нет, то показывать всегда.

tishaishii

Дата 31.12.2009, 13:21 (ссылка)

(нет голосов)

Загрузка ...

Создатель

Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8

Смысл. Для размещения баннера на сайте клиенту предлагается два варианта ключевых слов, по которым они будут показываться:
1. По одному слову (стоимость слова=100 тугриков).
2. По набору слов (стоимость каждого набора слов=10 тугриков).

Veve

Дата 31.12.2009, 14:14 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 291
Регистрация: 1.1.2005
Где: Латвия, Рига

Репутация: 3
Всего: 13

Опять же не особо стало понятнее.., но немного стало smile

Покрайней мере хочеться верить smile

Почему нельзя сделать так:

Код


CREATE TABLE `firm_banner` (
  `id_firm_banner` char(36) CHARACTER SET utf8 NOT NULL,
  `keywords` varchar(100) CHARACTER SET utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert  into `firm_banner`(`id_firm_banner`,`keywords`) values ('1','САХАР,РАФИНАД'),('2','САХАР'),('3','САХАР,ПЕСОК'),('1','КИРПИЧ');

И соответственно запросы:

Код


SELECT * FROM firm_banner WHERE  LOCATE(keywords,'САХАР,ПЕСОК')>0

Результат: 2 и 3;

Код


SELECT * FROM firm_banner WHERE  LOCATE(keywords,'САХАР')>0

Результат: 2;

Код


SELECT * FROM firm_banner WHERE  LOCATE(keywords,'КРАСНЫЙ КИРПИЧ')>0

Результат: 1;

tishaishii

Дата 11.1.2010, 15:46 (ссылка)

(нет голосов)

Загрузка ...

Создатель

Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8

1. locate не всегда использует индексы.
2. Вариант

Код

SELECT * FROM firm_banner WHERE LOCATE(keywords,'САХАР,ПЕСОК')>0

не выдаст баннеры с ключевыми словами 'САХАР', где не указано слово 'ПЕСОК'.

Допустим, человек заплатил за то, чтобы его баннер показывался всегда при слове 'САХАР' в запросе. А другой - за то, чтобы его баннер показывался только, если в запросе били и 'САХАР' и 'ПЕСОК'. Тогда при запросе 'САХАР-ПЕСОК' показываем и 1го и 2го, а при запросе 'САХАР' - только первого.

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MySQL \| Следующая тема »