Я пытаюсь перенести поиск продукта из Coldfusion в хранимую процедуру MySQL.
Поиск имеет около 20 критериев и является ванильной функцией сайта, на котором он находится, поэтому хотел преобразовать его в хранимую процедуру, поскольку необходимо выполнить много предварительной компиляции.
Кажется, я разобрался с большинством критериев поиска. Я борюсь с последним.
Первая часть Coldfusion:
<cfquery datasource="dtb" name="get_pricelists">
SELECT sid, pricelist
FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
WHERE b.bid = >parameter<
</cfquery>
Это выбирает всех продавцов, использующих прайс-листы и соответствующий прайс-лист для текущего пользователя. Выглядит так (в MySQL я создаю временную таблицу):
=====================
sellerID pricelist
12345 NULL
23467 foo
99999 bar
В следующей части я борюсь:
<cfset misterLister = "LEFT JOIN preislisten p ON ">
<cfoutput query="get_pricelists" >
<cfif pricelist IS ''>
<cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
<cfelse>
<cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "#pricelist#" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
</cfif>
</cfoutput>
<cfset misterLister = misterLister & "(1=0)">
Таким образом, в приведенном выше примере это будет циклически проходить по 3 найденным продавцам и прайс-листам, чтобы создать этот синтаксис MySQL в Coldfusion:
LEFT JOIN pricelists p ON
(p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = 12345 ) OR
(p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR
(p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR
(1=0)
который затем просто передается в фактический запрос.
Вопрос:
Я могу сделать первую часть и сохранить ее во временной таблице. Но можно ли также создать вторую часть в MySQL, то есть перебрать результаты временной таблицы и построить из нее приведенный выше оператор?
Я все еще новичок в MySQL, поэтому не знаю, с чего начать. Я смотрю на подготовленные операторы и курсор, но это единственные варианты?
EDIT:
Хорошо. Я попытался придумать свое первое подготовленное заявление. Выглядит так:
SET @sql_text := '
DECLARE strCount INT DEFAULT 1;
SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
WHERE b.bid = ?
SET @string = "LEFT JOIN preislisten AS p";
lj:
LOOP
SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");
SET strCount = strCount+1;
IF strCount = recs
THEN LEAVE lj;
END IF;
END LOOP lj;
SET @string = CONCAT( @string,"(1=0)")
';
SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;
Итак, я делаю начальный запрос внутри строки оператора подготовки, а затем надеялся, что смогу перебрать найденные прайс-листы (это будет более 3 продавцов и прайс-листов, поэтому мне нужно пройти через цикл, не так ли?). Соединяю все вместе. Но если бы это сработало, как бы я добавил эту строку в мой фактический поисковый запрос, который будет выглядеть так:
SELECT articles AS art
<< insert left join here >>
FROM bigtable AS bt
WHERE
a lot of other criteria
Я потерялся...