Join tables where one column value has leading zeros and the other doesn't

Question

I am trying to LEFT JOIN a table, but the two columns used for relating rows are not identically formatted.

My problem is the value in a.vendor, it has leading zeros on it, and c.No doesn't have leading zeros. I tried to make it int to remove the zero, but my code int('a.vendor') is not working.

$query
    ->select(array('a.*'))
    ->select($db->quoteName('c.Name', 'vendor_name'))
    ->select($db->quoteName('d.name', 'vendor_subcontractor'))
    ->from($db->quoteName('cert_pack_fcm', 'a'))
    ->join('LEFT', $db->quoteName('our_vendors', 'c') . ' ON ' . $db->quoteName('a.vendor' )  . ' = ' . $db->quoteName('c.No'))

What should I do?

Answer 1

There are several ways you can do this. First, you already have the correct value in c.No so you can just select it instead of a.vendor:

$query->select(['a.*', 'c.No as vendor']);

Using this method you might want to declare specific columns to prevent unnecessary data (i.e. a.vendor) from being sent:

$query->select(['a.column1', 'a.column2', 'c.No as vendor']);

Another method is to cast the column to integer:

$query->select(['a.*', $query->castAs('INT', 'a.vendor') . ' AS vendor'])

Same advice about declaring columns applies here.

Third method is to cast the value in PHP after the data has been retrieved:

$results = $db->loadObjectList();

foreach ($results as $row)
{
    $row->vendor = (int) $row->vendor;
}

Answer 2

I am assuming that your concern is regarding the join of the our_vendors table. One viable, raw SQL technique is to explicitly trim zeros from the front of each value with the SQL TRIM() function.

TRIM(LEADING '0' FROM myfield)

Alternatively, as shown in @Sharky's answer, you can use a Joomla-native approach (castAs()) which converts the zero padded string to an integer (which by definition cannot have leading zeros).

None of your table names or column names pose any risk to query stability or security, so it is actually perfectly safe to remove all of those quoteName() method calls which make the query building script longer and harder to read. This is more of a personal preference than official advice. For developers that are uncertain about sql stability/security, there is no harm in quoting everything.

$query = $db->getQuery(true);  // must be declared so that chaining can re-reference $query for castAs() method
$query
    ->select('a.*, c.Name AS vendor_name, d.name AS vendor_subcontractor')
    ->from('cert_pack_fcm AS a')
    ->leftJoin('our_vendors AS c ON ' . $query->castAs('INT', 'a.vendor') . ' = c.No')

Note: see Issues to be aware of when calling concatenate(), regexp(), rand(), and castAsChar() in sql queries regarding declaring $query so that its methods can be used while chaining the rest of the query building methods.