Hello, I have a search with an override item in SimpleSimon template, inside I have the follow query:
Select a.id, cck.pk, s.srv_estado, e.sigla, s.srv_cidade, i.cidade, s.srv_imagem, a.title nome_empresa, a.introtext, a.catid, t.title segmento, t.alias segmento_alias, s.srv_endereco, s.srv_numero, s.srv_bairro, s.srv_cep, s.srv_fone1, s.srv_fone2, s.srv_celular, s.srv_url From #__content a Inner Join #__cck_core cck ON cck.pk = a.id left Join #__contentitem_tag_map m ON m.content_item_id = a.id left join #__tags t ON t.id = m.tag_id Inner Join #__cck_store_form_servicos s ON cck.pk = s.id Inner Join #__estados e ON e.id = s.srv_estado Inner Join #__cidades i ON i.id = s.srv_cidade Where a.title like '%la%' or t.title like '%la%' or t.alias like '%la%' Group by a.title
What this query does? This query join results from articles, tags and two free tables. The problem is this query doesn't work, because I enabled debug mode in configuration (inside seblod) and seblod does another query:
SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.cck AS cck,t0.storage_location AS loc,tt.id AS type_id,tt.alias AS type_alias
FROM `#__cck_core` AS t0
LEFT JOIN `#__content` AS t1 ON t1.id = t0.pk
LEFT JOIN `#__cck_core_types` AS tt ON tt.name = t0.cck
WHERE t1.state = 1
AND t1.access IN (1,1,2,3,6)
AND ( t1.publish_up = '0000-00-00 00:00:00' OR t1.publish_up <= '2016-11-29 17:59' )
AND ( t1.publish_down = '0000-00-00 00:00:00' OR t1.publish_down >= '2016-11-29 17:59' )
AND t0.cck = 'servicos'
AND ((t1.title LIKE '%la%'))
ORDER BY t1.title ASC
LIMIT 1
So, I would like to know how to override the seblod query with my query. I have tried the follow code:
$db = JFactory::getDBO();
$db->getQuery(true); // I'm trying to use this to start a new query
$query = " Select a.id, cck.pk, s.srv_estado, e.sigla, s.srv_cidade, i.cidade, s.srv_imagem, a.title nome_empresa, a.introtext, a.catid, t.title segmento, t.alias segmento_alias, s.srv_endereco, s.srv_numero, s.srv_bairro, s.srv_cep, s.srv_fone1, s.srv_fone2, s.srv_celular, s.srv_url From #__content a Inner Join #__cck_core cck ON cck.pk = a.id left Join #__contentitem_tag_map m ON m.content_item_id = a.id left join #__tags t ON t.id = m.tag_id Inner Join #__cck_store_form_servicos s ON cck.pk = s.id Inner Join #__estados e ON e.id = s.srv_estado Inner Join #__cidades i ON i.id = s.srv_cidade Where a.title like '%$nome_empresa%' or t.title like '%$nome_empresa%' or t.alias like '%$nome_empresa%' Group by a.title ";
$db->setQuery($query);
$lista = $db->loadObjectList();
Thank you,
Hi
Not sure exactly what you're up to but are you using Seblod List & Search.... and want a custom query that overrides Seblods?
If so then this could be the answer:
using-the-search-query-field-plugin
Make sure to select the fields option of standalone rather than append
Hi Bucklash, so I bought this field plugin and I'm trying to use it. Let see bellow how I'm using.
Bellow is the result of this query
The query doesn't work properly, how can use a correct query to fix.
Another situation, how can I add Or in the Where clause:
Select a.id, cck.pk, s.srv_estado, e.sigla, s.srv_cidade, i.cidade, s.srv_imagem, a.title nome_empresa, a.introtext, a.catid, t.title segmento, t.alias segmento_alias, s.srv_endereco, s.srv_numero, s.srv_bairro, s.srv_cep, s.srv_fone1, s.srv_fone2, s.srv_celular, s.srv_url From #__content a Inner Join #__cck_core cck ON cck.pk = a.id left Join #__contentitem_tag_map m ON m.content_item_id = a.id left join #__tags t ON t.id = m.tag_id Inner Join #__cck_store_form_servicos s ON cck.pk = s.id Inner Join #__estados e ON e.id = s.srv_estado Inner Join #__cidades i ON i.id = s.srv_cidade
Where [MATCH]a.title||$uri->getValue('art_title') OR t.title||$uri->getValue('art_title') OR t.alias||$uri->getValue('art_title')[/MATCH]
Group By a.title
Thank you in advance.
Sorry folks, it was my mistake, it was missing some fields in item. Now it is working.
Thank you