Implement SELECT query with a LEFT JOIN in J4

Question

I want to get data from two tables using a LEFT JOIN. My query works in my current PHP code, I just don't know how to implement it with query building methods in J4. Can anyone help?

SQL:

SELECT `nazwa`,
       `powierzchnia`,
       `state`,
       `stawka`,
       `partycypacja`,
       `skladka`,
       `woda`,
       `smieci`,
       `energia`,
       `inwestycyjna`
FROM dzialki
LEFT JOIN dane ON dzialki.id = dane.id
WHERE dzialki.state=1
ORDER BY dzialki.id ASC;

My tables:

dzialki
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| nazwa        | varchar(50) | NO   |     | NULL    |                |
| powierzchnia | varchar(50) | NO   |     | NULL    |                |
| state        | tinyint(1)  | NO   |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
    

and

dane
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| stawka       | decimal(9,2) | NO   |     | NULL    |                |
| partycypacja | decimal(9,2) | NO   |     | NULL    |                |
| skladka      | decimal(9,2) | NO   |     | NULL    |                |
| woda         | decimal(9,2) | NO   |     | NULL    |                |
| smieci       | decimal(9,2) | NO   |     | NULL    |                |
| energia      | decimal(9,2) | NO   |     | NULL    |                |
| inwestycyjna | decimal(9,2) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Answer 1

In Joomla 4 to get data from two MySQL tables, you can use some code like:

use Joomla\CMS\Factory;

$db    = Factory::getContainer()->get('DatabaseDriver');
$query = $db->getQuery(true)
    ->select($db->quoteName(['nazwa', 'powierzchnia', 'state', 'stawka', 'partycypacja', 'skladka', 'woda', 'smieci', 'energia', 'inwestycyjna']))
    ->from($db->quoteName('#__dzialki', 'dzialki'))
    ->leftJoin(
        $db->quoteName('#__dane', 'dane') 
        . ' ON ' . $db->quoteName('dane.id') . ' = ' . $db->quoteName('dzialki.id')
    )
    ->where($db->quoteName('dzialki.state') . ' = 1')
    ->order('dzialki.id ASC');
$db->setQuery($query);

// The result set as an array of objects
$results = $db->loadObjectList();

// Display the actual SQL query
echo $query->dump();