Slozitejsi select s databaze s ref a related

Zdravim,

snazim se smontovat slozitejsi select dat nad databazi, ktery potrebuji provest nad vice tabulkami. Povedlo se mi ho uvest ± do chodu, ale spise me zajima, jestli to nejde udelat „lepe“ (optimalneji, rychleji).

V databazi ukladam domenu (informace o indexovane lokaci), linky (v podstate soubory a adresare + jejich obsah), klicova slova (veskera nalezena klicova slova v nazvu linku a v obsahu) a potom mam 16 tabulek, ktere predstavuji vazby mezi klicovimy slovy a linky. Tech tabulek mam 16, protoze predstavuji neco jako hashtable a podle hashe klicoveho slova potom volim konkretni tabulku, kde hledat, ke kterym linkum dane klicove slovo nalezi.

schema db

Timto kodem se snazim z DB dostat vysledky pro hledane(a) klicove(a) slovo(a).

   private function searchKeywords($selectedDomains) {
	//get stored search value
	$searchValues = $this->searchValue;
	//replace astering and exclamation mark (counted as characters for regular expression) and replace them by their mysql equivalent
	$searchValues = str_replace("*", "%", $searchValues);
	$searchValues = str_replace("!", "_", $searchValues);

	// empty result array to prevent previous results to interfere
	$this->resultData = array();
	//searched phrase can be multiple keywords, so split it by space and get results for each keyword
	foreach (explode(" ", $searchValues) as $keywordName) {
	    //set default link result weight to -1 (default value)
	    $weight = -1;
	    //select all keywords, which match searched keyword (or its regular expression)
	    $recordExists = $this->database->table('keywords')->where('keyword LIKE ?', $keywordName);
	    //count keywords
	    $recordExistsCount = $recordExists->count();
	    //if there are some keywords, continue
	    if ($recordExistsCount != 0) {
		//fetch all keywords from database
		$keywords = $recordExists->fetchAll();
		//loop all matched keywords
		foreach ($keywords as $keyword) {
		    $currentKeyword = $keyword->keyword;
		    //count keyword md5 sum to determine which table should be use to match it links
		    $md5 = md5($currentKeyword);
		    //get all link ids from linkkeyword relation table
		    $keywordJoinLink = $keyword->related('linkkeyword' . $md5[0]);
		    //fetch all keywordLinkIds
		    $keywordJoinLink->fetchAll();
		    //loop found links
		    foreach ($keywordJoinLink as $link) {
			//store link weight, for later result sort
			$weight = $link->weight;
			//get link ID
			$linkId = $link->linkId;
			//check if link already exists in results, to prevent duplicity
			$keyExists = array_key_exists($linkId, $this->resultData);
			//if link already exists in result set, just update its weight and insert matching keyword for later keyword tag specification
			if ($keyExists) {
			    $this->resultData[$linkId]->updateWeight($weight);
			    $this->resultData[$linkId]->addKeyword($currentKeyword);
			//if link isnt in result yet, insert it
			} else {
			    //get link reference
			    $linkData = $link->ref('link', 'linkId');
			    //get information about domain, to which link belongs (location, flagPath,...)
			    $domainData = $linkData->ref('domain', 'domainId');
			    //if is domain searchable and was selected before search, add link to result set. Otherwise ignore it
			    if ($domainData->searchable == 1 && in_array($domainData->id, $selectedDomains)) {
				//create new link instance
				$linkClass = new search\linkClass($linkData, $domainData);
				//insert matching keyword to links keyword set
				$linkClass->addKeyword($currentKeyword);
				//set links weight
				$linkClass->updateWeight($weight);
				//insert link into result set
				$this->resultData[$linkId] = $linkClass;
			    }
			}
		    }
		}
	    }
	}
}

V prvni fazi, se snazim vybrat veskera slova, ktere odpovidaji hledanemu vyrazu. Pote pro jednotliva slova hledam v relacni tabulce odpovidajici linky a pokud link jeste neni ve vysledne datove sade, tak vytvorim instanci jeho objektu a vlozim ho do vyslednych dat.

Je to timto zpusobem ± spravne? Nebo na to jdu uplne ze spatne strany? Samotna konstrukce dotazu mi prijde tezkopadna, ale nemuzu prijit na to, jak to zjednodusit.

Hlavni dira mi asi prijde kolem toho spojovani. Podle toho co jsem cetl v navodu k database selection a activerow bych chtel delat select nad tabulkou link, kterou bych spojil prez relacni tabulky na tabulku keywords, ale neumim si poradit jak do toho zakomponovat spojeni prez relacni tabulky. A jestli je to dobre reseni…

(klicovych „slov“ mam v databazi neco kolem 400 000 a odkazu asi 15 000)

Editoval Muhahe (13. 9. 2016 9:54)