Autor Zpráva
JardaB
Profil
Zdravím potřebuji nějak formulovat následující dotaz do mysql, pokud to jde..

v tabulce 'table' jsou sloupce 'key' a 'text'. Potřebuji dle $string najít 5 adekvátních výsledků v porovnání s 'key' a těchto 5 výsledků dále prohledat zda obsahují nebo neobsahují shodu $string v sloupci 'text'. Potřebuji vrátit ne jen 5 výsledků seřazených dle nelepší shody 'key', ale také u každého hodnotu, zda se podařilo řetězec nebo shodu najít v text.

Snad jsem to napsal srozumitelně.. díky za reakce, prvně prosím než začnete cokoliv psát, prosím o zamyšlení nad dotazem...
juriad
Profil
co je "adekvátní výsledek"?
co znamená "seřazených dle nelepší shody"?

Pokud to dokážeš dostatečně přesně definovat a bude ta definice aplikovatelná v jazyce SQL, tak to snad půjde.
Zkus se víc rozepsat a uvést nějaké příklady.
JardaB
Profil
Jednoduše řečeno 5 výsledů, které nejlépe vyhovují hledanému řetězci (podobnost řetězce)... tedy hledám např. slovo televize a to musí být obsaženo v sloupci key (může být obsaženo ve více než sto řádích) a může nebo nemusí být obsaženo v sloupci text. Kdy těchto 5 výsledků potřebuji vrátit od nejvyhovujícího dle sloupce key a s hodnotou, zda je slovo televize obsaženo ve sloupci text. Rád bych k tomu použil fulltextové vyhledávání.

Jde o to, že slovo televize může být obsaženo v tisíce řádcích sloupce text.. chci tedy laicky řečeno vymezit výběr na pět hodnot dle sloupce key a teprve ty porovnat na klíčové slovo dle jejich textové části sloupce text. A vrátit 5 hodnot, kde bude i hodnota zda je hledaný výraz i ve sloupci text.

Chci si takhle vytvořit jednoduchou kategorizaci zboží, kdy mám v sloupci key, klíčová slova jednotlivých kategorií a v sloupci text bližší charakteristiku dané kategorie obsahující další klíčová slova a přívlastky. Výsledkem dotazu by mělo být 5 možných výsledků kategorií produktu. Nečekám 100% úspěšnost.
peta
Profil
vaha 1: `key` = 'hledany text'
vaha 2: `text` LIKE '%hledany text%'

SELECT * FROM tabulka WHERE `key` = 'hledany text' LIMIT 5
UNION
SELECT * FROM tabulka WHERE `text`LIKE '%hledany text%' LIMIT 5
Necham nekoho sikovnejsiho, at z toho udela lepsi dotaz.
JardaB
Profil
peta:

Pokud to dobře chápu, takt první řádek zajistí těch 5 hodntot dle key a druhý řádek těchto 5 hodnot ještě prohledá dle text? V druhém řádků by LIMIT už být nemusel, ne? Protože nemůže vrátit více jak těch 5 hodntot, které jsou limitované prvním řádkem.
juriad
Profil
JardaB:
Peta zase blábolí. Jeho select vrátí 5 řádků, jejichž key se přesně shoduje a dalších 5 řádků, jejichž text obsahuje podřetězec; select vrátí množinu řádků, tedy jich může být 5 až 10.

Použití fulltextového vyhledávání má některé, často nepěkné důsledky na schéma databáze (tabulka musí byt MyISAM).

SELECT *, (text LIKE '%hledany text%') AS obsahuje FROM tabulka WHERE key = 'hledany text' ORDER BY obsahuje DESC LIMIT 5
Vrátí 5 řádků takových, že jejich key se přesně shoduje s hledaným textem a ty setřídí podle toho, zda text obsahuje hledaný text.

Nějak mi není jasné, jak by vypadala taková tabulka (jaká obsahuje data). Obávám se, že naše dotazy dělají něco úplně jiného a moc si nerozumíme.
Zkus napsat nějakou demonstraci, například pomocí termínů: televize, lcd, plasma, projektor, monitor.
peta
Profil
2 selecty, kazdy vyhleda prvnich 5 radku podle kazdeho kriteria. cili dohromady max 10, min 0. Nekdo sikovnejsi ti to bude jiste umet napsat jako 1 dotaz, ktery vrati max 5 a min 0.
juriad: To ty ses uplne mimo :) televize muze, ale nemusi byt v `key`
Ale to mi pripomina, ze ten druhy dotaz je chybne
SELECT * FROM tabulka WHERE `key` <> 'hledany text' AND `text`LIKE '%hledany text%' LIMIT 5
JardaB
Profil
juriad:
Ono je to krapánek složitější, něž jsem zde uvedl, ale to není podstatné. Ty hledané řetězce jsou ve skutečnosti dva. První z nich prohledá key, vrátí skupinu shod a druhý řetězec z vrácených pěti výsledků prohledá samotný text těch výsledků.

Př.:
key - obsahuje slova (Bytové doplňky)
text - obsahuje slova (byt,bytový,doplňky,vybavení bytu,bytové...)

key chci porovat se $string1, který často odpovídá dané kategorizaci, tedy (Bytové doplňky), chci dostat 5 řádků které tomu odpovídají. Může se totiž stát, že bude kategorie (Pracovní doplňky, Oděvní doplňky).

těcho 5 získaných řádků chci dále prohledat dle $sting2 v sloupci text, kde tento řetězec již obsahuje jednotlivá klíčová slova, která by měla zaručit správnou volbu kategorie produktu z pěti, které se prohledaly dle key. těžko bude např. v bytových doplňcích slovo pracovní, ochranné atd.

Jednoduše řečeno, první fází dostanu několik hrubých odhadů, které zpřesním dalším prohledáním výsledku. Vylučujte to tedy použití klasického LIKE, ale asi MATCH a AGAINST
JardaB
Profil
Tak koukám že už nikdo žádný nápad? V zásadě mi jde jen strukturu dotazu dle popisu, jeho syntaxi si udělám sám. Tedy potřebuji najít z celé tabulky 5 záznamů dle sloupce key a teprve tyhle záznamy prohledat dle sloupce txt.

...WHERE key LIKE $string AND txt LIKE $string1 LIMIT 5 ..
je logicky nesmysl, najde sice 5 výsledků, ale v txt výraz musí být, což nepotřebuji v mém případě tam být nemusí

...WHERE key LIKE $string OR txt LIKE $string1 LIMIT 5 ..
taky kravina, páč to prohledá naprosto celou tabulku na sloupec txt, já potřebuji hledat jen v těch 5 záznamech

...WHERE key LIKE $string AND (key LIKE $string OR txt LIKE $string1) LIMIT 5 ..
Fungovalo by tohle dle toho zadání??
ts_istudio
Profil
JardaB:
a těchto 5 výsledků dále prohledat zda obsahují

Nehledáš něco jako toto?
SELECT * FROM (SELECT FROM * WHERE ...) WHERE ...
juriad
Profil
JardaB:
Mě přijde, že na to jdeš špatně:
Ty hledáš "bytový doplněk"; ze stovky klíčů, které obsahují slovo "doplněk", vybereš 5 více-méně náhodných a doufáš, že v popisu bude i to slovo "bytový". Nebylo by lepší hledat fulltextově jak v klíčích, tak v textu, příčemž shoda v klíči by měla mnohem větší váhu (třeba 3x)?

http://stackoverflow.com/questions/4767145/give-some-fields-more-relevance-and-sort-by-relevance-in-mysql-full-text-search
SELECT *,
MATCH (key) AGAINST ('hledany text') AS rel_key,
MATCH (text) AGAINST ('hledany text') AS rel_text,
(rel_key*3)+(rel_text) AS rel_total
FROM tabulka
WHERE MATCH (key,text) AGAINST ('hledany text')
ORDER BY rel_total

Limit bych do SELECTu nedával, můžeš klidně načíst v PHP jen pár řádků a zbytek query zahodit až ti přijde, že relevance klesla příliš.
Pokud trváš na tom, že se napřed vybere 5 podle klíče a ty se budou třídit podle textu, lze ten dotaz tak přepsat.
Přítomnost "hledaného textu" ve sloupečku text poznáš podle nenulovosti rel_text.
JardaB
Profil
juriad:

Sloupec key obsahuje klíčová slova stromové struktury kategorií, tedy je předpoklad, že výsledek bude mít max 5 řádků, čímž zúžím danou množinu, kterou dále prohledám v textové dle části dle text a určím výsledek. Dle mě je to zcela logický postup. data klientů mají v klíčovém řetězci svou logiku a moje klíčová slova jsou na tom logicky postavena... Ale logicky na základě jen key to přesně nikdy neurčím.

To co jsi napsal bude vracet i položky, které nevyhovují key, byť s menší váhou? Jestli ano, tak to je přesně to co nechci, to si umím napsat i sám. Limit bych rád použil pokud to zrychlí dotazy, protože se zpracovává třeba 20 tis produktů.


ts_istudio:

Ánoo tohle vypadá logicky jako to co hledám... pokud je takto možné dotaz zapsat?

JardaB:
„a těchto 5 výsledků dále prohledat zda obsahují“

Nehledáš něco jako toto?


SELECT * FROM (SELECT FROM * WHERE ...) WHERE ...
juriad
Profil
JardaB:
SELECT *,
MATCH (text) AGAINST ('hledany text') AS rel_text
FROM (SELECT *,
FROM tabulka
WHERE MATCH (key) AGAINST ('hledany text')
LIMIT 5)
ORDER BY rel_text DESC

Vnitřní SELECT vybere jen prvních 5 podle key a výsledek se seřadí podle rel_text (nulovost rel_text značí, že "hledany text" nebyl ve sloupci text nalezen)

OK, pokud tam máš logiku, tak předchozí select by měl fungovat. Jen mi to přijde jako dost nešikovné řešení, pokud váhu zvýšíš 10x, tak se bude rozhodovat téměř čistě podle key a jen pokud v key nenalezne shodu, tak půjde podle textu - větší šance, že alespoň nějak produkt zařadíš.

FULLTEXT musí (nemusí, ale pak je rychlejší i moje babička) mít INDEX, a pokud jsou data v indexu, moc nezáleží na tom, kolik jich je.
JardaB
Profil
juriad:
Mě to nepřijde nešikovné... není podmínkou abych dostal výsledek vždy. Tedy ani na key nemusím dostat výsledek. Což bude znamení, že nemám takovou kategorii nebo data klienta postrádají doporučenou logiku. V takovém případě produkt nekategorizuji a jen dávám k dispozici ve fulltextu.
Přesně o to jde... pokud najdu shodu v key, tak teprve budu hledat v textu... protože kdybych nenašel shodu v key a dále se snažil hledat nějaký výsledek v textu, byly by výsledky už šíleně nepřesné, to bych mohl rovnou prohledávat už jen v tom textu.
peta
Profil
Ja to chapu tak, ze ma treba
kat: televize
prod: philips
kat: kabelaz
prod: JACK/CINCH kabely k televizi
Cili, televize je obsazena v kategorii nebo v popisu produktu. Muze a nemusi byt tam i tam.
Ted je samozrejme otazka presneho ucelu. Jestli tam treba dat random nebo nechat nalezt prvnich 5. U tech mych dotazu spojenych UNION proste vemu 0-5 a 0-5 z obou. Bude to serazene tak, ze prvnich 0-5 bude vzdy vaha1 a dalsich -50 bude vaha2. To najde treba 7. a pomoci php bych prvnich 5 vypsal.
juriadovo reseni #11 s nasobeni tremi je zajimave, ale to by potlacilo vyznam kategorie, kdyz by na konec napsal LIMIT 5. To by pak naslo treba 3 z kategorie, 2 v textu a pritom v kategorii by bylo jeste dalsich 30 vyrobku. Spravne by se tedy melo zobrazit 5 televizi a zadna kabelaz.
JardaB
Profil
ano takhle nějak to je...

zkusím otestovat vše co tu bylo nějak naznačeno a uvidí se.... Jen pořád nevím, proč všem nějak nesedí má úvaha o zpřesňování výsledku určením hrubé množiny a její následující prohledání. Ve finále potřebuji stejně jeden jediný výsledek který musí být produktu přiřazen. Ta váha je ale zajímavá... protože když najdu shodu jen na key a již ne v textu, tak bych hodnotu váhy zapsal k produktu... tedy by se tyto produkty v dané kategorii zobrazovaly na konci.. kde už by případně mylná kategorizace nebyla tak tristní.
JardaB
Profil
Tak moje řešení funguje a hledání je cca na 90% úspěšnosti...

Chtěl jsem otestovat ještě následující, ale nějak ten dotaz namůžu dostat do validní formy, vidí tam někdo zjevně problém v zápisu? Nebo je vůbec možné dotaz takhle napsat?

SELECT *,
MATCH (key) AGAINST ('hledany text') AS rel_key,
MATCH (text) AGAINST ('hledany text') AS rel_text,
(rel_key*3)+(rel_text) AS rel_total
FROM tabulka
WHERE MATCH (key,text) AGAINST ('hledany text')


Také jsem chtěl otestovat tohle, ale hlásí to problém s aliasem. Dá se to nějak ošetřit?

SELECT * FROM (SELECT FROM * WHERE ...) WHERE ...
ORDER BY rel_total
juriad
Profil
JardaB:
Omlouvám se za zmatek, funkční SQL je:
SELECT *,
MATCH (`key`) AGAINST ('hledany text') AS rel_key,
MATCH (text) AGAINST ('hledany text') AS rel_text,
(MATCH (`key`) AGAINST ('hledany text'))*3+(MATCH (text) AGAINST ('hledany text')) AS rel_total
FROM tabulka
WHERE MATCH (`key`,text) AGAINST ('hledany text')
ORDER BY rel_total

Měl jsem tam dvě chyby:
1) sloupec se nesmí jmenovat key, to je rezervované slovo, proto musí být escapovaný; obdobně je to s text, ale to ještě MySQL z důvodu zpětné kompatibility zkousne.
2) na alias se můžu odvolávat jen z GROUP BY, HAVING a ORDER BY, takže je třeba celou definici zopakovat
JardaB
Profil
juriad:
.. :D no jasně.. někdy kravina a člověka to nenapadne.. Díky, vyzkouším a dám vědět, které řešení mělo větší úspěch...


ještě mi ten tvůj zápis hlásí
#1191 - Can't find FULLTEXT index matching the column list
Kajman
Profil
Musíte si oba indexy vytvořit. Klikněte si na against a tam to je popsáno.
JardaB
Profil
Jasně díky, nestačí mít indexy jen na (key) , (text) jak jsem měl, ale i (key,text), teď to funguje...
juriad
Profil
JardaB:
Pak ty dotazy porovnej a pověz, který dává lepší výsledky a jestli dotaz není příliš pomalý. Samotného mě to zajímá.
JardaB
Profil
To tvoje řešení vypadá hodně zajímavě... zkusím to nasadit do kódu a uvidíme výsledky u cca 100 tis produktů. Jinak dotaz cca 0.0012 sec., kde tabulka s kategorizačními klíči má má zatím cca 900 řádků, kdy ve finále bude mít kolem 2 tis, to se potom uvidí...

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: