CODEWITHSUNDEEP
mysqlsolr
Salines
Salines

Reputation: 5767

Solr multiple sort results, but first premium (true) posts

I have start learning Solr, and trying to understand and implement same query like one i have done in mysql, to return results in same order and logic.

What i need:

default mysql example query / without search params:

SELECT 
  * 
FROM 
  postings Postings 
  // LEFT JOIN query ..
WHERE 
  (
    // where query..
  ) 
ORDER BY 
  Postings.premium DESC, // <--- bool (1), 
  FIELD(Postings.source, "local") DESC, 
  Postings.cpc DESC

and example with search parameter:

SELECT 
  MATCH (Postings.title) AGAINST ('developer' IN BOOLEAN MODE) AS `Postings__relavance_title`, 
  MATCH (Postings.description) AGAINST ('developer' IN BOOLEAN MODE) AS `Postings__relavance_description`, 
  // other Fields 
FROM 
  postings Postings 
  // LEFT JOIN queries ...
WHERE 
  (
    MATCH (
      Postings.title, Postings.description
    ) AGAINST ('developer' IN BOOLEAN MODE) 
  ) 
ORDER BY 
  (Postings__relavance_title * 2)+ Postings__relavance_description DESC, 
  Postings.premium DESC, // <--- bool (1)
  FIELD(Postings.source, "local") DESC, 
  Postings.cpc DESC

How to sort / order solr data in same way?

Upvotes: 5

Views: 1045

Answers (3)

Salines
Salines

Reputation: 5767

Here is solution for our task:

We must:

select fields like: 

fl=score,*

boost query fields, like:

bq=source:local^50 (premium:true^100 OR premium:false^0)

and sort by score:

sort=score DESC, created DESC

in solr schema:

<field name="premium" type="boolean" indexed="true" stored="true"/>

Upvotes: 2

MatsLindh
MatsLindh

Reputation: 52902

You can give Solr a set of sort criteria:

&sort=premium desc, date_created desc

... this will give you all the premium posts first, then all the non-premium posts, while being ordered by date_created inside each group.

This assumes that you have indexed your boolean field in Solr as a boolean / int field. Also, sorting by fields are more efficient if you've enabled docValues for those fields, but that will be on by default for the fields that support them in the most recent versions of Solr.

Upvotes: 0

Rick James
Rick James

Reputation: 142528

Clearly you understand the SQL tricks to achieve your goal.

I don't know Solr, but that sounds rather complex for a 3rd party software to provide for. If there is a way to hand-code SQL (and have Solr simply pass it through), I suggest you do it that way.

Upvotes: 0

Related Questions