ActiveRecord Query (Castle, Performance)

Question

I've 3 tables:

• Parts: Name: internal name, Active: bool
• Languages: list of languages (English, French, German, ....)
• PartsTranslations: RealName and Id's of the other 2 tables.

I would like to get a list of Parts telling me the internal name, active status and how many translations are missing (total lang subtract translations made)

I made this SQL query that is giving me what I need (I don't know if this is the better way to make it or not, but it's working):

SELECT 
    parts1.name, 
    parts1.active, 
    (
     (SELECT count(lang.id) 
      FROM languages AS lang) 
     - 
     (SELECT count(trans.id)
      FROM parts AS parts2 
      INNER JOIN partstranslations as trans
      ON parts2.id = trans.partid
      WHERE parts2.id = parts1.id)  
    )
from parts as parts1;

1º question- How can I make this query using Castle ActiveRecord?

2º question- How's the performance of the final query (expensive one)?

Thanks

Answer 1

I can't help with ActiveRecord, but this query should perform better than the one with a correlated subquery.

SELECT
    p.name,
    p.active, 
    (SELECT count(*) FROM languages) - count(pt.divid)
FROM
    Parts p
    LEFT JOIN PartsTranslations pt ON p.id=pt.divid
GROUP BY p.id, p.name, p.active

Or, if you do want to use the correlated subquery, you don't need to select from Parts in it again:

SELECT 
    p.name, 
    p.active, 
    (SELECT count(*) FROM Languages) - 
    (SELECT count(*) FROM PartsTranslations WHERE divid = p.id)            
FROM Parts p;

Answer 2

I was able to make this query in ActiveRecord using HqlBasedQuery, so I post here the answer to help others in the same situation as me.

HqlBasedQuery query = new HqlBasedQuery(typeof(Part),
 @"
    SELECT                     
        par.Id, 
        par.Name, 
        par.Active,                                         
        (SELECT count(*) - count(trans) FROM Language)                                                            
    FROM Part par
        LEFT JOIN par.PartsTranslations trans
    GROUP BY par.Id, par.Name, par.Active, trans.Part
    ");
query.SetQueryRange(startId, currentPageSize);

var results = from object[] summary in
              (ArrayList)ActiveRecordMediator.ExecuteQuery(query)
          select new PartProjection
          {
              Id = (int)summary[0],
              Name = (string)summary[1],
              Active = (bool)summary[2],
              TransMissing = (long)summary[3]                                       
          };

I also have pagination made on this query and it also give me a stronged typed PartProjection objects. This class does NOT need to have any ActiveRecord parameter.