Nooku Request Problem

Question

I am using Nooku framework and Joomla CMS and MySQL database to create an application , and I need to retrieve a certain data from another table into the dropdownmenu of my table , using Nooku Framework , Model Part

The difference in Nooku is that request aren't written in SQL way , and so i want to know if you please how can i write this request : "SELECT DISTINCT Libelle_Pays from a3n4v_sgrmre_reclamations r, a3n4v_sgrmre_pays p where r.Code_Pays = p.code_pays"

Thank you so much

Answer 1

To do this you need to create a model for your component and put it in your component model folder. Nooku is a by convention framework ensure you understand the naming conventions. You can find more information in the Nooku guides.

Here is the code for your model (Nooku Framework 2.x) :

class ComSgrmreModelReclamations extends KModelDatabase
{ 
    protected function _buildQueryColumns(KDatabaseQueryInterface $query)
    {  
        $query
            ->distinct();
            ->columns(array('tbl.Libelle_Pays'))

    }        

    protected function _buildQueryJoins(KDatabaseQueryInterface $query)
    {
        $query->join(array('pays' => 'sgrmre_pays'), 'pays.Code_Pays = tbl.code_pays', 'LEFT'):

    }
}

The resulting query generated by this code would be :

SELECT DISTINCT Libelle_Pays FROM a3n4v_sgrmre_reclamations AS tbl LEFT JOIN a3n4v_sgrmre_pays AS pays WHERE tbl.Code_Pays = pays.code_pays

The $query object passed in is a KDatabaseQuerySelect object in this case and provides a OO wrapper to create SELECT statements.

---

The name of the table is based on the name of the model class name, eg sgrmre_reclamations. The Joomla database prefix is automatically added by Nooku and doesn't need to be included.

---

The tbl is the shortcut for the table base table that is being queried; it's available everywhere when creating a query.

---

The DISTINCT is done in the when creating the columns to ensure it's not included when generating a COUNT query. If you want the count query to take the distinct into account you need to move to the joins or where method.

---

Th type of join is LEFT by default, and the third parameter can be omitted; or any other type of join can be specified.

References :

1. KDatabaseQuerySelect
2. KModelDatabase