| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Sable Blitzmann
Massively Dynamic
Reverberation Project
13
   |
Posted - 2011.10.12 19:26:00 -
[1] - Quote
Hello all,
I'm trying to put together a SQL query that will give me all the skill prereqs for any item. I'm not really sure how to got about doing this...
I have it partially working: it gives me the immediate skills required (primary, secondary, etc), but not the prereqs of those skills. I guess I can loop through the results and get the prereqs for them recursively, but I'd rather do it all in one query if possible.
This is what I have so far (to pull the prereqs for the Torpedo skill):
SELECT * FROM dgmtypeattributes WHERE typeID = 3325 AND ((attributeID > 181 AND attributeID < 185) OR (attributeID > 276 AND attributeID < 280))
which results in:
+--------+-------------+----------+------------+
| typeID | attributeID | valueInt | valueFloat |
+--------+-------------+----------+------------+
| 3325 | 182 | 3319 | NULL |
| 3325 | 183 | 3324 | NULL |
| 3325 | 277 | 4 | NULL |
| 3325 | 278 | 3 | NULL |
+--------+-------------+----------+------------+
Two problems with it:
1) I would like to get all the skills, not just Missile Launcher Op and Heavy Missiles. For example, Heavy Missiles also has prereqs, and I would like those included in the result
2) The level required for the skills (4 and 3 in the results). Is there anyway to include them on the row with the skills, so as to look like this:
+--------+-------------+----------+------------+------------+
| typeID | attributeID | valueInt | level | valueFloat |
+--------+-------------+----------+------------+------------+
| 3325 | 182 | 3319 | 4 | NULL |
| 3325 | 183 | 3324 | 3 | NULL |
+--------+-------------+----------+------------+
Any help would be much appreciated. =) |
Desmont McCallock
37
|
Posted - 2011.10.12 19:35:00 -
[2] - Quote
https://forums.eveonline.com/default.aspx?g=posts&m=49803#post49803 |
Steve Ronuken
Cossette Moana
17
|
Posted - 2011.10.12 19:35:00 -
[3] - Quote
Unfortunately, it's a recursive or hierarchical query that you need. Which mysql doesn't natively support. Oracle does, but the express edition's a trifle heavy in comparison. Though I'm seriously considering getting a local copy for this kind of thing.
What I'd suggest is trying it out with a custom function to do the recursive bits.
Take a look at http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ which might show you what you need to do.
|
Steve Ronuken
Cossette Moana
17
|
Posted - 2011.10.12 19:38:00 -
[4] - Quote
Might be that you've already seen it, but you can probably use COALESCE() to deal with the whole valueint and valuefloat issue. It returns the first value that's not null, from the values you hand in. |
Sable Blitzmann
Massively Dynamic
Reverberation Project
13
|
Posted - 2011.10.12 19:40:00 -
[5] - Quote
Steve Ronuken wrote:Might be that you've already seen it, but you can probably use COALESCE() to deal with the whole valueint and valuefloat issue. It returns the first value that's not null, from the values you hand in.
Actually, I use IFNULL(valueInt,valueFloat) AS value. =)
Thanks for the info guys, I'll look into both of those solutions
|
Desmont McCallock
37
|
Posted - 2011.10.12 19:53:00 -
[6] - Quote
Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn. |
Sable Blitzmann
Massively Dynamic
Reverberation Project
13
|
Posted - 2011.10.12 20:00:00 -
[7] - Quote
Desmont McCallock wrote:Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn.
Heh, same can be said for me.
I don't think your query is what I'm looking for tho. It does show how to do problem 2), but doesn't show the prereqs of the prereqs. I'll continue looking into things, but I think a stored procedure is the way to go (that is, if I don't just simply loop it via PHP and do a few extra queries =P)
I'll post any solution I come up with. |
Desmont McCallock
37
|
Posted - 2011.10.12 20:25:00 -
[8] - Quote
Sable Blitzmann wrote:Desmont McCallock wrote:Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn. Heh, same can be said for me. I don't think your query is what I'm looking for tho. It does show how to do problem 2), but doesn't show the prereqs of the prereqs. I'll continue looking into things, but I think a stored procedure is the way to go (that is, if I don't just simply loop it via PHP and do a few extra queries =P) I'll post any solution I come up with.
In your shoes I would create two queries for each problem and then link the queries.
Creating one for both will end up messy. Just my 0.02 cents. |
Sable Blitzmann
Massively Dynamic
Reverberation Project
13
|
Posted - 2011.10.14 22:19:00 -
[9] - Quote
Seemed to have figured it out with the extreme help from the guy who wrote that article on hierarchical queries linked above.
Solution can be found here: http://stackoverflow.com/questions/7746667/how-to-do-this-mysql-stored-procedure-for-hierarchical-query
This is the query that I'm currently using (finding the prereqs for the Minmatar Carrier skillbook):
Quote:SELECT output.*, invtypes.typeName
FROM(
SELECT test2(dgmtypeattributes.typeID) AS id, @level AS level, @parent AS parent, @path AS path
FROM (
SELECT
@start_with := 24314,
@id := @start_with,
@level := 0,
@parent := 0,
@path := ''
) vars, dgmtypeattributes
WHERE @id IS NOT NULL
) output
INNER JOIN invtypes ON output.id = invtypes.typeID
And the result:
Quote:+-------+-------+--------+-----------------------+----------------------------+
| id | level | parent | path | typeName |
+-------+-------+--------+-----------------------+----------------------------+
| 3337 | 1 | 24314 | ,24314 | Minmatar Battleship |
| 3327 | 2 | 3337 | ,24314,3337 | Spaceship Command |
| 3333 | 2 | 3337 | ,24314,3337 | Minmatar Cruiser |
| 3327 | 3 | 3333 | ,24314,3337,3333 | Spaceship Command |
| 3329 | 3 | 3333 | ,24314,3337,3333 | Minmatar Frigate |
| 3327 | 4 | 3329 | ,24314,3337,3333,3329 | Spaceship Command |
| 3442 | 1 | 24314 | ,24314 | Drone Interfacing |
| 3436 | 2 | 3442 | ,24314,3442 | Drones |
| 20533 | 1 | 24314 | ,24314 | Capital Ships |
| 20342 | 2 | 20533 | ,24314,20533 | Advanced Spaceship Command |
| 3327 | 3 | 20342 | ,24314,20533,20342 | Spaceship Command |
+-------+-------+--------+-----------------------+----------------------------+
There's still a few quirks here and there. Most notably for some newer ships/items that have more than 3 immediate prereqs. Such items include the t3 cruiser skillbooks, which have 6 immediate subsystem skills (and thus don't follow the 812, 813, and 814 attribute id rule to the tee). Should be minor fixes from here, and I'll look into them more after a few hours of rest. ^_^ I'll post any interesting results I may find here and update the StackOverflow post to mirror any updates.
|
Leska Helios
New Eden Advanced Solutions
0
|
Posted - 2012.01.09 14:42:00 -
[10] - Quote
Could you post the corresponding function test2 pls? As it seems the one you posted on stackoverflow doesnt work too well with this latest query you posted here. It always runs into a infinity and kills my server it seems. The shorter one you posted on stackoverflow seems to work fine though, so i guess you must have made changes to the function for that final SQL...
Would be awesome as this would be exactly what i could use atm... |
Leska Helios
New Eden Advanced Solutions
0
|
Posted - 2012.01.09 16:32:00 -
[11] - Quote
Nvm was a small mistake on my end. Now it works fine. Many thanks as this is a great help.
PS.: if you ever got around to updating it and stuff i would be interested in those updates.
Anyway thx alot |
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |