I'm using the Connect plugin by Andrew Welch to get some data from an external database. Somewhere in the data, I need data from another related table. The way I'm doing this now is like this:
{% set games = craft.connect.query(db)
.select(['*'])
.from('games')
.where(['and',{'LevelID': '15'},{'SeasonID': '2019'}])
.limit(99)
.all()
%}
{% for game in games %}
{# use lots of data from the games table #}
{# ... #}
{% set home = craft.connect.query(db)
.select(['Name'])
.from('teams')
.where({'ID': game.TeamA})
.one() %}
{{ home.Name }}
{% endfor %}
but I guess there must be a better way? Where I combine both queries? Or something like that?
Also, I would like to exclude results where the value of the column named "Status" is NOT "a" and NOT "b" (combined with the LevelID and SeasonID clause). How do I do this?