Как искать значение в поле БД, содержащем список значений?

Надеюсь, я правильно задаю этот вопрос. Я использую ColdFusion 10, и у меня есть таблица mySQL с именем companies. В таблице есть поле с именем stateList. Он содержит разделенный запятыми список идентификаторов состояний, в которых у компании есть магазины.

У меня есть форма поиска, в которой пользователь может выбрать штат, чтобы найти компании в этом штате. Мне нужно передать это form.state_ID поисковому запросу, чтобы увидеть, содержит ли companies.stateList form.state_ID.

Единственный код, который я пробовал, который возвращал какие-либо результаты, но не все они были правильными, был

<cfquery name="searchCompanies" datasource="#businessDSN#">
  SELECT company_id, company_name, stateList
  FROM companies
  WHERE stateList LIKE "%#form.state_ID#"
</cfquery>

Если я искал Аризона, state_ID 4, вышеуказанный запрос вернул любую компанию, у которой было 4, 14, 24, 34 и 44 в ее stateList.

Возможно ли то, что я хочу делать? Спасибо за любую предоставленную помощь.


mysql coldfusion coldfusion-10
person RobK    schedule 23.10.2013    source источник

Ответы (2)


arrow_upward
6
arrow_downward

... есть поле с именем stateList. Он содержит список идентификаторов состояний, разделенных запятыми ...

О, Боже, почему? Если можете, немедленно измените это на правильную таблицу отношений!

Но пока ...

<cfquery name="searchCompanies" datasource="#businessDSN#">
  SELECT company_id, company_name, stateList
  FROM companies
  WHERE CONCAT(',', stateList, ',') 
        LIKE
        CONCAT('%,', <cfqueryparam value="#form.state_ID#" cfsqltype="CF_SQL_VARCHAR">, ',%')
</cfquery>

И пожалуйста используйте <cfqueryparam> вместо того, чтобы отбрасывать непроверенный ввод пользователя в строку SQL.

person Tomalak    schedule 23.10.2013
comment
+100. То же касается реструктуризации. Как вы обнаружили, запрашивать списки неудобно, не говоря уже о медленном и подверженном ошибкам. Поскольку вы должны прибегать к сравнению строк, даже одно различие в пространстве может привести к тому, что запросы будут возвращать неверные результаты. Более надежная структура - хранить отношения в отдельной таблице: столбцы CompanyState: Company_id, State_id. - person Leigh; 23.10.2013
comment
Спасибо за ответ и напоминание об использовании cfqueryparam. Тем не менее, он по-прежнему возвращает те же результаты, что и мой исходный код. Все идентификаторы в списке с 4, 14, 24, 34, 40-49 возвращаются. - person RobK; 23.10.2013
comment
Предоставленный Tomalak код не вернет все идентификаторы с цифрой 4 - обратите особое внимание на то, что делается с запятыми. - person Peter Boughton; 23.10.2013
comment
Я взял на себя этот беспорядок на прошлой неделе и уже внес некоторые другие изменения в базу данных. Я создам таблицу company_states и буду ее использовать. Спасибо за помощь!!! - person RobK; 23.10.2013
comment
@RobK - К вашему сведению: приведенный выше запрос работает нормально. Похоже, что изменения не были применены (или, может быть, это просто проблема с кешированием). - person Leigh; 23.10.2013
comment
@Leigh - Вы правы. Я пропустил запятую после первого%. Спасибо Томалаку и Ли за вашу помощь. Я все еще исправлю это как следует. - person RobK; 23.10.2013
comment
Я не работаю с mysql, поэтому точно не знаю, как работает эта функция. Однако похоже, что запрос будет работать только в том случае, если элементы is расположены в том же порядке. Другими словами, если statelist был 3,1,4, а form.state_id был 1,3,4, вернет ли запрос ожидаемые записи? - person Dan Bracuk; 23.10.2013
comment
@DanBracuk - он ищет только один state_id, поэтому проблема не возникает. - person Leigh; 23.10.2013
comment
Если нужно было проверить список значений, ту же логику можно было бы переместить внутри цикла (например, <cfloop index="CurState" list=#form.state_id# >) с изменением WHERE на AND / OR (в зависимости от того, нужен ли весь список или какое-либо совпадение). - person Peter Boughton; 23.10.2013
comment
@Peter, на самом деле, ColdFusion изначально поддерживает списки в cfqueryparam (для использования в SQL IN), но, к сожалению, здесь это не работает. - person Tomalak; 23.10.2013
comment
@RobK - Re: Я все равно исправлю это правильно Да, нормализация - определенно правильный путь. Я просто хотел отметить, что приведенный выше подход действительно работает ... на случай (не дай бог) вам когда-нибудь снова придется работать со списками ;-) - person Leigh; 24.10.2013
comment
Да, я знаю об атрибуте list. Я отвечал на комментарий Дэна относительно сравнения списков с обеих сторон. - person Peter Boughton; 24.10.2013

arrow_upward
2
arrow_downward

Если вы используете MySQL, вы можете использовать FIND_IN_SET ... например:

<cfquery name="searchCompanies" datasource="#businessDSN#">
  SELECT company_id, company_name, stateList
  FROM companies
  WHERE FIND_IN_SET(<cfqueryparam value="#form.state_ID#" cfsqltype="CF_SQL_VARCHAR">,stateList)
</cfquery>

Я не уверен, что запрос быстрее, чем у Томалака.

И да, нормализуйте эти данные !!

person Vernons    schedule 25.10.2013
comment
Да, если вы должны это сделать, это еще одна хорошая техника. Я предполагаю, что производительность двух запросов будет аналогичной. Ни то, ни другое не будет очень эффективным, поскольку, как только вы начнете использовать функции или like, это будет мешать базе данных использовать индексы. Еще одна причина избегать списков :) - person Leigh; 25.10.2013