If I am unable to do this in the mapping, can it be done at query time to populate the various other properties?
On Jul 7, 11:07 am, Joe B <[email protected]> wrote: > 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); > > > > }- Hide quoted text - > > - Show quoted text - -- 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.
