0

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?

2
  • Did my edit damage the question requirements? Do you want trim the leading zeros while joining, or in the SELECT, or both?
    – mickmackusa
    May 25 at 10:29
  • Yes it is, I need our_vendors table to track the vendor name on it because i only have the id of the vendor on the cert_pack_fcm. so to get the name i name to connect it with our_vendors. but the problem is i just want to remove leading zeros on it. May 25 at 17:13

2 Answers 2

0

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;
}
1
  • I interpreted the requirement to be casting in the LEFT JOIN. Or maybe you are right.
    – mickmackusa
    May 25 at 10:33
0

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 offucial advice. For developers that are uncertain about sql stability/security, there is no harm in quoting everything.

$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')

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.