I have a situation which I can't believe is all that abnormal, but I
can't seem to make happen what I think I should be able to do.
I have a Ticket & Customer class.
A Customer calls in, the agent creates a ticket. easy 'nuff.
Now, agents need to see an open issues list (not customer-specific)
The Ticket entity is rather wide (100 properties), and also has some
collections.
However, for the list, we only need to show a subset of ticket
properties, and a subset of customer properties.
So, I created an IssueSearchResult class, with just the properties we
need in the grid.
The problem is that ALL the examples of join I've seen are the other
way around.
-- That is, my Customer table needs a TrackedItemID field.
-- I can map the three customer-specific fields just like I do the
GroupAssignedTime, but it seems to me that 3 scalar sub-queries that
join the same table on the same row is overhead and a performance
waste - all to do a simple join.
All I really need is a way to specify HOW the join is ... joined,
rather than have NH _assume_ how it should be joined.
The generated SQL:
this_.SAVED_CUSTOMER_PARENT_ID as SAVED40_38_0_,
this_1_.EHD_FLAG as EHD5_19_0_,
this_1_.VIP_FLAG as VIP3_19_0_,
this_1_.STRATEGIC_FLAG as STRATEGIC4_19_0_,
(-- GroupAssignedTimeFormula -- ) as formula0_0_
FROM TS_TROUBLE_TICKET this_
inner join TS_CUSTOMERS this_1_
on this_.TRACKED_ITEM_ID = this_1_.TS_CUSTOMER_ID
But what I need is:
FROM TS_TROUBLE_TICKET this_
inner join TS_CUSTOMERS this_1_
on this_.TS_CUSTOMER_ID = this_1_.ID
-------------------- Is it possible to get the needed join ?
------------------
Here is my mapping. I apologize for posting the Fluent mappings, but
'whether or not I should be able to do this' isn't dependent on the
method I go about doing the mapping (with the exception of, FNH
doesn't support it, but that's something I can address if I have to)
public IssueSearchResultMap()
{
base.ReadOnly();
base.Cache.ReadOnly();
base.Id(x => x.TrackedItemID,
IssueMap.Fields.TRACKED_ITEM_ID);
base.Table("ISSUES");
base.Join("CUSTOMERS", m =>
{
m.KeyColumn(IssueMap.Fields.CUSTOMER_ID);
m.Map(x => x.IsEHD,
CustomerMap.Fields.EHD_FLAG).CustomType("YesNo");
m.Map(x => x.IsVIP,
CustomerMap.Fields.VIP_FLAG).CustomType("YesNo");
m.Map(x => x.IsStrategic,
CustomerMap.Fields.STRATEGIC_FLAG).CustomType("YesNo");
});
// this works great.
// collapsing a historical audit collection into a single
DateTime - to calculate TimeInQueue
base.Map(x => x.GroupAssignedTime)
.Formula(string.Format("(SELECT gats.start_time " +
"FROM ts_group_assigned_times gats " +
"WHERE gats.tracked_item_id = {0} " +
"AND gats.end_time is null)",
TechSupportTicketMap.Fields.TRACKED_ITEM_ID
));
// this is supposed to be doing the same thing as
GroupAssignedTime, but Oracle doesn't like it.
// I get a 'right paren not closed' or similar error. if I
remove the Order By it doesn't complain.
// This isn't what I'm posting about, but if anyone can
shed some light, awesome :)
//
//base.Map(x => x.NextScheduledCallbackTime)
// .Formula(string.Format("(select
cbss.date_of_callback from (select cbs.date_of_callback from
ts_callbacks cbs " +
// "where cbs.tracked_item_id = {0} " +
// "and cbs.status = 'scheduled' " +
// "order by cbs.date_of_callback asc) cbss "
+
// "where cbss.rownum = 1 )",
// TechSupportTicketMap.Fields.TRACKED_ITEM_ID
// ));
// just here for consistency with the above generated SQL
base.Map(x => x.SavedCustomerParentId,
IssueMap.Fields.SAVED_CUSTOMER_PARENT_ID);
}
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
