author_ref as a
where
p.id = a.id and p.id in ($id)
group by a.id
order by field(p.id, $id)''', post_processors = [
hits.SplitOnSep('authors', sep='@#@'),
]
)
cl.AttachDBFetch(db_fetch)
# provide search cache
#cl.AttachCache(redis_cache)
# setup the different facets
cl.AttachFacets(
Facet('author', sql_col='author', sql_table='author_terms', sph_field='authors'),
Facet('keyword', sql_col='tag', sql_table='title_terms', attr='title_attr', sph_field='title'),
Facet('venue', sql_col='source_id', sql_table='venue_terms', sph_field='source_id'),
Facet('year', sql_table=None)
)
# we want to group by counts
group_sort = '@count desc'
# setup sorting and ordering of each facet
for f in cl.facets:
f.SetMaxNumValues(15)
# group by a custom function
f.SetGroupSort(group_sort)
# order the term alphabetically within each facet
f.SetOrderBy('@term', order='asc')
pmc_id,
(select group_concat(distinct mesh_term separator "@#@") from mesh as m where m.pmid = c.pmid) as mesh,
(select group_concat(distinct author separator "@#@") from authors as a where a.pmid = c.pmid) as authors,
(select count from pmc_num_citations as p where p.pmid = c.pmid) as citation_count
from citations as c
where pmid in ($id)
order by field(pmid, $id)''',
post_processors = [
hits.SplitOnSep('mesh', 'authors', sep='@#@'),
]
)
cl.AttachDBFetch(db_fetch)
# setup the different facets
cl.AttachFacets(
Facet('author', sph_field='authors'),
Facet('journal', sph_field='journal_title_abbreviation'),
Facet('mesh', attr='mesh_attr', sql_col='mesh_term', sql_table='mesh_terms_terms'),
Facet('year', sql_table=None),
)
# setup sorting and ordering of each facet
for f in cl.facets:
# group by a custom function
f.SetGroupFunc('sum(citation_count_attr)')
# f.SetGroupSort('@count desc')
# order the term alphabetically within each facet
f.SetOrderBy('@term', order='asc')
# fixes Sphinx bug: SetSelect with distributed indexes
f._set_select = f._set_select + ', %s, %s, pmid_attr, citation_count_attr' % (f._attr, 'year_attr')
# stop after having processed 500 results (see optimization)
order by field(imdb_id, $id)''',
post_processors=[
SplitOnSep('director',
'actor',
'genre',
'keyword',
sep='@#@')
])
cl.AttachDBFetch(db_fetch)
# give it a cache for the search and the facets
#cl.AttachCache(cache)
# setup the different facets
cl.AttachFacets(
Facet('year', sql_table=None),
Facet('genre'),
Facet('keyword',
attr='plot_keyword_attr',
sql_col='plot_keyword',
sql_table='plot_keyword_terms'),
Facet('director'),
Facet('actor'),
)
# for all facets compute count, groupby and this score
group_func = '''
sum(
if (runtime_attr > 45,
if (nb_votes_attr > 1000,
if (nb_votes_attr < 10000, nb_votes_attr * user_rating_attr, 10000 * user_rating_attr),
(select group_concat(distinct s.alias separator '@#@') from relPersona as r, tipoRelacionP2P as t, persona as s where r.origenP=p.id and r.destinoP=s.id and r.relacion=t.id and t.parent!=1) as relacion_persona,
(select group_concat(distinct s.alias separator '@#@') from relFamiliar as r, persona as s where r.origenP=p.id and r.destinoP=s.id) as relacion_familiar
from persona p
where p.is_active='T' and id in ($id)
order by field(id, $id)''', post_processors = [
SplitOnSep('organizaciones', 'relacion_empresa', 'conyuge', 'relacion_persona', 'relacion_familiar',sep='@#@')
]
)
cl.AttachDBFetch(db_fetch)
# give it a cache for the search and the facets
#cl.AttachCache(cache)
# setup the different facets
cl.AttachFacets(
Facet('organizaciones', attr='organizacion_attr', sql_col='alias', sql_table='Organizacion'),
Facet('empresas', attr='empresa_attr', sql_col='alias', sql_table='Organizacion'),
Facet('relacion_empresa', attr='relacionEmp_attr', sql_col='relationship', sql_table='tipoRelacionP20'),
Facet('relacion_persona', attr='relacionPers_attr', sql_col='alias', sql_table='persona'),
Facet('relacion_familiar', attr='relacionFam_attr', sql_col='alias', sql_table='persona'),
)
# for all facets compute count, groupby and this score
# setup sorting and ordering of each facet
for f in cl.facets:
#f.SetGroupFunc(group_func)
# order the term alphabetically within each facet
f.SetOrderBy('@term')