Lower the Rank of Search Results of one Type in Sphinx Search

Yuri Boyz
Yuri Boyz used Ask the Experts™
on
I am using Sphinx Search in which I have simple join query which fetch results from 2 tables.

In my one table I have Column 'Fields'. 'Fields' column contain different values like 'News', 'Page', 'Blog' etc.

The searching is working fine. I just want that documents having type 'NEWS' must be shown in last.

For example:

 I search a word 'Content' and in search result I got 15 results i.e:
10 documents  with type 'Page',
2 documents  with Type 'Blog'
'3' results with type 'NEWS'
I want that records with type 'NEWS' will always rank lower.


Here is my query:

select n.nid as ID, n.title, n.status, n.created, f.field_body_value
from node n             
inner join field_data_field_body f on (n.nid = f.entity_id)
where n.status = 1;

Open in new window


Do I need to update my query or do any setting in Sphinx.conf?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is Fields an attribute or a full-text field? Can you show the whole source section?
Yuri BoyzSoftware Engineer

Author

Commented:
Field is a column name in mysql database. which stores values like 'News', 'MainPage' , 'Blog' etc.
Can you please explain that what source are you willing to see? Sphinx.conf or what?
Yuri BoyzSoftware Engineer

Author

Commented:
select n.nid as ID, n.title, n.type, n.status, n.created, f.field_body_value 
            from node n 
            inner join field_data_field_body f on (n.nid = f.entity_id)
            where n.status = 1
            order by type='news' asc

Open in new window


Here is my sql queries which works well and show 'NEWS' result at last. This is due to the Order by clause. But it cannot works in Sphinx. How can i implement this order by clause in Sphinx?

Thanks
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Here's an example of how this can be done:

[snikolaev@dev01 ~]$ cat sphinx_29092013.conf
source min
{
      type = mysql
      sql_host = localhost
      sql_user = root
      sql_pass =
      sql_db = test
      sql_query = select 1, 'john' body, 'news' type union select 2, 'john' body, 'blogs' type union select 4, 'john' body, 'news' type union select 3, 'john' body, 'page' type
      sql_attr_string = type
}

index idx_min
{
      path = idx_min
      source = min
}

searchd
{
    listen                       = 9314:mysql41
    log = sphinx_min.log
    pid_file = sphinx_min.pid
    binlog_path = #
}

[snikolaev@dev01 ~]$ indexer -c sphinx_29092013.conf --all --rotate
Manticore 2.6.1 9a706b4@180119 dev
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2018, Manticore Software LTD (http://manticoresearch.com)

using config file 'sphinx_29092013.conf'...
indexing index 'idx_min'...
collected 4 docs, 0.0 MB
sorted 0.0 Mhits, 100.0% done
total 4 docs, 16 bytes
total 0.003 sec, 4083 bytes/sec, 1020.92 docs/sec
total 7 reads, 0.000 sec, 13.7 kb/call avg, 0.0 msec/call avg
total 12 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
rotating indices: successfully sent SIGHUP to searchd (pid=9270).

[snikolaev@dev01 ~]$ mysql -P9314 -h0
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.6.3 5bc9e81@180316 dev

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select *, if(type='news',1,if(type='blogs',2,3)) w from idx_min where match('john') order by w desc;
+------+-------+------+
| id   | type  | w    |
+------+-------+------+
|    3 | page  |    3 |
|    2 | blogs |    2 |
|    1 | news  |    1 |
|    4 | news  |    1 |
+------+-------+------+
4 rows in set (0.00 sec)
Yuri BoyzSoftware Engineer

Author

Commented:
Thank you so much for this explanation. Let me test it with union. I wonder why order by cannot work in Sphinx.
I will back after few hours. Thanks
The union part is just to add random docs to the index, it's doesn't make much sense. The key thing which should be most important to you is "if(type='news',1,if(type='blogs',2,3)) ... order by w desc"
Yuri BoyzSoftware Engineer

Author

Commented:
@Manticore Sphinx next gen
Well, there is some confusion. You have understand my issue but not 100%.  Let me explain in more details.

1) I have a  query in Sphinx.conf (see below query) When I run my PHP script and enter a search keyword like 'welcome' I get full search results. The only issue is that my results are not sorted according to my desired column. The Order by clause cannot work in Sphinx.Conf file. I will get perfect results if I execute query  in mysql. The order by clause just display the records with Bundle type 'NEWS' in the last.

source drupal_search
{
        type                = mysql
        sql_host            = localhost
        sql_user            = root
        sql_pass            =
        sql_db              =  sc
        sql_port            = 3306
        sql_query 	 =   select n.nid as ID, n.title, n.status, n.created, f.field_body_value , f.bundle\
                                       from node n \
                                      inner join field_data_field_body f on (n.nid = f.entity_id)\
                                      where n.status = 1\
                                     order by f.bundle='news' asc\

       sql_attr_timestamp   = created
       sql_attr_uint        = status
       sql_field_string = bundle
       sql_attr_string        = type
	sql_query_pre       = SET NAMES utf8

}

Open in new window


NOTE: When I run this from Command prompt, I came to notice that my results are sorted according to the creation Date of records.
Whereas I want to sort results on this clause "Order by Bundle='NEWS' asc". Means sort results on Bundle but display those results in the last where Bundle='NEWS'.

C:\sphinx5\bin>search.exe --config c:\sphinx5\sphinx.conf
Sphinx 2.0.3-release (r3043)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file 'c:\sphinx5\sphinx.conf'...
index 'drupal_search': query '': returned 10 matches of 10 total in 0.000 sec

displaying matches:
1. document=59, weight=1, status=1, created=Tue Mar 06 17:40:03 2018, bundle=mainpage
2. document=87, weight=1, status=1, created=Sat Mar 17 01:58:16 2018, bundle=mainpage
3. document=91, weight=1, status=1, created=Thu Mar 22 19:48:04 2018, bundle=mainpage
4. document=93, weight=1, status=1, created=Thu Mar 29 14:23:43 2018, bundle=mainpage
5. document=104, weight=1, status=1, created=Fri Mar 30 18:15:50 2018, bundle=mainpage
6. document=105, weight=1, status=1, created=Fri Mar 30 18:18:05 2018, bundle=news
7. document=106, weight=1, status=1, created=Fri Mar 30 18:27:40 2018, bundle=mainpage
8. document=107, weight=1, status=1, created=Fri Mar 30 18:33:00 2018, bundle=news
9. document=108, weight=1, status=1, created=Fri Mar 30 21:29:55 2018, bundle=mainpage
10. document=113, weight=1, status=1, created=Sat Mar 31 01:26:28 2018, bundle=ztest

words:

Open in new window


2) I have tried it another technique like Instead of using ORDER BY , I have rewrite my mysql query in sphix.conf with UNION (see below)

source drupal_search
{
	type                = mysql
        sql_host            = localhost
        sql_user            = root
        sql_pass            =
        sql_db              = sc
        sql_port            = 3306

	sql_query 	=      (\
 		SELECT\
                n.nid as ID,\
                n.title,     \
                n.type as type,\
                n.status,\
                n.created,\
                f.field_body_value \
            FROM\
                node n\
            inner join field_data_field_body f on (n.nid = f.entity_id)\
            where n.status = 1  \
            AND\
                type <>'news'\
        )\
    UNION \
        (\
            SELECT\
               n.nid as ID,\
                n.title, \
                n.type as type,\
                n.status,\
                n.created,\
                f.field_body_value \
            FROM\
                node n\
             inner join field_data_field_body f on (n.nid = f.entity_id)\
            where n.status = 1  \
            AND\
                type= 'news' \
        );
}

index drupal_search
{
	source              = drupal_search
          path                = C:\sphinx5\data\drupal_search
	docinfo             = extern

	charset_type        = utf-8	
	min_infix_len = 2

}

Open in new window


This query returns perfects results when I run in MySql but when I run it with my SPHINX PHP script the sorting cannot works according to my criteria.

Hope you got better understanding.

The main motive is to return results according to the sorting column 'BUNDLE' and show records in the last which has BUNDLE type=news.
1) sorting things in sql_query doesn't make much sense, in sql_query you just tell Sphinx how it should fetch your data from mysql, it doesn't affect the order of their appearance in following searches
2) to order search results you need to do ORDER BY in SphinxQL (not MySQL) query, e.g.:
mysql> select *, if(type='news',1,if(type='blogs',2,3)) w from idx_min where match('john') order by w desc;
or use corresponding API method if you don't use SphinxQL
Yuri BoyzSoftware Engineer

Author

Commented:
Yes i got some understanding but I am not using SphinxQL. I am using Legacy API. So any idea how to do this?
$cl = new SphinxClient();
$cl->SetServer('127.0.0.1', 9313);
$cl->SetSelect("if(type='news',1,if(type='blogs',2,3)) w,*");
$cl->SetSortMode(SPH_SORT_ATTR_DESC, 'w');
print_r($cl->Query("john"));

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial