Posts
-
Lexical scope
— Miscellaneous nerdosity. Programming and... uh...
-
Veekun's database
Can I get a bit of help with it? Actually I need help making a SQL request. I want to select all pokemon that can have a specified move (by any method, including eggmoves and previous evolution moves). Here's my request: http://pastebin.com/dV2QwV88 pokemon.canevolve is my own field: tinyint 1|0 - pretty selfexplanatory. The problem with this request is that it doesn't select pokemon that don't have evolution relatives (because there are no pokemon with their evolution_chain_id that can evolve), but if I remove 'AND canevolve=1' this request selects pokemon if they belong to branched family and one of branches have desired move. Say, Leafeon has Superpower, Blizzard, Moonlight, Flamethrower, Gardevoir has Close Combat and so on... This is because of the field pokemon.order (Why is it called this way btw, ORDER is a reserved word..?) I'm fairly new to SQL, so I could forget something.
Previous evolutions can be a bitch, but I think this query does the job: http://pastebin.com/D5Jd4C4A
Instead of using evolution families, it uses the pokemon.evolves_from_pokemon_id column to find the IDs of a pokémon's previous two evolution stages, and then joins rows from pokemon_moves that match any of the three IDs.
Also I forgot to put a semicolon on the end and I can't change it coz I don't have a Pastebin account :(
Mystic is correct; the only way to get parent evolutions is to self-join to the pokemon table twice.
You can't rely on order to do anything useful; it's explicitly meaningless. It's only there for national-dex-ish ordering in lists.
The name is a reserved word because... veekun uses an ORM which quotes everything anyway, so it didn't matter. 8)
But there is no such column as pokemon.evolves_from_pokemon_id. Or am I using outdated base? I got it from here: http://veekun.com/static/pokedex/downloads/veekun-pokedex.sqlite.gz
I tried joining pokemon_evolution table http://pastebin.com/aetZ2kLD , it works fine and fast but again, omits pokemon with no preevolutions. Example selects pokemon with Draco Meteor, Lati@s should be there. I have no idea why it's happening though, 'pokemon_moves.pokemon_id = this.id' should work in this case.
Found my problem, I was using wrong JOIN, it works this way: http://pastebin.com/zUbcbaay
Thanks for you help and the db itself.
Oh, I think the SQLite that's up is a bit behind the most recent database (which isn't running veekun.com, either). But yeah that'll work.
