A couple SQLDelight questions (1.4.4, Android): 1....

# squarelibraries

[Brady Aiello]

A couple SQLDelight questions (1.4.4, Android):
1. After creating a CREATE VIEW statement, does it need to be declared as a method to be generated, and then invoked at runtime?
2. Is there any limitation to the number of `VIEW`'s or nested queries you can create?

[Brady Aiello]

For some context, I have some SQL to create a View:

CREATE VIEW spellsWithClasses AS
   SELECT spells.*, GROUP_CONCAT(spells.className, ", ") classes
    FROM
    (
        SELECT Spell.*, Class.className
        FROM Spell JOIN Class ON Spell.name = Class.name
        ORDER BY Spell.name, Class.className ASC
    ) spells
GROUP BY spells.name;

And to query it:

getSpellsWithClassesSortedByName:
SELECT spellsWithClasses.*,
       GROUP_CONCAT(conditionInflict) conditionsInflict
FROM
spellsWithClasses LEFT JOIN ConditionInflicts ON spellsWithClasses.name = ConditionInflicts.name
GROUP BY spellsWithClasses.name
ORDER BY spellsWithClasses.name;

This query returns an NPE. Running the same query (I'm leaving out some columns for clarity) with the Database Inspector gives me what I'm expecting:

[Brady Aiello]

Putting it all into one SQL statement produces the same effect: NPE on device, correct execution from the DB inspector.

getSpellsWithClassesSortedByName:
   SELECT spells.*, GROUP_CONCAT(spells.className, ", ") classes, GROUP_CONCAT(conditionInflict)
    FROM
    (
        SELECT Spell.*, Class.className, ConditionInflicts.conditionInflict
        FROM Spell JOIN Class ON Spell.name = Class.name LEFT JOIN ConditionInflicts ON Spell.name = ConditionInflicts.name
        ORDER BY Spell.name, Class.className ASC
    ) spells
GROUP BY spells.name
ORDER BY spells.name;

[Brady Aiello]

Couldn't get an answer to this, so I refactored my tables to avoid 3-way joins and views in general. If anyone knows why CREATE VIEW doesn't happen automatically, like CREATE TABLE statements, or knows limitations on nested SELECT statements or 3-way `JOIN`s, I'd still really like to know.

[leandro]

Did you add the view as part of a migration?

[Brady Aiello]

No

[leandro]

try adding the view as part of one