TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
ORM function with() causes SQL ERROR: Column alias not enclosed with apostrophe ` ?!
  • Hey everyone,

    I downloaded version 3.2 yesterday.

    I've managed to let the ORM build some query, wich actually doesn't do yet what I'm lookin for BUT the query has an SQL Syntax error. The query the ORM tries to execute is:

    SELECT 
       content_language.id AS content_language:id, 
       content_language.content_id AS content_language:content_id, 
       content_language.language_id AS content_language:language_id, 
       content_language.title AS content_language:title, 
       content_language.heading AS content_language:heading, 
       content_language.content AS content_language:content, 
       content.* 
    FROM 
       content AS content 
    LEFT JOIN 
       content_language AS content_language ON (content.id = content_language.content_id)
    

    If I alter the query and execute directly in phpMyAdmin, by placing ` apostrophes around the column aliases, everything works fine:

    SELECT 
       content_language.id AS  `content_language:id`, 
       content_language.content_id AS  `content_language:content_id`,
       content_language.language_id AS  `content_language:language_id`, 
       content_language.title AS  `content_language:title`,
       content_language.heading AS  `content_language:heading`, 
       content_language.content AS `content_language:content`, 
       content . * 
    FROM 
       content AS content
    LEFT JOIN 
       content_language AS content_language ON ( content.id = content_language.content_id ) 
    

    What am I, or Kohana, doing wrong? Is this a bug, or a setting somewhere?

    Thanks for your time!

  • is using ":" valid? never ever read about this!? and dont forget to post the description of the syntax error smartiepants

  • This is the syntax error:

    Database_Exception [ 42000 ]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':id, content_language.content_id AS content_language:content_id, content_languag' at line 1 [ SELECT content_language.id AS content_language:id, content_language.content_id AS content_language:content_id, content_language.language_id AS content_language:language_id, content_language.title AS content_language:title, content_language.heading AS content_language:heading, content_language.content AS content_language:content, content.* FROM content AS content LEFT JOIN content_language AS content_language ON (content.id = content_language.content_id) ]
    

    I'm using this in my controller, and that will build the wrong query:

    $content_pages = ORM::factory('content')->with('content_language')->find_all();
    

    Edit:

    Using ":" is valid I think as long as you wrap it in apostrophes, what the query builder 'forgets' ?

    The ORM uses ":" to indicate it is a column of another model, if I understand the code well enough

  • i guess this is a bug, 'content_language:id' isn't a valid fieldname for MySQL query syntax if im right

  • Can I bump this discussion a bit?

    Is the ORM forgetting the ` characters when spitting out the mysql query?

  • Well I fixed it myself, don't know if it is the good way to do it though:

    In \modules\orm\classes\kohana\orm.php I changed around line number 816, in function with(): $alias = $target_path.':'.$column;

    Into this: $alias = ''.$target_path.':'.$column.'';

    And there we go, my query run by the ORM is now like this:

    SELECT content.id AS `content:id`, content.parent_content_id AS `content:parent_content_id`, content.is_active AS `content:is_active`, content_language.* FROM content_language AS content_language LEFT JOIN content AS content ON (content.id = content_language.content_id) WHERE content.`is_active` = 1 AND language_id = 1 LIMIT 1
    

    And it runs without problems, because the aliases are now enclosed with `` and the ORM gives objects to me :)

  • Please put this on the issue tracker! It helps us all :D

  • If you are using PDO, add the "identifier" option to your database config:

    'identifier' => '`'

    Shall fix the Problem.

Howdy, Stranger!

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

In this Discussion