We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Query Builder Issues (dots in columns name, resultset problems)

Hi, I have some issues with Phalcon query builder, issues are critical and I've tried many times to find a logical solution for them, but nothing.

First Issue

I think this is a bug! however...

I want get a column name like title as foo.title, look at sample code:

$options = ['...'];
$builder = $this->modelsManager->createBuilder();
$builder->from(['aircrafts' => 'App\Models\Aircrafts']);
$modelAlias = 'aircrafts';
$columns = [];
if($options['columns']) {
    foreach( $options['columns'] as $column ){
        columns[] = $column . ' as `' . $modelAlias . '.' . $column . '`';
    }
}

# sample query
# SELECT id as `aircrafts.id`, title as `aircrafts.title` FROM ...

Ok, as you know in mysql this query is valid, but seems phalcon does not offer any way to do this.

Backticks (`) in phalcon query builder cause query scan error, how could use dot (.) in phalcon columns name?

Second Issue

Query builder resultset issue

Sample code:

$builder = $this->modelsManager->createBuilder();
$builder->from(['seatmap' => 'App\Models\SeatMaps']);
$builder->join('App\Models\Aircrafts', null, 'aircrafts');
$builder->join('App\Models\Airlines', 'aircraft.airline = airline.id', 'airline');
// Set columns
$builder->columns('seatmap.*, aircrafts.*, airline.title');
// ...Execute Query...

query result will be something like:

    // Complex Resultset   
    [
        'seatmaps' => {
            'id': '...',
            'title' : '...',
            // ..
        },
        'aircrafts' => {
            'id': '...',
            'title' : '...',
            // ...
        },
        'title' => '...' // this is airline title
    ]

as you see, in this case I need title column from airlines table and all columns from seatmaps (seatmaps.*) and aircrafts (aircrafts.*) models. how its possible to get data in following format:

    // Complex Resultset   
    [
        'seatmap' => {
            'id': '...',
            'title' : '...',
            //...
        },
        'aircrafts' => {
            'id': '...',
            'title' : '...',
            //...
        },
        // title belongs to airlines, so i want it be a child of airlines object
        'airlines' => {
            'title' => '...'
        }
    ]

how can I do this, fetch all columns in their related object in query builder resultset?

edited Nov '15

First issue

// fields
$fields = array(
    'News.id AS itemID',
    'Translation.title',
    'News.is_active',
    'News.created_at',
);

// query builder shizzles
$builder = $this->modelsManager->createBuilder();
$builder->columns($fields);
$builder->from(array('News' => 'Models\News'));
$builder->leftJoin('Models\NewsI18n', 'Translation.foreign_key = News.id', 'Translation');

// Result after dump
$builder->getQuery()->execute()->toArray();
Array
(
    [0] => Array
        (
            [itemID] => 11
            [title] => test novina 22
            [is_active] => 1
            [created_at] => 2015-10-31
        )
....

Second issue

Try not to pass columns to the builder? Im not sure about this and dont have the setup to test at the moment. Sorry.

No. this is not.

for the first issue I want get a column name as another.name.that.contains.dots for example:

In second issue, is I don't pass columns name it's Ok, but Consider a query with many joins, in the result there are many Non-Needed data!!