CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordruby-on-rails-3.2
Richard Stokes
Richard Stokes

Reputation: 3552

SQL ORDER BY LIKE

This is similar to my previous question located here.Is there any way in SQL (using ActiveRecord) that you can order a relation using the LIKE operator? For instance, I'm querying a relation where one of the columns is a long string that typically contains many comma-separated-values. I would like to order the resulting relation by ordering to the front the records whose value for the aforementioned column matches a smaller string using LIKE (e.g. if my variable is "Spain", then I would like a record with a value like "Ireland, Spain, France" to be ordered to the front of the relation).

I tried this in active record with something like MyModel.order("country_preferences LIKE ?", country), but got an SQL error. What is the correct way to perform this kind of ordering?

Update (extracted from comments):

Using PostgreSQL 9.1. Current SQL error here: http://pastie.org/8037147 .

5 records where country_preference = "Ireland, Spain, France", 5 records where country_preference = "Ireland, Germany, France". Given a particular country, would like to return a relation where records whose country_preferences string contains this country are ordered to the front.

Upvotes: 1

Views: 1569

Answers (4)

Denis de Bernardy
Denis de Bernardy

Reputation: 78581

e.g. if my variable is "Spain", then I would like a record with a value like "Ireland, Spain, France" to be ordered to the front of the relation

You need a case statement, for that. It might look like this in Rails:

MyModel.order("CASE WHEN country LIKE '%' || ? || '%' THEN 0 ELSE 1 END", country)

Though I'm honestly not 100% on the latter. The SQL, though, should look like this:

select ...
from ...
where ...
order by case
         when country like '%Spain%' then 0
         else 1
         end

Do note that this is NOT an efficient query, though. The order by clause basically guarantees that no efficient index will be used to fetch the top 10 rows.

Upvotes: 0

Talasila
Talasila

Reputation: 161

if my variable is "Spain", then I would like a record with a value like "Ireland, Spain, France" to be ordered to the front of the relation.

In that case, assign weights to the values according to your variable and ORDER by it.

 DECLARE @test NVARCHAR(20) = '%Spain%'

SELECT Column1 ,
       CASE WHEN Column1 LIKE @test
            THEN 1
            ELSE 0 
       END [Weight]
FROM Table
ORDER BY [Weight] DESC

If you dont want the 'weight' column show up, create a subquery on top of this query and leave out the 'weight' column.

Upvotes: 0

mu is too short
mu is too short

Reputation: 434975

Your problem is that ActiveRecord's order doesn't support placeholders so you'll have to deal with the ? by hand, sigh. Something nasty like this should make it go:

c = ActiveRecord::Base.connection.quote(country)
MyModel.order("country_preferences LIKE '%' || #{c} || '%'")

You could combine this with Goat_CO's CASE if you don't want to rely on boolean ordering.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

Not entirely sure if a SQL answer is helpful, but you can use a CASE statement in your order by, necessary criteria isn't clear from your question, but perhaps this will help:

SELECT *
FROM table
ORDER BY CASE WHEN country_preference LIKE '%Ireland%' THEN 1
              WHEN country_preference LIKE '%Spain%' THEN 2
              WHEN country_preference LIKE '%France%' THEN 3
              ELSE 4
          END

Upvotes: 0

Related Questions