Finding the best tokens to money ratio from mission items in Lego Universe

bgstack15

2025-02-20 09:29

In the self-hostable Darkflame Lego Universe game server, you can earn 2 types of currency: money, and faction tokens. In the late game, you accumulate these in spades, but only money is still the most useful. So a lot of advanced players are interested in determining the most efficient way to convert faction tokens into money.

The way to do this scientifically is within the database. All the mission objects are stored in the CDServer.sqlite file, so we can use sqlite3 for this.

I have a whole methodology of what I did, in heading Narrative below. It's very lengthy, and really just my scratch pad combined with the steps I took to become familiar with the database.

tl;dr

All the experienced Lego Universe players are right: Castle Model Pack 3. You can buy this for 10 tokens at Honor Accolade in Nexus Tower, and sell the items from it for 798 money, which makes the token:money ratio 1:79.

Fun picture showing ratio of money to tokens at 79:1

Sql query

You need at least sqlite 3.39 for the FULL JOIN operation. My narrative explains the same query without it, but you're not going to like it.

Technically this query is still incomplete because it shows some of the "small number of random options for rewards" but they also aren't available at Honor Accolade.

SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
FULL JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        FULL JOIN ComponentsRegistry cr ON cr.id = o.id
        FULL JOIN ItemComponent ic ON ic.id = cr.component_id
        FULL JOIN PackageComponent pc on pc.id = cr.component_id
        FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        FULL JOIN Objects o2 ON lt.itemid = o2.id
        FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
ORDER BY moneypertoken DESC
;

Inferior sql query

The query without full joins and is slightly more flawed, is this one. You can run this on CentOS 7 sqlite3 (less than 3.39).

/* This makes duplicates, and runs for about 5 minutes on my game server machine (CentOS 7, sqlite3.7). It's a horrible design, but technically it avoids FULL JOINs but is not worth it. */
SELECT DISTINCT * FROM
(
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
LEFT JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
        LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
        LEFT JOIN PackageComponent pc on pc.id = cr.component_id
        LEFT JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        LEFT JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        LEFT JOIN Objects o2 ON lt.itemid = o2.id
        LEFT JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        LEFT JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        LEFT JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
UNION ALL
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
        LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
        LEFT JOIN PackageComponent pc on pc.id = cr.component_id
        LEFT JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        LEFT JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        LEFT JOIN Objects o2 ON lt.itemid = o2.id
        LEFT JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        LEFT JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        LEFT JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
LEFT JOIN (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
ON i.oid = p.oid
ORDER BY moneypertoken DESC
)
;

To dump this to a csv, run this in the sqlite3 REPL first:

.headers on .mode csv .output /path/to/file.csv

Narrative

2025-01-03-6 17:01
idea for next program: find most cost-effective Tokens-to-money translation given the items available to me in Honor Accolade.

# Plan to learn how to easily calculate most cost-effective tokens-to-money item
Explore the database and learn how to perform each task listed here.

  1. find object by name, "2011 Championship Sparkler", and get raw monetary value (but a character can sell for only 1/10 of this value)
   SELECT cr.component_id as cid, o.id, ic.baseValue, o.name from Objects o JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE name = "Superior Mate's Cutlass" AND cr.component_type = 11;
-- cr.component_type 2=render, 9=skill, 11=item
  1a. find object token value from Honor Accolade
/* WITH IDs */
    SELECT cr.component_id as cid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name from Objects o JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE o.name = "Superior Mate's Cutlass" AND cr.component_type = 11;
/* WITHOUT IDs */
SELECT ic.baseValue, ic.commendationCost AS tokens, o.name from Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE name = "Superior Mate's Cutlass" AND cr.component_type = 11;

  2. find object from a pack by name, and get raw monetary value of all items in it
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (11,53);
/* show LootMatrix.LootTableIndex grouped */
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, GROUP_CONCAT(lm.LootTableIndex) lti FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (11,53);
/* show objectIds that are in the loot table of the package. We are not calculating percentages because we assume a package contains 100% chance of a drop of the listed items. */
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid itemid FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (53);
/* select only the objectIds */
SELECT lt.itemid itemid FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (53);

/* and now, look up each item in the loot table and get baseValue. Display money, tokens, item name. */
SELECT ic.baseValue, ic.commendationCost AS tokens, o.name from Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE cr.component_type = 11 AND o.id IN (
SELECT lt.itemid itemid FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (53)
);

/* sum the monetary value for a package */
SELECT SUM(ic.baseValue) from Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE cr.component_type = 11 AND o.id IN (
SELECT lt.itemid itemid FROM Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (53)
);
/* This is fewest full joins that still works. */
SELECT SUM(ic.baseValue) from Objects o FULL JOIN ComponentsRegistry cr ON cr.id = o.id JOIN ItemComponent ic ON ic.id = cr.component_id WHERE cr.component_type = 11 AND o.id IN (
SELECT lt.itemid itemid FROM Objects o JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id JOIN PackageComponent pc on pc.id = cr.component_id JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex WHERE o.name = "Castle Model Pack 3" AND cr.component_type = 53
);

/* summary entry for a given item name? This has some duplicate entries, because it repeats each loot table item 4 times */
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid, ic2.baseValue, ic2.commendationCost as tokens2, o2.name FROM Objects o JOIN ComponentsRegistry cr ON cr.id = o.id FULL JOIN ItemComponent ic ON ic.id = cr.component_id FULL JOIN PackageComponent pc on pc.id = cr.component_id FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex FULL JOIN Objects o2 ON lt.itemid = o2.id FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (11,53);

/* Shows each item separately */
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (11,53)
GROUP BY o2.name;

/* now, show a single entry showing the total baseValue, tokencost, and item name */
SELECT SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)), SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)), name
FROM (
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
WHERE o.name = "Castle Model Pack 3" AND cr.component_type IN (11,53)
GROUP BY o2.name
)
GROUP BY name;

/* generate a summary of multiple item ids */
SELECT SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)), SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)), name
FROM (
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
WHERE o.name in ("Castle Model Pack 3","Superior Mate's Cutlass") AND cr.component_type IN (11,53)
GROUP BY o.name,o2.name
)
GROUP BY name;

  3. find objects/packs given by a mission, and get raw monetary value of all items in it
/* list all items, period*/
SELECT reward_item1, reward_item2, reward_item3, reward_item4 FROM Missions m WHERE m.id = 1125;

SELECT SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)), SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)), name
FROM (
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
WHERE cr.component_type IN (11,53)
GROUP BY o.name,o2.name
)
GROUP BY name;

/* useful mission: 17, 82, 14, 427, 633 */
/* list all items from a given mission id. TECHNICALLY miscalculates when the reward_item1_count>1 or isChoiceReward=1 */
SELECT SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)), SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)), mid,  COUNT(oname), SUBSTR(GROUP_CONCAT(oname),1,300), SUBSTR(GROUP_CONCAT(o2name),1,300)
FROM (
SELECT cr.component_id as crid, o.id, ic.baseValue, ic.commendationCost AS tokens, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name o2name, m.id mid
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11,53)
AND m.id IN (17,14,82)
AND m.reward_item1 > -1
AND o.id > -1
GROUP BY o.name,o2.name
)
GROUP BY mid;

/* goal: Get all mission reward items. */
SELECT SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money,oid, mid, SUBSTR(GROUP_CONCAT(oname),1,300), SUBSTR(GROUP_CONCAT(o2name),1,300)
FROM (
SELECT cr.component_id as crid, o.id oid, ic.baseValue, ic.commendationCost AS tokens, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name o2name, m.id mid
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11,53)
--AND m.id IN (17,14,82)
AND m.reward_item1 > -1
AND o.id > -1
GROUP BY o.name,o2.name
)
GROUP BY oid
ORDER BY money/tokens
;

/* get mission reward items with moneypertoken column already calculated */
SELECT SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, (SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0))/10)/SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) moneypertoken, oid, mid, SUBSTR(GROUP_CONCAT(oname),1,300) oname, SUBSTR(GROUP_CONCAT(o2name),1,300)
FROM (
SELECT cr.component_id as crid, o.id oid, ic.baseValue, ic.commendationCost AS tokens, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name o2name, m.id mid
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11,53)
--AND m.id IN (17,14,82)
AND m.reward_item1 > -1
AND o.id > -1
GROUP BY o.name,o2.name
)
GROUP BY oid
ORDER BY moneypertoken DESC
;

/* goal show package cost, and also individual item cost together. This is the closest, but it still screws up Pirate's Scimitar by combining it with castle model pack 2, because componentsregistry 11 27 when it should only combine with cr 53 (part of a model pack) */
SELECT SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, (SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0))/10)/SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) moneypertoken, oid, mid, SUBSTR(MAX(oname),1,300), COUNT(o2name), SUBSTR(GROUP_CONCAT(o2name),1,300)
FROM (
SELECT cr.component_id as crid, o.id oid, ic.baseValue, ic.commendationCost AS tokens, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.name o2name, m.id mid
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11,53)
AND m.id IN (17,14,82,427)
AND m.reward_item1 > -1
AND o.id > -1
GROUP BY o.name,o2.name
)
GROUP BY oid
ORDER BY money/tokens
;

/* trying to fix the superior pirate's scimitar grouped with castle walls like its from castle model pack 2, mid 427 */
SELECT SUM(IFNULL(money,0)), SUM(IFNULL(tokens,0)), oid, name
FROM (
SELECT o.id oid, ic.baseValue money, ic.commendationCost AS tokens, o.name name
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11)
AND m.id IN(427,14,17,82)
GROUP BY o.name
)
GROUP BY name;

/* avoid any inner select. This shows a value of 0 for mega brick booster pack, but is fast */
SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, (SUM(IFNULL(ic.baseValue,0))/10)/SUM(IFNULL(ic.commendationCost,0)) moneypertoken, o.id oid, o.name name
FROM Objects o
JOIN ComponentsRegistry cr ON cr.id = o.id
JOIN ItemComponent ic ON ic.id = cr.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11)
--AND m.id IN(427,14,17,82)
GROUP BY o.name
ORDER BY moneypertoken DESC, tokens DESC LIMIT 30
;


SELECT SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, (SUM(IFNULL(ic.baseValue,0))/10)/SUM(IFNULL(ic.commendationCost,0)) moneypertoken, o.id oid, o.name name, MAX(ic2.baseValue), SUBSTR(GROUP_CONCAT(cr2.id),1,300) cr2id, SUBSTR(GROUP_CONCAT(cr2.component_id),1,300), cr2.component_type
FROM Objects o
JOIN ComponentsRegistry cr ON cr.id = o.id
JOIN ItemComponent ic ON ic.id = cr.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o.id
JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
JOIN PackageComponent pc ON pc.id = cr2.component_id
JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
JOIN Objects o2 ON lt.itemid = o2.id
WHERE cr.component_type IN (11)
AND cr2.component_type IN (53)
AND m.id IN(427,14,17,82)
GROUP BY o.name, cr2.component_type
ORDER BY moneypertoken DESC, tokens DESC
;


/* WIP; need to join these rather than union. */
SELECT * FROM (
SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, (SUM(IFNULL(ic.baseValue,0))/10)/SUM(IFNULL(ic.commendationCost,0)) moneypertoken, o.id oid, o.name name, NULL, NULL, NULL
FROM Objects o
JOIN ComponentsRegistry cr ON cr.id = o.id
JOIN ItemComponent ic ON ic.id = cr.component_id
JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type IN (11)
AND m.id IN(427,14,17,82,633)
--AND IFNULL(ic.baseValue,0) > 0
GROUP BY o.name
--ORDER BY moneypertoken DESC, tokens DESC
UNION
SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, (SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0))/10)/SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) moneypertoken, oid, SUBSTR(MAX(oname),1,300) name, COUNT(o2name), SUBSTR(GROUP_CONCAT(o2id),1,300), SUBSTR(GROUP_CONCAT(o2name),1,300)
FROM (
SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.id o2id, o2.name o2name, GROUP_CONCAT(cr3.id),GROUP_CONCAT(cr3.component_id), cr3.component_type cr3ct
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ComponentsRegistry cr3 ON o.id = cr3.id
FULL JOIN ItemComponent ic ON ic.id = cr3.component_id
FULL JOIN PackageComponent pc on pc.id = cr3.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type = 11
AND cr3.component_type IN (53)
AND m.id IN (427,14,17,82,633)
AND o.id > -1
GROUP BY o.name,o2.name,cr3.component_type
)
GROUP BY oid
)
ORDER BY moneypertoken DESC
;

/* ALMOST! probably just need to measure moneypertoken during this select instead. */
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0), i.moneypertoken, p.moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, (SUM(IFNULL(ic.baseValue,0))/10)/SUM(IFNULL(ic.commendationCost,0)) moneypertoken, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
    --ORDER BY moneypertoken DESC, tokens DESC
) i --items
FULL JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, (SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0))/10)/SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) moneypertoken, oid, SUBSTR(MAX(oname),1,300) name, COUNT(o2name) o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, lt.itemid, MAX(ic2.baseValue) baseValue2, MAX(ic2.commendationCost) as tokens2, o2.id o2id, o2.name o2name, GROUP_CONCAT(cr3.id),GROUP_CONCAT(cr3.component_id), cr3.component_type cr3ct
        FROM Objects o
        FULL JOIN ComponentsRegistry cr ON cr.id = o.id
        FULL JOIN ComponentsRegistry cr3 ON o.id = cr3.id
        FULL JOIN ItemComponent ic ON ic.id = cr3.component_id
        FULL JOIN PackageComponent pc on pc.id = cr3.component_id
        FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        FULL JOIN Objects o2 ON lt.itemid = o2.id
        FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 11
        AND cr3.component_type IN (53)
        AND m.id IN (427,14,17,82,633)
        AND o.id > -1
        GROUP BY o.name,o2.name,cr3.component_type
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
ORDER BY i.moneypertoken DESC
LIMIT 30
;

/* NEED TO recalculate money, but the innermost SELECT for the packages is measuring baseValue wrong. */
/* see next one; the baseValue has been corrected. */
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, i.moneypertoken, p.moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, (SUM(IFNULL(ic.baseValue,0))/10)/SUM(IFNULL(ic.commendationCost,0)) moneypertoken, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
    --ORDER BY moneypertoken DESC, tokens DESC
) i --items
FULL JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, (SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0))/10)/SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) moneypertoken, oid, SUBSTR(MAX(oname),1,300) name, COUNT(o2name) o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        FULL JOIN ComponentsRegistry cr ON cr.id = o.id
        FULL JOIN ItemComponent ic ON ic.id = cr.component_id
        FULL JOIN PackageComponent pc on pc.id = cr.component_id
        FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        FULL JOIN Objects o2 ON lt.itemid = o2.id
        FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633)
        AND m.id IN (633)
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
ORDER BY i.moneypertoken DESC
LIMIT 30
;

/* this is innermost SELECT for the nearby queries, for summing value of packages */
SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, GROUP_CONCAT(ic2.baseValue) o2_baseValues
FROM Objects o
FULL JOIN ComponentsRegistry cr ON cr.id = o.id
FULL JOIN ItemComponent ic ON ic.id = cr.component_id
FULL JOIN PackageComponent pc on pc.id = cr.component_id
FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
FULL JOIN Objects o2 ON lt.itemid = o2.id
FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type = 53
AND cr2.component_type = 11
--AND m.id IN (427,14,17,82,633)
AND m.id IN (633)
AND o.id > -1
GROUP BY o.id
;

/* Closest thing I can generate. 2025-01-04-7 16:09
This shows Castle Model Pack 3 near the top.
This is the REFERENCE FUNCTION -----------------------------------------------
*/
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
FULL JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        FULL JOIN ComponentsRegistry cr ON cr.id = o.id
        FULL JOIN ItemComponent ic ON ic.id = cr.component_id
        FULL JOIN PackageComponent pc on pc.id = cr.component_id
        FULL JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        FULL JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        FULL JOIN Objects o2 ON lt.itemid = o2.id
        FULL JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        FULL JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        FULL JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
ORDER BY moneypertoken DESC
;

/* trying to remove the FULL JOIN business on that innermost loop, so I can run this on sqlite3.7 on centos 7 */
SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
FROM Objects o
LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
LEFT JOIN PackageComponent pc on pc.id = cr.component_id
LEFT JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
LEFT JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
LEFT JOIN Objects o2 ON lt.itemid = o2.id
LEFT JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
LEFT JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
LEFT JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
WHERE cr.component_type = 53
AND cr2.component_type = 11
AND m.id IN (427,14,17,82,633,1254)
AND lm.percent > 0.98 AND lm.minToDrop >=1
AND o.id > -1
GROUP BY o.id
;

--SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
SELECT o.id, o.name, ic.baseValue
FROM Objects o
LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
LEFT JOIN PackageComponent
WHERE o.id = 9723
AND cr.component_type = 53
UNION
SELECT o.*, cr.*
FROM ComponentsRegistry cr
LEFT JOIN Objects o ON cr.id = o.id
WHERE o.id = 9723
;

/* This makes duplicates, and runs for about 5 minutes on my game server machine (CentOS 7, sqlite3.7). It's a horrible design, but technically it avoids FULL JOINs but is not worth it. */
SELECT DISTINCT * FROM
(
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
LEFT JOIN (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
        LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
        LEFT JOIN PackageComponent pc on pc.id = cr.component_id
        LEFT JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        LEFT JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        LEFT JOIN Objects o2 ON lt.itemid = o2.id
        LEFT JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        LEFT JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        LEFT JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
ON i.oid = p.oid
UNION ALL
SELECT i.mid, IFNULL(i.tokens,0)+IFNULL(p.tokens,0) tokens, IFNULL(i.money,0)+IFNULL(p.money,0) money, ((IFNULL(i.money,0)+IFNULL(p.money,0))/10)/(IFNULL(i.tokens,0)+IFNULL(p.tokens,0)) moneypertoken, i.oid, i.name, p.o2count, p.o2id, p.o2names
FROM (
    SELECT mid, SUM(IFNULL(tokens,0))+SUM(IFNULL(tokens2,0)) tokens, SUM(IFNULL(baseValue,0))+SUM(IFNULL(baseValue2,0)) money, oid, SUBSTR(MAX(oname),1,300) name, o2namecount o2count, SUBSTR(GROUP_CONCAT(o2id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2name),1,300) o2names
    FROM (
        SELECT m.id mid, cr.component_id as crid, ic.baseValue, ic.commendationCost AS tokens, o.id oid, o.name oname, pc.LootMatrixIndex, SUM(ic2.baseValue) baseValue2, SUM(ic2.commendationCost) as tokens2, SUBSTR(GROUP_CONCAT(o2.id),1,300) o2id, SUBSTR(GROUP_CONCAT(o2.name),1,300) o2name, COUNT(o2.name) o2namecount, GROUP_CONCAT(ic2.baseValue) o2_baseValues
        FROM Objects o
        LEFT JOIN ComponentsRegistry cr ON cr.id = o.id
        LEFT JOIN ItemComponent ic ON ic.id = cr.component_id
        LEFT JOIN PackageComponent pc on pc.id = cr.component_id
        LEFT JOIN LootMatrix lm ON lm.LootMatrixIndex = pc.LootMatrixIndex
        LEFT JOIN LootTable lt ON lt.LootTableIndex = lm.LootTableIndex
        LEFT JOIN Objects o2 ON lt.itemid = o2.id
        LEFT JOIN ComponentsRegistry cr2 ON cr2.id = o2.id
        LEFT JOIN ItemComponent ic2 ON ic2.id = cr2.component_id
        LEFT JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
        WHERE cr.component_type = 53
        AND cr2.component_type = 11
        --AND m.id IN (427,14,17,82,633,1254)
        AND lm.percent > 0.98 AND lm.minToDrop >=1
        AND o.id > -1
        GROUP BY o.id
    )
    GROUP BY oid
) p --packages
LEFT JOIN (
    SELECT m.id mid, SUM(IFNULL(ic.commendationCost,0)) AS tokens, SUM(IFNULL(ic.baseValue,0)) money, o.id oid, o.name name, NULL, NULL, NULL
    FROM Objects o
    JOIN ComponentsRegistry cr ON cr.id = o.id
    JOIN ItemComponent ic ON ic.id = cr.component_id
    JOIN Missions m ON o.id IN (m.reward_item1, m.reward_item2, m.reward_item3, m.reward_item4)
    WHERE cr.component_type IN (11)
    --AND m.id IN(427,14,17,82,633)
    GROUP BY o.name
) i --items
ON i.oid = p.oid
ORDER BY moneypertoken DESC
)
;

  4. for a given char_xml, search all completed missions and calculate the most valuable items and token cost

Knowledge Base

tl;dr

Sql query

Inferior sql query

Narrative

Comments