search

averbraeck / housinggame-facilitator

Facilitator app for the housing game
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

SQL error when retrieving news table #6

Closed averbraeck closed 11 months ago

averbraeck commented 11 months ago

Message:

SQL [SELECT newsitem.id FROM newsitem INNER JOIN round ON newsitem.round_id=round.id 
    WHERE round.scenario_id=4 ORDER BY round_number DESC;]; Table 'housinggame.round' doesn't exist

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

org.jooq.exception.DataAccessException: SQL [SELECT newsitem.id FROM newsitem 
   INNER JOIN round ON newsitem.round_id=round.id 
   WHERE round.scenario_id=4 ORDER BY round_number DESC;]; Table 'housinggame.round' doesn't exist
averbraeck commented 11 months ago

Since the database retrieval was based on an SQL string rather than on a jOOQ statement, it was not flagged as being outdated after the database change. The code has been changed into:

List<NewsitemRecord> resultList =
        dslContext.selectFrom(Tables.NEWSITEM)
                .where(Tables.NEWSITEM.SCENARIO_ID.eq(data.getScenario().getId()))
                .and(Tables.NEWSITEM.ROUND_NUMBER.eq(data.getCurrentRoundNumber())).fetch()
                .sortDesc(Tables.NEWSITEM.ROUND_NUMBER);

After this change, the results are as expected:

image

If needed, the 'eq' for the round number can be replaced by 'le' to show news item of the current round and previous rounds.