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
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
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
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
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
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
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.? 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 script, will have to play around with it. I also found Structget(), but it doesn't like anything other than alpha and numeric in the Keys. Even using ['item 1'], Structget() throws an error or ['item-1'], has to be ['item1']. Using rereplace(key, '[^[:alpha:]]', ,all) when I create the structure, I can get Structget() to work. It is a pain, because then everytime the structure is searched,the rereplace needs to be done on the key. But this is still faster than looping thru the structure to find a key(at least on large Structures). FoundPath = StructKeyFind(MyStruct, rereplace(key, '[^[:alpha:]]', ,all) ) NewPath = 'MyStruct' FoundPath[x].path FoundKey = StructGet(Newpath) #FoundKey.lastitem# can even: FoundKey[newitem] = StructNew() FoundKey[newitem].hours = hoursWorked In my case here newitem would be a persons id. Rodney ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237808 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
Isaac,
Thanks for the script, will have to play around with it.
I also found Structget(), but it doesn't like anything other than alpha and
numeric in the Keys.
Even using ['item 1'], Structget() throws an error or ['item-1'], has to be
['item1'].
Using rereplace(key, '[^[:alpha:]]', ,all) when I create the
structure, I can get Structget() to work.
It is a pain, because then everytime the structure is searched,the rereplace
needs to be done on the key.
But this is still faster than looping thru the structure to find a key(at
least on large Structures).
FoundPath = StructKeyFind(MyStruct, rereplace(key, '[^[:alpha:]]',
,all) )
NewPath = 'MyStruct' FoundPath[x].path
FoundKey = StructGet(Newpath)
#FoundKey.lastitem#
can even:
FoundKey[newitem] = StructNew()
FoundKey[newitem].hours = hoursWorked
In my case here newitem would be a persons id.
Rodney
-Original Message-
From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]
Sent: Friday, April 14, 2006 9:59 AM
To: CF-Talk
Subject: 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
CFOUTPUT#Evaluate(NewPath '.lastitem')#/cfoutput
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.
cfscript
temp = Mystruc;
aPath = listToArray(path);
arrayDeleteAt(aPath,1);
while (arrayLen(aPath)) {
temp = temp[aPath[1]];
arrayDeleteAt(aPath,1);
}
/cfscript
cfoutput#temp.lastitem#/cfoutput
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:237787
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
