Running complex queries with some functions

[ckald]

I need to run query like this:

SELECT id, CONCAT( REPEAT('—', level), ' ' , title) FROM `categories`

And I think it is obvious to do it this way:

Jelly::select('category')
    ->select_array(
        array(
            'id'=>'id',
            'title'=>"CONCAT( REPEAT('—', level), ' ' , title)"
         )
     )
     ->order_by('path')
     ->execute();

This causes error

Database_Exception [ 1054 ]: Unknown column 'CONCAT( REPEAT('—', level), ' ' , title)' in 'field list' [ SELECT `id`, `CONCAT( REPEAT('—', level), ' ' , title)` FROM `categories` ORDER BY `path` ]

The question is: does Jelly supports this feature, or i should invent it?

[bistory]

Try with array("CONCAT( REPEAT('—', level), ' ' , title)", 'title'), it should make an alias and make your query work :)

[ckald]

This helped

Jelly::select('category')
->select_array(array(
       'id',
       DB::expr("CONCAT( REPEAT('—', `level`), ' ', `title`) as title")
  )
)
->order_by('path')->execute()->as_array();

But is there any way to extract this like associative array(id => title)?

[banks]

ckald: as_array('id', 'title') this is the standard from KO DB.

[banks]

In fact I'm closing this as it is not an actual issue with Jelly