CODEWITHSUNDEEP
phpsqlkohana
neolaser
neolaser

Reputation: 6907

Kohana 3.3 Custom SQL with ORM model

I am trying to execute a custom SQL statement within Kohana. I have an ORM model that I have already used extensively throughout the site, but the need for an advanced query has arose. Is there a way to execute the query using the ORM model? An example of a query is

SELECT
    sum(TotalSales) AS sum,
    DATE(adddate(SaleDate, INTERVAL (6-weekday(SaleDate)) DAY)) AS dt
FROM Sales
GROUP BY dt
ORDER BY dt DESC

Upvotes: 0

Views: 3451

Answers (2)

Michal M
Michal M

Reputation: 9480

Such a statement should not (and probably can't) be done via Kohana's ORM. ORM is designed to handle table rows as objects and manage relations between those.

Anyway, in Kohana you can use Kohana's excellent query builder approach, e.g.:

$result = DB::select(
        array(
            DB::expr('sum(`TotalSales`)'),
            'sum'),
        array(
            DB::expr('DATE(adddate(`SaleDate`, INTERVAL (6-weekday(`SaleDate`)) DAY))',
            'dt')))
    ->from('Sales')
    ->group_by('dt')
    ->order_by('dt', 'DESC')
    ->execute();

Then simply:

$rows = $result->as_array();

It's important to remember that you should use DB::expr() for expressions in the query builder - this is new from Kohana 3.3.0.

Upvotes: 0

Jakub Kania
Jakub Kania

Reputation: 16477

Kohana has methods for direct access to db (http://kohanaframework.org/3.0/guide/api/DB#query). Just create a function like so:

$query = DB::query(Database::SELECT, 'SELECT
    sum(TotalSales) AS sum,
    DATE(adddate(SaleDate, INTERVAL (6-weekday(SaleDate)) DAY)) AS dt
FROM Sales
GROUP BY dt
ORDER BY dt DESC');

$qresult = $query->execute();

Upvotes: 1

Related Questions