unknown column, agregation function, generic type

banter · **Joined:** Fri Apr 20, 2007 6:04 am **Posts:** 6

1)I would like to sort by rank which is created by a matematic operation of agregation functions. It is unable to create a alias. Hibernate reports: "unknown column rank"

Code:

String hql = "select company, (3*sum(broker.loginCount) +   15*sum(broker.contacts) + 5*sum(broker.alreadySeenCount)) as  rank "
  + "from Company company join company.brokers  broker " +
"group by broker.company.id " + "order by rank";

So I have to use

Code:

"order by col_1_0_ desc"

because of generated SQL (see below)

Code:

select
        company0_.id as col_0_0_,              3*sum(brokers1_.loginCount)+15*sum(brokers1_.contacts)+5*sum(brokers1_.alreadySeenCount) as col_1_0_,
        company0_.id as id1_,      
    from
        Company company0_ 
    inner join
        AbstractUser brokers1_ 
            on company0_.id=brokers1_.company_id 
    group by
        brokers1_.company_id 
    order by
        col_1_0_ desc limit ?

2) This query returns list of Object[] so I have to use terrible casting

Code:

Query query = session.createQuery(hql);
query.setMaxResults(maxResult);
List temp = query.list();
List<Company> bestPartners = new ArrayList<Company>();

for (int i = 0; i < temp.size(); i++) {
            bestPartners.add((Company) ((Object[]) temp.get(i))[0]);
        }

How could I get only list of Company?
(I use Hibernate 3.2.1.ga)

Ananasi · **Posted:** Fri Apr 20, 2007 8:38 am

1)

Code:

"order by col_1_0_ desc"

That's not good right there. There is no contract that says the AST parser has to maintain its naming across versions, or even within the same SessionFactory. That will break sooner or later. If you don't mind, if it exists, can you post the generated SQL from the original "order by rank" query?

2) Why not just this:

Code:

Query query = session.createQuery(hql); 
query.setMaxResults(maxResult); 
List<Company> bestPartners = query.list();

The compiler will say something like "Type safety: The expression of type List needs unchecked conversion to conform to List<Company>", but just ignore it (we know that it is a List<Company>) or add @SuppressWarnings("unchecked") to the method.

banter · **Joined:** Fri Apr 20, 2007 6:04 am **Posts:** 6

Ananasi wrote:

can you post the generated SQL from the original "order by rank" query?

Code:

select
        company0_.id as col_0_0_,
        3*sum(brokers1_.loginCount)+15*sum(brokers1_.contacts)+5*sum(brokers1_.alreadySeenCount) as col_1_0_,
        company0_.id as id1_,
        company0_.city as city1_,
        company0_.created as created1_,
        company0_.email as email1_,
        company0_.ico as ico1_,
        company0_.telephone as telephone1_,
        company0_.account as account1_,
        company0_.dic as dic1_,
        company0_.psc as psc1_,
        company0_.street as street1_,
        company0_.web as web1_,
        company0_.software as software1_,
        company0_.city_id as city14_1_,
        company0_.district_id as district16_1_,
        company0_.region_id as region15_1_,
        company0_.name as name1_ 
    from
        Company company0_ 
    inner join
        AbstractUser brokers1_ 
            on company0_.id=brokers1_.company_id 
    group by
        brokers1_.company_id 
    order by
        rank desc limit ?

Ananasi wrote:

List<Company> bestPartners = query.list();

It is a class cast exception. Problem is that I select company and new column which is number(rank). I select rank because I use it for order but I do not want in in result.

Ananasi · **Posted:** Fri Apr 20, 2007 9:37 am

I'm wondering....

If this is a common sorting mechanism for that class, can you define a formula column in your mapping. The query parser is obviously not parsing that statement properly.

Reference: http://www.hibernate.org/hib_docs/v3/re ... ing-column

banter · **Joined:** Fri Apr 20, 2007 6:04 am **Posts:** 6

My problem continues with setting a parametr to a formula http://forum.hibernate.org/viewtopic.php?t=973873&highlight=

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

what about:

Code:

String hql = "select\n"
           + "    company,\n"
           + "    (3*sum(broker.loginCount) + 15*sum(broker.contacts) + 5*sum(broker.alreadySeenCount))\n" 
           + "from\n"
           + "    Company company\n
           + "    join company.brokers broker\n"
           + "group by\n"
           + "    broker.company.id\n"
           + "order by\n"
           + "    (3*sum(broker.loginCount) + 15*sum(broker.contacts) + 5*sum(broker.alreadySeenCount))\n";

?

banter · **Joined:** Fri Apr 20, 2007 6:04 am **Posts:** 6

It does not return list of Company, but list of arrays (Company, number)

so I have to use custing

Code:

for (int i = 0; i < temp.size(); i++) { 
            bestPartners.add((Company) ((Object[]) temp.get(i))[0]); 
        }

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

You could create a VO that takes a Customer and an int in it's constructor and then change the HQL to select into the new VO:

Code:

public class CustomerSumVO{
    private Customer customer = null;
    private int sum = 0;

    public CustomerSumVO(){
    }

    public CustomerSumVO(Customer c, int sum){
        this.customer = c;
        this.sum = sum;
    }

    // code access methods...
}

String hql = "select new CustomerSumVO(\n" 
           + "    company,\n" 
           + "    (3*sum(broker.loginCount) + 15*sum(broker.contacts) + 5*sum(broker.alreadySeenCount))\n" 
           + ")\n"
           + "from\n" 
           + "    Company company\n 
           + "    join company.brokers broker\n" 
           + "group by\n" 
           + "    broker.company.id\n" 
           + "order by\n" 
           + "    (3*sum(broker.loginCount) + 15*sum(broker.contacts) + 5*sum(broker.alreadySeenCount))\n"; 

That would return you a List of CustomerSumVO obecjts.