How to re-write sql for joomla JDatabase

Question

I am new to custom Joomla coding so excuse me for this, I want to execute the following statement:

"SELECT 
    MAX(CASE WHEN v.FieldName = 'Team_Name' THEN v.FieldValue END) 'Team_Name'
    , MAX(CASE WHEN v.FieldName = 'Manager_Name' THEN v.FieldValue END) 'Manager_Name'
FROM `team_values` v  
where tournamentId=5"

but I am not sure how to write it, tried many scenarios but they didn't work out for me.

Answer 1

I think you are asking, Joomla code to execute query ?

if yes

$db = JFactory::getDBo();
$query = $db->getQuery(true);
$query->select("MAX(CASE WHEN v.FieldName=".$db->q('Team_Name')." THEN v.FieldValue END) as Team_Name, MAX(CASE WHEN v.FieldName = ".$db->q('Manager_Name')." THEN v.FieldValue END) as Manager_Name")->from('#__team_values as v')->where('v.tournamentId='.$db->q(5));
$db->setQuery($query);
$final_data = $db->loadObject();

Answer 2

If you are new, then I'd like to include some basic error checking as well as printing out the generated query and the 2-column resultset.

Untested Code:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select(
                    array(
                        "MAX(CASE WHEN v.FieldName = 'Team_Name'
                                  THEN v.FieldValue END) 'Team_Name'",
                        "MAX(CASE WHEN v.FieldName = 'Manager_Name'
                                  THEN v.FieldValue END) 'Manager_Name'"
                    )
                  )
                ->from("#__team_values v")
                ->where("v.tournamentId = 5");
    echo $query->dump();    // only if you  wish to see what is generated. Of course, don't do this on your live/public site
    $db->setQuery($query);
    echo "<pre>";
        var_export($db->loadAssocList());  // display the resultset
    echo "</pre>";
} catch (Exception $e) {
    echo "<div>", $e->getMessage(), "</div>";  // show error, if any.  Of course, don't do this on your live/public site
}

Because your query doesn't use any variables or mysql reserved keywords, you can reduce code bloat by omitting the $db->q() and $db->qn() calls for quoting tablenames, columns, and values. The integer 5 doesn't need to be single-quote wrapped.

I prefer to chain -> query methods from getQuery so that $query doesn't need to be written out again.

If you want to see a similar question where I answered with a fully joomla-quoted query, read this post.

I do have uncertainties/concerns...

This is just a reproduction of your sql using Joomla sql method calls. I am not entirely certain that this query will function as you intend. If you provide an SQLFiddle Demo of your table structure/data and explain your expected result, I will be able to verify or correct the query itself. loadRowList(), loadAssocList(), and loadObjectList() generate a multi-row resultset, but if you only expect a single row, then loadRow(), loadAssoc(), or loadObject() will be better suited.

MAX() is an odd choice if the FieldValue is non-numeric; perhaps this is not best for your query. Maybe you can enlighten us as to why you are using MAX(). If the FieldValue values are numeric, then I have concerns about your decision to name the FieldName values [word]_Name -- I mean, perhaps [word]_id would be more appropriate.