Translating a nested loop to sql

[Thalassicus]

for promotionInfo in GameInfo.Unit_FreePromotions(
        "SUBSTR(PromotionType,-7)<>'PENALTY'"
        ) do
    for overrideInfo in GameInfo.Civilization_UnitClassOverrides(
            "UnitType='"..promotionInfo.UnitType.."'
            AND CivilizationType<>'CIVILIZATION_BARBARIAN'"
            ) do
        DB.Query("UPDATE UnitPromotions SET LostWithUpgrade='false' WHERE Type='"..promotionInfo.PromotionType.."'")
    end
end

I basically need to translate this nested for loop to sql. How do I properly use "join"? I've got this so far... but something's wrong.

UPDATE UnitPromotions
SET LostWithUpgrade = 'true'
WHERE (    PediaType = 'PEDIA_ATTRIBUTES'
OR      SUBSTR(Type,-7) = 'PENALTY'
OR      Type = 'PROMOTION_ROUGH_TERRAIN_ENDS'           -- penalty
OR      Type = 'PROMOTION_ONLY_DEFENSIVE'               -- penalty
OR      Type = 'PROMOTION_MUST_SET_UP'                  -- penalty
OR      Type = 'PROMOTION_CITY_SIEGE'                   -- demolish
AND NOT Type = 'PROMOTION_INDIRECT_FIRE'                -- earned
AND NOT Type = 'PROMOTION_CAN_MOVE_AFTER_ATTACKING'     -- not important
AND NOT Type = 'PROMOTION_DESERT_POWER'                 -- barbarians
AND NOT Type = 'PROMOTION_ARCTIC_POWER'                 -- barbarians
AND NOT Type = 'PROMOTION_GUERRILLA'                    -- barbarians
AND NOT Type IN (
        SELECT PromotionType
        FROM Unit_FreePromotions
        JOIN Civilization_UnitClassOverrides
        ON ( Civilization_UnitClassOverrides.PromotionType = Unit_FreePromotions.PromotionType
        AND Civilization_UnitClassOverrides.CivilizationType <> 'CIVILIZATION_BARBARIAN'
        )
);

Everything works if I comment out the "AND NOT Type IN" section.

 

[Thalassicus]

I think I figured it out. SqlLite can't use joins in update statements, so what I needed was nested IN statements:

AND NOT Type IN ( 
    SELECT PromotionType
    FROM Unit_FreePromotions
    WHERE UnitType IN (
        SELECT UnitType
        FROM Civilization_UnitClassOverrides
        WHERE CivilizationType <> 'CIVILIZATION_BARBARIAN'
        )
    )
);

This works in the SQL file I have. However, I need to use this both in SQL and in Lua. When I copy it to lua, this query fails:

local ignoredQuery = [[
        PediaType = 'PEDIA_ATTRIBUTES'
OR      PediaType = 'PEDIA_SHARED'
OR      SUBSTR(Type,-7) = 'PENALTY'
OR      Type = 'PROMOTION_ROUGH_TERRAIN_ENDS'             
OR      Type = 'PROMOTION_ONLY_DEFENSIVE'                 
OR      Type = 'PROMOTION_MUST_SET_UP'                     
OR      Type = 'PROMOTION_CITY_SIEGE'                     
OR      Type = 'PROMOTION_GREAT_GENERAL'                 
AND NOT Type = 'PROMOTION_INDIRECT_FIRE'                 
AND NOT Type = 'PROMOTION_CAN_MOVE_AFTER_ATTACKING'     
AND NOT Type = 'PROMOTION_DESERT_POWER' 
AND NOT Type = 'PROMOTION_ARCTIC_POWER'                 
AND NOT Type = 'PROMOTION_GUERRILLA'                     
AND NOT Type IN ( 
    SELECT PromotionType
    FROM Unit_FreePromotions
    WHERE UnitType IN (
        SELECT UnitType
        FROM Civilization_UnitClassOverrides
        WHERE CivilizationType <> 'CIVILIZATION_BARBARIAN'
        )
]]
default_ignoredPromotion = {}
for promotionInfo in GameInfo.UnitPromotions(ignoredQuery) do
    default_ignoredPromotion[promotionInfo.Type] = true
end

Is there some sort of length to how long a query can be in Lua? The full query works in SQL, and f I remove everything past PROMOTION_DESERT_POWER it works fine in lua too, but once I add another line to the query it fails in Lua.

 

[MdREV]

The function format SQL is a feature of the Civ 5 API, and not standard LUA, and we all know how well the API is not documented. Short answer is that I suspect noone knows the answer.

Good news, you can shorten your query with judicious use of IN, and I'd also suspect with this kind of limitation that whitespace counts against you... In addition, you are missing a closing ')' at the end of your query.

PediaType IN ('PEDIA_ATTRIBUTES', 'PEDIA_SHARED') OR
SUBSTR(Type,-7) = 'PENALTY'
OR Type IN (
 'PROMOTION_ROUGH_TERRAIN_ENDS',
 'PROMOTION_ONLY_DEFENSIVE',
 'PROMOTION_MUST_SET_UP',
 'PROMOTION_CITY_SIEGE',
 'PROMOTION_GREAT_GENERAL'
) AND NOT Type IN (
 'PROMOTION_INDIRECT_FIRE',
 'PROMOTION_CAN_MOVE_AFTER_ATTACKING',
 'PROMOTION_DESERT_POWER',
 'PROMOTION_ARCTIC_POWER',
 'PROMOTION_GUERRILLA',
) AND NOT Type IN (
 SELECT PromotionType
 FROM Unit_FreePromotions
 WHERE UnitType IN (
  SELECT UnitType
  FROM Civilization_UnitClassOverrides
  WHERE CivilizationType <> 'CIVILIZATION_BARBARIAN'
 )
)

 

[Thalassicus]

Thanks! That's very helpful, I haven't used IN before so I'm slow in identifying good places to put it.

I also realized I forgot to take short-circuiting into account, so the logic was incorrect.

I ended up just coding the Lua part in Lua, and the SQL in SQL. It's somewhat cumbersome to maintain two different formats in two files for the same information, but works.