Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ORM support for databases which do not allow the LIMIT clause
  • mtmacdonaldmtmacdonald
    I notice the ORM library inserts the LIMIT clause into generated queries. For example:

      DELETE FROM users WHERE UserId = '9' LIMIT 1

    The LIMIT keyword doesn't seem to be SQL standard syntax, or supported by all database vendors: http://troels.arvin.dk/db/rdbms/#select-limit
      
    E.g. SQL Anywhere only supports the LIMIT clause in a SELECT statement, not in UPDATE or DELETE.

    E.g. MS SQL Server:

    As a workaround, I notice I can hack packages/orm/classes/query.php to remove the LIMIT clause:

     public function limit($limit)
      {
       //$this->limit = intval($limit);

       return $this;
      }
      
    But is there a better way? Are there any plans to make the ORM layer more database-agnostic by removing non-standard SQL clauses?
  • HarroHarro
    Accepted Answer
    Yes, that is on the roadmap when we switch from the old Kohana QB to our new QB (which is fully platform independent).

    You wouldn't want to remove the limit() functionality, as that will also remove it from SELECT statements and will render the ORM virtually useless.

    It has to be removed from the UPDATE and DELETE methods. I can only assume it's in there because in the "old" days you could call the method without an id, causing it to generate a "DELETE ALL". The second reason for it being there is that it's faster on a non-index column, as it stops after the first operation, instead of doing a table scan to see if more values match.
  • mtmacdonaldmtmacdonald
    I've improved the temporary workaround to follow your advice. In packages/orm/classes/query.php:


     public function build_query(\Fuel\Core\Database_Query_Builder_Where $query, $columns = array(), $type = 'select')
     {
      // Get the limit
      if ( ! is_null($this->limit))
       {
         if ($type != 'update' && $type != 'delete') //added exclusion for update and delete queries
         {
          $query->limit($this->limit);
         }
       }
       
      ....
      }
     
    Looking forward to the new query builder.
  • HarroHarro
    You can already have a look: http://github.com/FrenkyNet/Cabinet.
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion