Sortowanie po wyrażeniu SQL w Grails

Ostatnio mam okazję produkcyjnie pobawić się Grailsami. Co cieszy – zwłaszcza po niedawnych przygodach z prehistorycznym kodem, który prezentował ogromniaste kolce i strzykał na kilometr śmierdzącą śliną. Jednakże jak zwykle coś delikatnie było nie tak – stąd ten wpis.

Na pierwszy rzut oka sprawa jest dość prosta. Chcemy posortować zwracane z bazy danych rekordy za pomocą pewnego wyrażenia. Załóżmy, że mamy tabelę z listą postów na blogu. Tabelka zawiera tytuł, zajawkę, datę i wszystkie inne obowiązkowe w tym przypadku dane. Każdy rekord zawiera też informację o ilości odsłon danego postu, a także o ilości kliknięć na link do niego prowadzący z głównej strony bloga. Sprawa jest prosta:

SELECT * FROM posts 

Idźmy dalej. Może byłoby fajnie wyciągnąć posty o największym CTR (kliki do emisji)? Ok, lecimy:

SELECT * FROM posts ORDER BY main_page_click_count / emission_count DESC

Od strony logiki oraz kodu SQL sprawa jest niesamowicie prosta. Niestety w przypadku Grailsowego DSLa problem okazał się dość niebagatelny. Standardowe metody listujące ( list, findAll ) niestety nie przyjmują dla wyciągania/sortowania/grupowania wyrażeń – otrzymywałem błąd, iż dana właściwość nie istnieje w klasie domenowej. Próby stworzenia własności typu transient (nie zapisywanej w bazie danych) jako wyniku działania pewnej funkcji (w tym przypadku dzielenia dwóch innych własności) również nie zakończyła się powodzeniem.

Tutaj z pomocą przyszedł ‘spodni’ mechanizm frameworku, czyli konkretnie Hibernate. W tym ORMie mamy coś takiego jak kryteria. Sprawa zaczęła wyglądać na łatwiejszą, niestety, tylko wyglądać. Próba użycia restrykcji do klauzuli sortującej zakończyła się niepowodzeniem. Całe szczęście znalazłem w necie wpis o dokładnie tym samym zagadnieniu. Kod przeze mnie prezentowany bazuje na zamieszczonym przez autora ww. wpisu.

Musimy utworzyć oddzielną klasę, która będzie fizycznie odpowiedzialna za dodanie kawałka kodu SQL do naszego wynikowego zapytania. U mnie wygląda ona tak:

package com.wordpress.chlebik.hibernate.extension;

import org.hibernate.criterion.Order;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;


/**
 * Class extending Hibernate order by functionality to deal with
 * SQL-expression in order by  
 */
public class SortBySql extends Order {

    private static final long serialVersionUID = -6698545180750378282L;
    private String sqlFormula;
 
    /**
     * Constructor for Order.
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     */
    protected SortBySql(String sqlFormula) {
        super(sqlFormula, true);
        this.sqlFormula = sqlFormula;
    }
 
    public String toString() {
        return sqlFormula;
    }
 
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return sqlFormula;
    }
 
    /**
     * Custom order
     *
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     * @return Order
     */
    public static Order sqlFormula(String sqlFormula) {
        return new SortBySql(sqlFormula);
    }   
    
}

Klasę tę oraz metody wywołujemy w miejscu użycia kryteriów Hibernate. Dzięki sile domknięć kryteria można stworzyć oraz wywoływać w ten sposób:

 // To jest stworzenie obiektu kryteriow dla klasy domenowej 'Post'
 def listCriteria = Post.createCriteria()

 // To domkniecie, ktore przekazemy jako parametr do metody listujacej kryteriow
 def sortBySqlCriteria = {
           getInstance().addOrder( SortBySql.sqlFormula(" (main_page_click_count / emission_count) DESC ") )
 }
       
 // Wywolanie metod listujacych posty dla zadeklarowanych kryteriow          
def postList = listCriteria.list(sortBySqlCriteria) 

I w ten oto piękny sposób uzyskaliśmy zamierzony efekt. Zainteresowanych odsyłam do ww. wpisu. W tym przypadku (chodzi o CTR) należy również zwrócić uwagę na możliwy błąd dzielenia przez zero! Trzeba go obsłużyć na poziomie samej bazy danych – w moim przypadku było to Oracle i posiłkowałem się rozwiązaniami zaprezentowanymi w tym poście. Jednakże ostatecznie sprawa zakończyła się ustaleniem domyślnej wartości emisji nawiększą niż 0 i tym samym konieczność obsługi tego problemu można było pominąć.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s