CODEWITHSUNDEEP
nhibernate-mapping
ra3etka
ra3etka

Reputation: 1

NHibernate mapping many-to-one fetch="join" formula

I've got the tables:

PROCESSES  STEP_STATUSES  STEPS
---------  -------------  -----------
PROC_ID    STAT_ID        STEP_ID
PROC_NAME  NAME           STEP_NUMBER
...                       STEP_NAME
                          ...
                          STAT_ID
                          PROC_ID

Is there any way a can map the Processes table into NHibernate with a property CurrentStep (which is a mapped STEPS table row) ?

I can do almost what I need with a mapping:

<many-to-one name="CurrentStep" formula="(
  select t.step_id
  from STEPS t
  where PROC_ID = t.PROC_ID 
    and STEP_NUMBER = nvl((select max(STEP_NUMBER), PROC_ID from STEPS where STAT_ID > 0 and PROC_ID = t.PROC_ID),
                                (select min(STEP_NUMBER), PROC_ID from STEPS where STAT_ID = 0 and PROC_ID = t.PROC_ID))
)"/>

But I can't set fetch="join" for this field, and I want to fetch all data with one query, altogether with CurrentStep property value - like this:

select t.*, st.*
from PROCESSES t
left outer join (
    select STEP_ID, PROC_ID 
    from STEPS s1
    where STEP_NUMBER = nvl((select max(STEP_NUMBER) from STEPS where STAT_ID > 0 and PROC_ID = s1.PROC_ID),
                            (select min(STEP_NUMBER) from STEPS where STAT_ID = 0 and PROC_ID = s1.PROC_ID))
          ) s on s.PROC_ID = t.PROC_ID
left outer join STEPS st on st.PROC_ID = s.PROC_ID and st.STEP_ID = s.STEP_ID

It's necessary because of performance.

So, the question id:

  1. Is there any way to set fetch="join" for such a complex formula field?
  2. Is there any way to join such a complex subquery?
  3. Is there any way to create ICriteria to get such query result if it is impossible to set mapping?

I'm sorry if the question is not clear, I'll try to clarify it if any questions would appear/

Upvotes: 0

Views: 654

Answers (1)

Firo
Firo

Reputation: 30813

an idea to query for what you want using batched reads

var lastStepWithStatus = session.CreateCriteria<Step>()
    .Add(Restriction.Eq("Process.Id", processId))
    .Add(Restriction.NotNull("Status"))
    .AddOrder(Order.Desc("Number"))
    .SetMaxResults(1)
    .Future<Step>();

var firstStepWithoutStatus = session.CreateCriteria<Step>()
    .Add(Restriction.Eq("Process.Id", processId))
    .Add(Restriction.IsNull("Status"))
    .AddOrder(Order.Asc("Number"))
    .SetMaxResults(1)
    .Future<Step>();

return lastStepWithStatus.Concat(firstStepWithoutStatus).Select(step => new { Process = Step.Process, CurrentStep = step }).FirstOrDefault();

another option is to use LINQ on the Steps collection to get the current step which implies that all steps are loaded first however.

Upvotes: 1

Related Questions