this post is a long one sorry for that but the problem is complex too. Using mysql and php,
I can solve my problem with 3 sql requests, but not in 1, nor in 2. So that's why I post here :
does any of you know a way to do it in less than 3 strokes ? (sorry for my english) First let me explain the context :
it's about theme park ! So there is the theme park owner,
and he owns a theme park with different zones, and each zone contains games.
Each game can be setup with different settings, according to a game manager (the human responsible for the game). Here is what the interesting part of the schema looks like
there are 5 tables themeparkzone
--name (varchar) themeparkzone_has_game_with_manager
--themeparkzone_id (pk)
--game_id (pk)
--manager_id (fk)
--game_settings (text) themeparkzone_has_game
--themeparkzone_id (pk)
--game_id (pk)
--game_settings (text) game
--name (varchar) manager
--name (varchar) Two tables may need more explanations : themeparkzone_has_game_with_manager and themeparkzone_has_game. themeparkzone_has_game may hold the default settings for the games, if the themepark ownerwrote them. Then themeparkzone_has_game_with_manager holds the game settings that the game_manager has (or has not) setup,
and those settings should override the default settings from the themeparkzone_has_game table when set up. Because the themepark owner wants his themepark to be very attractive, he plans to change the settings every day,
and here is the convention that he and his team will have to deal with :to decide which game settings will apply, we have the following vars are given at startup :
- the manager_id : for example 22.
- the themeparkzone ids for today, for example 12,13,14 So the final goal is :
for the given manager_id, and for the given themeparkzone_ids,
find out the name and settings of the games, given that :
if a setting is set in the themeparkzone_has_game_with_manager it will apply
else
if a setting is set in the themeparkzone_has_game it will apply
else
there is no game. That's the problem I have,
and because I could'nt do it within 1 request,
I spent a first request to loop the themeparkzone_ids,
so that the problem now is to find the game name and settings for a given manager_id and a given themeparkzone_id.At this point, I can do it in two requests, but I wondered if one can do it in one. My requests are : SELECT g.name,w.game_settings
FROM game g
INNER JOIN themeparkzone_has_game_with_manager w on w.game_id=g.id
WHERE w.manager_id=22
AND w.themeparkzone_id in(12,13,14) and SELECT g.name,h.game_settings
FROM game g
INNER JOIN themeparkzone_has_game h on h.game_id=g.id
WHERE h.themeparkzone_id in(12,13,14) Foreach of them, I put the resulting array in a php array and then mix themaccording to the conventions. So I was wondering if there was one way to solve the conditions in a mysql requestinstead of doing it with php. A request that would do something like this :
foreach (themeparkzone_id and manager_id)
if exists a matching setting in themeparkzone_has_game_with_manager
takeit,
else
if exists a matching setting in themeparkzone_has_game
takeit,
else
takenothing. Hope someone understood what I tried to explain.
I can solve my problem with 3 sql requests, but not in 1, nor in 2. So that's why I post here :
does any of you know a way to do it in less than 3 strokes ? (sorry for my english) First let me explain the context :
it's about theme park ! So there is the theme park owner,
and he owns a theme park with different zones, and each zone contains games.
Each game can be setup with different settings, according to a game manager (the human responsible for the game). Here is what the interesting part of the schema looks like
there are 5 tables themeparkzone
--name (varchar) themeparkzone_has_game_with_manager
--themeparkzone_id (pk)
--game_id (pk)
--manager_id (fk)
--game_settings (text) themeparkzone_has_game
--themeparkzone_id (pk)
--game_id (pk)
--game_settings (text) game
--name (varchar) manager
--name (varchar) Two tables may need more explanations : themeparkzone_has_game_with_manager and themeparkzone_has_game. themeparkzone_has_game may hold the default settings for the games, if the themepark ownerwrote them. Then themeparkzone_has_game_with_manager holds the game settings that the game_manager has (or has not) setup,
and those settings should override the default settings from the themeparkzone_has_game table when set up. Because the themepark owner wants his themepark to be very attractive, he plans to change the settings every day,
and here is the convention that he and his team will have to deal with :to decide which game settings will apply, we have the following vars are given at startup :
- the manager_id : for example 22.
- the themeparkzone ids for today, for example 12,13,14 So the final goal is :
for the given manager_id, and for the given themeparkzone_ids,
find out the name and settings of the games, given that :
if a setting is set in the themeparkzone_has_game_with_manager it will apply
else
if a setting is set in the themeparkzone_has_game it will apply
else
there is no game. That's the problem I have,
and because I could'nt do it within 1 request,
I spent a first request to loop the themeparkzone_ids,
so that the problem now is to find the game name and settings for a given manager_id and a given themeparkzone_id.At this point, I can do it in two requests, but I wondered if one can do it in one. My requests are : SELECT g.name,w.game_settings
FROM game g
INNER JOIN themeparkzone_has_game_with_manager w on w.game_id=g.id
WHERE w.manager_id=22
AND w.themeparkzone_id in(12,13,14) and SELECT g.name,h.game_settings
FROM game g
INNER JOIN themeparkzone_has_game h on h.game_id=g.id
WHERE h.themeparkzone_id in(12,13,14) Foreach of them, I put the resulting array in a php array and then mix themaccording to the conventions. So I was wondering if there was one way to solve the conditions in a mysql requestinstead of doing it with php. A request that would do something like this :
foreach (themeparkzone_id and manager_id)
if exists a matching setting in themeparkzone_has_game_with_manager
takeit,
else
if exists a matching setting in themeparkzone_has_game
takeit,
else
takenothing. Hope someone understood what I tried to explain.