Using table alias in Kohana queries?

Question

I am trying to run a simple query with $this->db in Kohana, but I am encountering some syntax problems when I try to use an alias for a table in my query:

 $result = $this->db ->select("ci.chapter_id, ci.book_id, ci.chapter_heading, ci.chapter_number") ->from("chapter_info ci") ->where(array("ci.chapter_number" => $chapter, "ci.book_id" => $book)) ->get(); 

It seems to me that this should work fine. I argue that "chapter_info" should be called "qi", but for some reason this is not perceived for some reason. The error is pretty simple:

 There was an SQL error: Table 'gb_data.chapter_info ci' doesn't exist - SELECT `ci`.`chapter_id`, `ci`.`book_id`, `ci`.`chapter_heading`, `ci`.`chapter_number` FROM (`chapter_info ci`) WHERE `ci`.`chapter_number` = 1 AND `ci`.`book_id` = 1 

If I use the full table name and not an alias, I get the expected results without errors. This requires me to write much more detailed queries, which is not ideal.

Can shorter table names be used in the Kohana query builder?

Answer 1

Try using the "as" keyword, for example ->from("chapter_info as ci") , perhaps the query designer will recognize it this way.

Answer 2

In Kohana 3, this is quite simple:

 ->from( array('table_name', 'alias') ) 

and this will create a query containing:

 FROM 'table_name' AS 'alias' 

I tested it and it works. Good luck.

Answer 3

 $result = $this->db ->select("ci.chapter_id, ci.book_id, ci.chapter_heading, ci.chapter_number") ->from("'chapter_info' AS ci") ->where(array("ci.chapter_number" => $chapter, "ci.book_id" => $book)) ->get(); 

That should work. Since you must enclose the original table name in quotation marks before the AS keyword and the new table name, you want to shorten it.