CODEWITHSUNDEEP
mysqldatabasesyntaxkohana
Aristotle
Aristotle

Reputation: 1102

Using a table-alias in Kohana queries?

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?

Upvotes: 4

Views: 3228

Answers (3)

XeoKeri
XeoKeri

Reputation: 40

$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.

Upvotes: 0

Tadeck
Tadeck

Reputation: 137360

In Kohana 3 it is simply enough:

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

and this will create the query that contains:

FROM 'table_name' AS 'alias'

I have tested it and it works. Good luck.

Upvotes: 7

Leventix
Leventix

Reputation: 3859

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

Upvotes: -1

Related Questions