DB Query with JOIN in Task Plugin

Question

I'm calling the following query to retrieve records and the primary table data elements are all included, but the JOIN elements of data are not included. Any suggestions what I'm missing?

private function getRecordsOfMbrs($endDate)
{
    $db    = $this->getDatabase();
    $query = $db->getQuery(true);
    $query->select('a.*, b.title, b.term_type, b.term_dur, u.name, u.email');
    $query->from($db->quoteName('#__mycomponent_records', 'a'));
    $query->join('LEFT', $db->quoteName('#__mycomponent_rectypes', 'b') . ' ON ' . $db->quoteName('b.id') . ' = ' . (int) $db->quoteName('a.type_id'));
    $query->join('LEFT', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('u.id') . ' = ' . (int) $db->quoteName('a.user_id'));
    $query->where($db->quoteName('a.end_date') . ' = ' . $db->quote($endDate));
    $query->where($db->quoteName('a.state') . ' = ' . (int) 2);
    $db->setQuery($query);
    try {
        $recMbrs = $db->loadObjectList();
        $this->logTask('Retrieved records for members.', 'notice');
        return $recMbrs;
    } catch (RuntimeException $e) {
        $this->logTask($e->getMessage());
        return Status::KNOCKOUT;
    }
}

If I use this query in the database directly, all is returned as expected.

SELECT a.*, b.title, b.term_type, b.term_dur, u.name, u.email
FROM `uj2k9_mycomponent_records` a
LEFT JOIN `uj2k9_mycomponent_rectypes` b ON b.id = a.type_id
LEFT JOIN `uj2k9_users` u ON u.id = a.user_id
WHERE a.end_date = '2024-04-24'
AND a.state = 2

Answer 1

You are casting the column name to integer in PHP, which always results in 0:

(int) $db->quoteName('a.type_id')

This results in query like this:

LEFT JOIN `uj2k9_mycomponent_rectypes` b ON b.id = 0

If you need to cast column values, use $query->castAs():

$query->castAs('INT', $db->quoteName('a.type_id'))

Answer 2

Your script is doing several things that it doesn't need to and a few things that are actively harmful to your process. Casting a column name to an integer doesn't make any sense, but I suspect you are just trying to implement some security measure that you've read elsewhere but in a different context.

Not only do you not need to cast a hardcoded integer as an integer, it damages your query to cast column names as integers.

Here's a rewrite where I've removed all of the non-helpful calls of quoteName(). The script becomes functional and also profoundly easier to read for humans.

private function getRecordsOfMbrs(string $endDate)
{
    $db = $this->getDatabase();
    $query = $db->getQuery(true)
        ->select('a.*, b.title, b.term_type, b.term_dur, u.name, u.email')
        ->from('#__mycomponent_records a')
        ->join('LEFT', '#__mycomponent_rectypes b ON b.id = a.type_id')
        ->join('LEFT', '#__users u ON u.id = a.user_id')
        ->where('a.end_date = ' . $db->quote($endDate))
        ->where('a.state = 2');
    $db->setQuery($query);
    try {
        $this->logTask('Retrieved records for members.', 'notice');
        return $db->loadObjectList();
    } catch (RuntimeException $e) {
        $this->logTask($e->getMessage());
        return Status::KNOCKOUT;
    }
}