Page 1 of 1
Modify free text search *Solved
Posted: Mon Sep 28, 2015 8:42 am
by simonhudin
Hi,
I am modifying phpMyFaq for a multi-brand version where multiple brands share a large portion of the FAQ but where certain FAQ's have to be hidden depending on certain parameters.
I've added 3 custom tables to the phpMyFaq DB for Category -> Brand & FAQ -> Brand and all is set for Categories and Tags. However, I need to join in these 3 tables for the free text search and I have been looking through the code for ~2 days now without being able to back track where the free text queries are actually being built.
I know the search string values are set in the search() function in PMF/Search
And that the SQL is executed by query() in PMF/DB/Mysqli.php
However, the SQL variables / conditions in search() in PMF/Search is set in an Object.
Anyone who knows where the search string is assembled?
Thanks beforehand.
Simon
Re: Modify free text search
Posted: Tue Sep 29, 2015 5:45 am
by Thorsten
Hi,
cool project!
What do you mean by assembled?
bye
Thorsten
Re: Modify free text search
Posted: Tue Sep 29, 2015 8:52 am
by simonhudin
Hi,
Might have expressed myself a bit weird.
In PMF\Search.php, the function search() creates an object that contains the search query in various keys.
It's not a SQL query string. So somewhere, I assume there is a function that takes the data in the keys and creates the complete SQL query string and then runs it through PMF\DB\Mysqli.php function query().
That function I was hoping I could hijack and add the table joins.
Or rewrite it to accept an array in
->setJoinedTable($fcrTable).
E.g
Code: Select all
->setJoinedTable(array($fcrTable,"custom_brand","custom_catagory","custom_data"))
Code: Select all
->setJoinedColumns(array(
$fdTable . '.id = ' . $fcrTable . '.record_id',
$fdTable . '.lang = ' . $fcrTable . '.record_lang',
$fdTable . '.id = custom_data.data',
'custom_brand.id = custom_data.brand'
))
Etcetera.
Thanks!
Simon
Re: Modify free text search
Posted: Sat Oct 03, 2015 12:50 pm
by Thorsten
Hi Simon,
that's in PMF_Search::search()
bye
Thorsten
Re: Modify free text search
Posted: Mon Oct 05, 2015 2:37 pm
by simonhudin
Managed to iron out the workflow
In PMF\Search\Database.php
Working draft
Code: Select all
public function getJoinedTable()
{
//CUSTOM SEARCH JOINEDTABLE
if(isset($_SESSION['bmlfaq'])){
$currentBrand = $_SESSION["bmlfaq"];
}
elseif(isset($_COOKIE["bmlfaq"])){
$currentBrand = $_COOKIE["bmlfaq"];
}
else{
$currentBrand = null;
}
if (empty($this->joinedTable)) {
return '';
}
elseif(isset($currentBrand)){
return ' LEFT JOIN custom_data cda ON cda.data = fd.id LEFT JOIN custom_brand cbr ON cbr.id = cda.brand LEFT JOIN ' . $this->joinedTable . ' ON ';
}
else {
return ' LEFT JOIN ' . $this->joinedTable . ' ON ';
}
}
Code: Select all
public function getConditions()
{
$conditions = '';
if (count($this->conditions)) {
foreach ($this->conditions as $column => $value) {
if (is_array($value)) {
$conditions .= ' AND ' . $column . ' IN (' . implode(', ', $value) . ')';
} else {
$conditions .= ' AND ' . $column . ' = ' . $value;
}
}
//CUSTOM SEARCH CONDITION
if(isset($_SESSION['bmlfaq'])){
$currentBrand = $_SESSION["bmlfaq"];
}
elseif(isset($_COOKIE["bmlfaq"])){
$currentBrand = $_COOKIE["bmlfaq"];
}
else{
$currentBrand = null;
}
if(isset($currentBrand)){
$conditions .= ' AND cbr.name = ' . "'" . strval($currentBrand) . "'";
}
}
return $conditions;
}
Cheers.
Will hint about the wishlist to my boss
Regards,
Simon
Re: Modify free text search
Posted: Mon Oct 05, 2015 3:56 pm
by simonhudin
Worth mentioning that I noticed some inconstancy with the queries passed through PMF\Search\Database.php
When you do a full text search, the query says
while when opening a FAQ entry, it says
Had to rebuild
Code: Select all
public function search($searchterm, $allLanguages = true)
in PMF\Search.php to use table abbreviation FD.
Search function is a bit more in line with the rest of the SQL queries and rebuild works with PMF_Db::getTablePrefix().
Code: Select all
public function search($searchterm, $allLanguages = true)
{
$fdTable = PMF_Db::getTablePrefix() . 'faqdata AS fd';
$fcrTable = PMF_Db::getTablePrefix() . 'faqcategoryrelations';
$condition = array('fd.active' => "'yes'");
$search = PMF_Search_Factory::create($this->_config, array('database' => PMF_Db::getType()));
if (!is_null($this->getCategoryId()) && 0 < $this->getCategoryId()) {
if ($this->getCategory() instanceof PMF_Category) {
$children = $this->getCategory()->getChildNodes($this->getCategoryId());
$selectedCategory = array(
$fcrTable . '.category_id' => array_merge((array)$this->getCategoryId(), $children)
);
} else {
$selectedCategory = array(
$fcrTable . '.category_id' => $this->getCategoryId()
);
}
$condition = array_merge($selectedCategory, $condition);
}
if ((!$allLanguages) && (!is_numeric($searchterm))) {
$selectedLanguage = array('fd.lang' => "'" . $this->_config->getLanguage()->getLanguage() . "'");
$condition = array_merge($selectedLanguage, $condition);
}
$search->setTable($fdTable)
->setResultColumns(array(
'fd.id AS id',
'fd.lang AS lang',
'fd.solution_id AS solution_id',
$fcrTable . '.category_id AS category_id',
'fd.thema AS question',
'fd.content AS answer'))
->setJoinedTable($fcrTable)
->setJoinedColumns(array(
'fd.id = ' . $fcrTable . '.record_id',
'fd.lang = ' . $fcrTable . '.record_lang'
))
->setConditions($condition);
if (is_numeric($searchterm)) {
$search->setMatchingColumns(array('fd.solution_id'));
} else {
$search->setMatchingColumns(array('fd.thema', 'fd.content', 'fd.keywords'));
}
$result = $search->search($searchterm);
if (!$this->_config->getDb()->numRows($result)) {
return array();
} else {
return $this->_config->getDb()->fetchAll($result);
}
}
Re: Modify free text search *Solved
Posted: Wed Oct 07, 2015 10:07 am
by Thorsten
Hi,
thanks for the hint, just fixed the inconsistency. Will be included in 2.8.25.
Cheers
Thorsten