1

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.

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();
1

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.