0

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    |                |
+--------------+--------------+------+-----+---------+----------------+
3
  • Welcome to JSE and thank you for your question. Please take our tour to familiarize yourself with this Stack Exchange community.
    – pe7er
    Apr 15 at 11:34
  • In the future, @Dariusz, please show your best coding attempt and/or proof of research/toil. I can't upvote unless there is proof of effort. Are you not using table prefixes in your Joomla database table names?
    – mickmackusa
    Apr 15 at 11:48
  • @Dariusz This question has unfairly become a moving target. Because the question requirements have been extended, pe7er's (previously correct) answer has become insufficient. Please rollback your question (by clicking on the blue "edited [time] ago" link) to a previous version when your question was purely about using Joomla methods to build the query. Please ask a new question containing your new requirements.
    – mickmackusa
    Apr 17 at 22:22

1 Answer 1

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();
2
  • Is $db = JFactory::getDbo(); obsolete in J4? Is this why you are using $db = Factory::getContainer()->get('DatabaseDriver');?
    – mickmackusa
    Apr 15 at 12:48
  • No, it's not obsolete but deprecated and will be removed in the future. See the getDbo() function in /libraries/src/Factory.php which says it's deprecated and to "Load the database from the dependency injection container" instead.
    – pe7er
    Apr 15 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.