Drupal's way of getting content is by using views. You should create a view (Structure -> Views -> Add view from admin menu) to get that data. It's something like visual interface for creating database queries, and it's not hard as it might look at first sight.
Then, there are few way how to use that view.
- View can create a page, or a block, so content will appear on the page
- You can execute view from your controller and collect data view returned and pass it to twig template to render the data.
- You could embed view directly from twig, so no need for any kind of coding inside page controller.
Option 1 with "page view" (it's display type is "page") is probably easiest one, but doesn't fulfill request of custom controller.
With option 2 you could use: views_get_view_result() call to get results:
https://api.drupal.org/api/drupal/core%21modules%21views%21views.module/function/views_get_view_result/8.2.x
It should look something like:
$view = Views::getView('view_machine_name');
$view->execute();
foreach ($view->result as $row) { // iterating trough rows of results
// Do something with $row
}
This option matches the best your request.
For option 3 you would have to Twig Tweak module to embed view from twig:
https://www.drupal.org/docs/8/modules/twig-tweak/twig-tweak-and-views
Beside that you could of course create custom database query, but that's not so "drupalish" way and queries can be pretty complicated since drupal's database structure is not the simplest one and you could get lost.
