Welcome, Guest
Username: Password: Remember me

TOPIC: [FIXED] SQL error when making a sort

SQL error when making a sort 03 Jan 2017 13:28 #14896

  • lastradacook
  • lastradacook's Avatar
  • Offline
  • New Member
  • Posts: 13
  • Thank you received: 1
  • Karma: 1
Hello I have 2 errors on a collection (list) display :

First error : wrong field in ORDER
Unknown column '__itm_device_types_id__.name' in 'order clause' SQL=SELECT a.id,a.alias,a.description_tt_fr,a.description_tt_nl,a.itm_bill_id,_itm_bill_id_.bill_reference AS `_itm_bill_id_bill_reference`,a.itm_device_types_id,_itm_device_types_id_.name AS `_itm_device_types_id_name`,a.itm_manufacturers_id,_itm_manufacturers_id_.name AS `_itm_manufacturers_id_name`,a.name FROM #__jslsitmanager_devices AS a LEFT JOIN `#__jslsitmanager_bills` AS _itm_bill_id_ ON _itm_bill_id_.id = a.itm_bill_id LEFT JOIN `#__jslsitmanager_devicetypes` AS _itm_device_types_id_ ON _itm_device_types_id_.id = a.itm_device_types_id LEFT JOIN `#__jslsitmanager_manufacturers` AS _itm_manufacturers_id_ ON _itm_manufacturers_id_.id = a.itm_manufacturers_id ORDER BY __itm_device_types_id__.name ASC LIMIT 20

the error is on the order clause : __itm_device_types_id__.name
Testing the SQL request changing "__itm_device_types_id__.name" with "_itm_device_types_id_.name" (without double "_"), works

Second error : the "ordering direction" is always setted to "ASC"



I don't know if this is a correct solution (will this code breaks jcook logic ?) but I made a "partial" solution to both problems ?

I have installed the component on a local joomla for debugging.
I have forked 2 files :
++++++++++++++++++++++++++++++++
first file : list.php
++++++++++++++++++++++++++++++++
The problem : 2 underscore into orderby
original : ~/administrator/components/com_jslsitmanager/classes/model/list.php
forked : ~/administrator/components/com_jslsitmanager/fork/classes/model/list.php
<?php
// no direct access
defined('_JEXEC') or die('Restricted access');

class JslsitmanagerClassModelList extends JslsitmanagerCkClassModelList{
    
    protected function applySqlStates($query)
    {
        //Populate only uniques strings to the query

        // SELECT
        foreach($this->getState('query.select', array()) as $select)
            $query->select($select);

        // JOIN LEFT
        foreach($this->getState('query.join.left', array()) as $join)
            $query->join('LEFT', $join);

        // JOIN INNER
        foreach($this->getState('query.join.inner', array()) as $join)
            $query->join('INNER', $join);

        // JOIN OUTER
        foreach($this->getState('query.join.outer', array()) as $join)
            $query->join('OUTER', $join);

        // WHERE
        foreach($this->getState('query.where', array()) as $where)
            $query->where($where);

        // RESTRICT
        if (is_array($this->getState('query.restrict')))
        {
            $restrict = $this->getState('query.restrict');
            if (count($restrict))
                $query->where("a.id IN (" . implode(',', $restrict). ")");
            else
                $query->where("a.id = -1");  // Return nothing (restricted all)
        }

        // EXCLUDE
        if (is_array($this->getState('query.exclude')))
        {
            $exclude = $this->getState('query.exclude');
            if (count($exclude))
                $query->where("a.id NOT IN (" . implode(',', $exclude). ")");
        }

        // GROUP BY : Native SQL Group By
        foreach($this->getState('query.groupBy', array()) as $groupBy)
            $query->group($groupBy);
        
        // GROUP ORDER : Prioritary order for groups in lists
        foreach($this->getState('query.groupOrder', array()) as $groupOrder){
            //echo 'groupOrder : ' . $groupOrder.'<br>';
            $query->order($groupOrder);
        }
        
        // ORDER
        foreach($this->getState('query.order', array()) as $order){
            /* HERE IS THE PROBLEM : 2 underscores(_) instead of 1
             rapid and ugly solution : remove them hardly
            */
            $order = join('_', explode('__',$order));
            //echo 'order : ' . $order.'<br>';
            $query->order($order);
        }
        //echo $query->dump();
    }
}
+
+
++++++++++++++++++++++++++++++++
second file : orm.php
++++++++++++++++++++++++++++++++
The problem : the direction always setted to ASC
original : ~/administrator/components/com_jslsitmanager/classes/model/orm.php
forked : ~/administrator/components/com_jslsitmanager/fork/classes/model/orm.php
<?php
// no direct access
defined('_JEXEC') or die('Restricted access');

class JslsitmanagerClassModelOrm extends JslsitmanagerCkClassModelOrm
{
    /**
    * Ordering directive.
    *
    * @access    public
    * @param    array    $config    Ordering configuration.
    *
    *
    * @since    Cook 3.1
    *
    * @return    void
    */
    public function order($config)
    {
        foreach($config as $namespace => $dir)
        {
            /* HERE IS THE PROBLEM : $dir is lower cased ('asc', 'desc'), so $dir will be always converted into "ASC"
            Rapid and ugly solution : uppercase the passed "$dir" parameter
            */
            $dir = strtoupper($dir);
            if (!in_array($dir, array('ASC', 'DESC'), true))
                $dir = 'ASC';

            $fieldAlias = $this->tableFieldAlias($namespace);

            // Requirements
            $this->join($namespace);

            $this->model->addQuery('order', $fieldAlias . ' ' . $dir);
        }
    }
}
I hope this will help to find a solution.
Last Edit: 03 Jan 2017 13:35 by lastradacook.
The administrator has disabled public write access.

SQL error when making a sort 16 Jan 2017 22:40 #14947

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 987
  • Karma: 140
Fixed.

There was 2 problems.

The uppercase solution is not ugly. The SQL case convention is not defined by Joomla, but it must stay consistent conventions for all the component.
I prefer when the keywords are uppercase.
The value send trough post can be lowecase (asc / desc), this is not a problem in my sense.
Coding is now a piece of cake
The administrator has disabled public write access.
Time to create page: 0.060 seconds

Get Started