Convert from MySQL query using SUBSTRING_INDEX into Joomla API query?

Question

I have a query which gets the information that I want using SUBSTRING_INDEX in MySQL. I am trying to convert this to use the Joomla query building syntax, but I am not sure how it should be formatted?

Here is my query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`file_url`, '/', -1), ".", 1),"-",1) AS product_sku, `virtuemart_media_id`, `virtuemart_product_id` FROM `jos_virtuemart_medias` INNER JOIN `jos_virtuemart_products` ON SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`jos_virtuemart_medias`.`file_url`, '/', -1), ".", 1),"-",1) = `jos_virtuemart_products`.`product_sku` WHERE 1

I'm getting stuck at the first hurdle as I really can't figure out how to implement the SELECT statement?

Any advise would be most appreciated? I have had a look at the Joomla docs, but it doesn't cover the inclusion of MySQL functions.

Answer 1

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("b.product_sku, a.virtuemart_media_id, a.virtuemart_product_id")
                ->from("#__virtuemart_medias a")
                ->innerJoin("#__virtuemart_products b ON a.SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.file_url, '/', -1), '.', 1), '-', 1) = b.product_sku");
    $db->setQuery($query);
    if (!$results = $db->loadAssocList()) {
        echo "No Qualifying Results";
    } else {
        echo "<table>";
            echo "<tr>";
                echo "<th>Product SKU</th>";
                echo "<th>Media ID</th>";
                echo "<th>Product ID</th>";
            echo "</tr>";
            foreach ($results as $row) {
                echo "<tr>";
                    echo "<td>{$row['product_sku']}</td>";
                    echo "<td>{$row['virtuemart_media_id']}</td>";
                    echo "<td>{$row['virtuemart_product_id']}</td>";
                echo "</tr>";
            }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "Syntax Error";
    // echo $query->dump();    // don't show actual details to the public
    // echo $e->getMessage();  // don't show actual details to the public
}

• Full disclosure: I've never used virtuemart; I don't know if these columns exist or if this query logic will be successful. I am merely converting your posted query.
• Because product_sku in your JOIN contains your desired file_url substring, we'll just use that table's column value to avoid that nasty triple-SUBSTRING_INDEX() call in the SELECT clause.
• By assigning table aliases (a & b) you can reduce query bloat.
• I don't know if there is a cleaner way to isolate the file_url substring because I don't know what format that data is in -- I must say it looks rather heavy-handed though.