Using a table-alias in Kohana queries?

Question

I'm trying to run a simple query with $this->db in Kohana, but am running into some syntax issues when I try to use an alias for a table within 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 just fine. I'm stating that "chapter_info" ought to be known as "ci," yet this isn't taking for some reason. The error is pretty straight-forward:

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, rather than an alias, I get the expected results without error. This requires me to write much more verbose queries, which isn't ideal.

Is there some way to use shorter names for tables within Kohana's query-builder?

Answer 1

Try using the "as" keyword like ->from("chapter_info as ci"), maybe the query builder will recognize it this way.

Answer 2

$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. As you must wrap the original table name in quotes first before the AS keyword and the new table name you want to shorten it to.