Re: StructFindKey Path and evaluate
Wow. Cubes and rollup sound nifty. I'll have to google up a bit, as I was like "what?". Sad, I know. But all that aside, I think you should think about restructuring your data. Specifically your task logic. You can't do something different? Are you in charge of the DB or is someone forcing this format on you? And all the niftyness of a database being what it is, there are still times where it simply makes more sense to not go the "super complicated all-in-one" query route. Sure you may stick with the same DB forever (I wouldn't count on it tho), but what about 6 months from now, when you look at that query and your head spins. Or the table layout happens to change, etc. etc.. I suck at giving advice. Sorry. But I'd look again at the task "linking" stuff, db wise, or perhaps breaking the query up a bit, making it a little easier on the head. There are plenty of ways to cache stuff and whatnot, so what I said about letting the DB do what it was designed for (don't make CF do it all!) is, like much of what I say, partially true. Sometimes it's easier to let CF do what the DB is great at, just for maintainability's sake. Sometimes. Eh, the right tool for the job and all that. I'd think about getting away from the 1.1.1 type stuff tho and use indexes and keys and link tables and whatnot, as that stuff gets gnarly when it gets big. Note: I went the route of recursive linking and for most stuff it's fine, but for other stuff, mostly large stuff (hmmm... ) it kind of sucks. But 6 of one and half a dozen (bakers, or normal ;) or whatever. You could negate X with Y, etc., etc., so... *shrug* If you can't bend the spoon... watch The Matrix. :d On 4/20/06, Bruce, Rodney S C-E LCMC HQISEC/Signal Solutions < [EMAIL PROTECTED]> wrote: > > We are using MS SQL 2000. > I do some of the math in the db. > I am very weak with rollup, so maybe you can see what I am missing. > Example: > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238457 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: StructFindKey Path and evaluate
We are using MS SQL 2000. I do some of the math in the db. I am very weak with rollup, so maybe you can see what I am missing. Example: Project: Task1 --Task1.1 --Task1.2 -- Has hours --Task1.2.1 --Task1.2.2 -- Has hours --Task1.2.3 --Task1.3 Task2 Task3---Has hours The rollup can get me the totals for a Project, CostArea and each individual task, but not the roll ups for tasks like Task1.2 or Task1. Here's a query I am playing with:(limiting to one project for faster testing :o). SELECT P.PROJ_NAME, TF.TEXT_VALUE AS CostArea, T.TASK_UID, T.TASK_NAME, GA.res_name, SUM(GA.Cost) AS cost, SUM(GA.HOURS) AS hours FROM TBLAllProjects P INNER JOIN ProjectServer_2003.dbo.MSP_TASKS T ON P.PROJ_ID = T.PROJ_ID LEFT OUTER JOIN TBLGovActuals GA ON P.PROJ_ID = GA.proj_id AND T.TASK_UID = GA.TASK_UID LEFT OUTER JOIN ProjectServer_2003.dbo.MSP_TEXT_FIELDS TF ON T.TASK_UID = TF.TEXT_REF_UID WHERE (TF.TEXT_FIELD_ID = 188744479) AND (TF.TEXT_VALUE IS NOT NULL) AND (T.TASK_UID > 0) AND (P.PROJ_ID = 840) GROUP BY P.PROJ_NAME, TF.TEXT_VALUE, T.TASK_UID, T.TASK_NAME, GA.res_name, T.TASK_NAME WITH ROLLUP The only fields that tell you were a Task is, are: Task_lvl(1,2,3, etc) and Task_number(1, 1.1, 1.2, 1.1.1, ext). The Query doesn't know that 1.1, 1.1.1 and 1.2 get rolled up into 1. So I don't see were it's possible to do the roll ups for the top lvl tasks. Thanks Rodney -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 5:22 AM To: CF-Talk Subject: Re: StructFindKey Path and evaluate Yes, I was thinking that a cube of this would probably provide most of the desired info in a query (although I haven't looked closely enough to be sure). Does your DB do cube and rollup? On 4/20/06, Thomas Chiverton <[EMAIL PROTECTED]> wrote: > On Wednesday 19 April 2006 17:24, Bruce, Rodney S C-E LCMC > HQISEC/Signal Solutions wrote: > > If there is a better way to do this, please let me know. I don't really > > like the way I am doing it, but unfortunetly I havent come up with > > anything better and I do seem to do things the hard way. > > Getting the DB to do the maths ? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238334 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: StructFindKey Path and evaluate
Yes, I was thinking that a cube of this would probably provide most of the desired info in a query (although I haven't looked closely enough to be sure). Does your DB do cube and rollup? On 4/20/06, Thomas Chiverton <[EMAIL PROTECTED]> wrote: > On Wednesday 19 April 2006 17:24, Bruce, Rodney S C-E LCMC HQISEC/Signal > Solutions wrote: > > If there is a better way to do this, please let me know. I don't really > > like the way I am doing it, but unfortunetly I havent come up with anything > > better and I do seem to do things the hard way. > > Getting the DB to do the maths ? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238253 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: StructFindKey Path and evaluate
On Wednesday 19 April 2006 17:24, Bruce, Rodney S C-E LCMC HQISEC/Signal Solutions wrote: > If there is a better way to do this, please let me know. I don't really > like the way I am doing it, but unfortunetly I havent come up with anything > better and I do seem to do things the hard way. Getting the DB to do the maths ? -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238251 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: StructFindKey Path and evaluate
>Ya know if that tree was flattened into a few database tables, it'd all be >much easier :-) >-- >Tom Chiverton >Advanced ColdFusion Programmer The information comes from 4 tables. There's many more fields in each table, but here is the short break down. Project_table Proj_id Proj_name WorkPlan_id Task_table Task_id Task_name Proj_id Cost_id Task_lvl(works with the Task_number, 1(is lvl 1), 1.1(lvl 2), 1.1.1(lvl 3) and so forth) Task_number(this is the closes to linking parent/children, the numbers are 1, 1.1 1.2, 1.1.1, 1.1.2) So a task with number 1.1 is a child to 1 Cost_table Cost_id Cost_area Hours_table Task_id Task_name Person_name Hours Cost_per_hour Worker_type What is wanted is, put a workplan_id in and get all projects/tasks/person(cost/hours). Hope I can explain this, :o) All Projects and tasks need to be displayed(whether hours are logged against it or not). The display is hierarical(much more graphical, lol): Project_name --1 Task_name --1.1 Task_name 1.1.1 Task_name 1.1.2 Task_name Person_name: Hours/cost Person_name: Hours/cost --2 Task_name Person_name: Hours/cost When a user mouses over the project_name or task_name, they get a popup display with roll ups for that lvl(ie. Mouse over 1.1 would include 1.1,1.1.1, 1.1.2). Mouse over the project_name gives the over all totals for the whole project. These are boken out into: Total: hours/Costs; Totals by Worker_type1: hours/costs; Totals by Worker_type2: hours/costs;(etc..) Totals by cost_area1: hours/costs; Totals by cost_area2: hours/costs;(etc..) I do two queries that populate/create the structure. The first links: Project, Task and Cost Tables and creates the main structure. The second gets the information from the hours table and adds it to the structure(per examples in previous emails). While adding the worker information, the totals are also calculated and placed in the structure. Then loop thru the structure to create the displays. The other ways I could come up with had me looping thru queries multipule times while creating the display, which was slower. If there is a better way to do this, please let me know. I don't really like the way I am doing it, but unfortunetly I havent come up with anything better and I do seem to do things the hard way. Thanks Rodney ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238129 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: StructFindKey Path and evaluate
On Monday 17 April 2006 17:07, Bruce, Rodney S C-E LCMC HQISEC/Signal Solutions wrote: > So I am not going to the very bottom of the structure, the value at > MyStruct.ProjectA.Task1.Task1.3, would be a structure not a simple value. Ya know if that tree was flattened into a few database tables, it'd all be much easier :-) -- Tom Chiverton Advanced ColdFusion Programmer ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238075 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: StructFindKey Path and evaluate
Yes, StruckFindKey returns a value, but in my case the value is a structure. Example: [MyStruct] [ProjectA] --[Task1] [Task1.1] [Task1.2] [Task1.3] --totalhours --totalcost --costarea --[Task2] [Task2.1] [ProjectB] --[Task1] All tasks(at all lvls)are structures with the keys totalhours,totalcost and costarea. I then have a person that has logged hours to a task. I have to find the task and add the persons information to it. So I am not going to the very bottom of the structure, the value at MyStruct.ProjectA.Task1.Task1.3, would be a structure not a simple value. I then add the persons hours/cost to the totals and add another lvl to the structure for each person . [Task1.3] --totalhours --totalcost --costarea --[person1name] Person1cost Person1hours --[person2name] Person2cost Person2hours So, I guess the simple answer to your question is yes, I am getting the path to another key(s), just below the one I found. Rodney -Original Message- From: Denny Valliant [mailto:[EMAIL PROTECTED] Sent: Saturday, April 15, 2006 3:45 PM To: CF-Talk Subject: Re: StructFindKey Path and evaluate Most likely I missed something, because doesn't structFindKey return a value as well as the path to the key? Unless you're talking about getting the "path" to another key, not the one you just found.? XPath only works with XML documents, neh? Hmm... must need more coffee... missing something obvious, feels I... :d ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237916 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: StructFindKey Path and evaluate
> Most likely I missed something, because doesn't > structFindKey return a value as well as the path > to the key? Unless you're talking about getting > the "path" to another key, not the one > you just found.? The native function only works on the keys of an individual structure. The issue Bruce is dealing with is that he's got nested structures, so he's looking for a way to navigate the path from the top to a nested structure. For example: struct [ [cat] [dog] [bird] ] This structure would only need ColdFusion native struct functions like StructFindKey. struct [ [cat[ [large[ [lion] [tiger] [jaguar] ]] ]] ] This nested structure would require a more elaborate solution in order to fetch "jaguar" from the top. What bruce has is a string representing the path from the top to "jaquar", i.e. his string contains the value "struct.cat.large.jaguar", at which point he can either use evaluate(string) to return the jaguar variable, or he would need to find a more elaborate solution, because even if he removes "struct" from the beginning of the string, structKeyFind(struct,string) won't find "cat" within "struct" (or "cat.large.jaquar" because the structure doesn't contain a "cat.large.jaguar" key, it contains a "cat" key). s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237851 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: StructFindKey Path and evaluate
Most likely I missed something, because doesn't structFindKey return a value as well as the path to the key? Unless you're talking about getting the "path" to another key, not the one you just found.? XPath only works with XML documents, neh? Hmm... must need more coffee... missing something obvious, feels I... :d On 4/14/06, Bruce, Rodney S C-E LCMC HQISEC/Signal Solutions < [EMAIL PROTECTED]> wrote: > > Isaac, > > Thanks for the
RE: StructFindKey Path and evaluate
Isaac, Thanks for the
Re: StructFindKey Path and evaluate
> Hi all
> I have a structure I am using StructFindKey with.
> Which returns an array/Structure.
> One of keys returned is Path, which is a dot separated
> list of the path thru
> the structure to the key your looking for.
> Something like: MyStruc.item1.item2.item3.item4 (find key
> Item4), returns
> item1.item2.item3.item4
> I tried: NewPath = 'MyStruc' & Path (with and without
> using evaluate)
> couldn't get it to work.
> I did get this to work:
> did a replace to make path =
> ['item1']['item2']['item3'['item4']
> NewPath = 'MyStruc' & path
> #Evaluate(NewPath & '.lastitem')#
> Is there anyway to get this to work with out using
> Evaluate?
> Hope I explained this well enough?
Yes, but in this case evaluate might actually be faster.
temp = Mystruc;
aPath = listToArray(path);
arrayDeleteAt(aPath,1);
while (arrayLen(aPath)) {
temp = temp[aPath[1]];
arrayDeleteAt(aPath,1);
}
#temp.lastitem#
You could encapsulate that into a function, which would make it less
efficient but gain you loads of maintainability (imo that's
important).
I've used this strategy for getting nested values before, but not for
the sake of efficiency -- in my case I've done this when I didn't know
what the names of keys might be, so this allows the storage of keys
that begin with numbers or are for some other reason invalid variable
names which would produce an error with the isDefined() function. If
you know that all your structure keys begin with alpha characters,
don't contain hyphens, etc. then it is probably more mechanically
efficient in this case to use the single evaluate() than to use the
loop and indeterminate number of array management statements.
s. isaac dealey 434.293.6201
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237770
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
