ASTEXT not working if Class::find() is called

Yii 2.0 General Discussions
1

Hello,

I am trying to make an SQL call using pagination as in the standard example including a call to a MYSQL geometry object.

https://www.yiiframework.com/doc/guide/2.0/en/start-databases

Class is a substitute for the name of the Class extending ActiveRecord.

The call to $query = Class::find() does seem to strip out the astext(LATLON) component in the query

or astext(LATLON) as bla in the Controller.php. No error is triggered it is just missing.

If the call is just to SELECT (‘LATLON’) the binary object is passed along in the view.

    $bloerb = $query->select(['AsText(LATLON) as BLA'])
    ->orderBy('NAME')

<?php foreach ($bloerb as $bloer): ?>
    <li>
<!= $bloer->BLA ?>
    </li>
<?php endforeach; ?>

<!= $bloer->BLA ?> throws an error

    $bloerb = $query->select(['LATLON'])

<!= $bloer->BLA ?> throws no error

I tried
$query = Class::findBySql

but it doesn’t let me put pagination on it.

I tried:

public function beforeFind()
in the model was a suggestion i think from Yii 1.1

But that didn’t help. No effect.

From the avrious solutions when googling nothing seemed to work in a straightforward manner. Class -> Controller -> view as soon as astext is called.

What worked was a function in the Model

public static function getList2()
{
     $query = Class::find();
    $pagination = new Pagination([
        'defaultPageSize' => 25,
        'totalCount' => $query->count(),
    ]);

$rows = (new \yii\db\Query())
        ->select(['AsText(LATLON') as BLA'])
        ->offset($pagination->offset)
        ->limit($pagination->limit)
        ->limit(25)
        ->all();
return $rows;
}

but only if I make the original call to

public function actionIndex()

in the ClassController.php using

$query = Class::find();

   $pagination = new Pagination([
        'defaultPageSize' => 25,
        'totalCount' => $query->count(),
        'totalCount' => 1000,
    ]);
$bloerb = $query;
    return $this->render('index', [
        'bloerb' => $bloerb,
        'pagination' => $pagination,
    ]);

Then in views I iterate over $rows from the class file and use the pagination from $bloerb

<?php $rows = Class::getList2();?>
<?php foreach ($rows as $row): ?>
--snip--
<TD><?= $row['BLA'] ?>&nbsp;</TD>
--snip--
<?= LinkPager::widget(['pagination' => $pagination]) ?>

But that means I need two db calls which is suboptimal.

How do I get

$bloerb = $query->select([‘AsText(LATLON) as BLA’])
->offset($pagination->offset)
->limit($pagination->limit)
->all();

in ClassController.php not to ignore the AsText(LATLON) as BLA component in the query?

I am a Yii newbie, so I hope I explained it sufficiently. :slight_smile: Thanks in advance for any help.

2

Also funny:
1

$text = "'AsText(LATLON) as BLA'"; $rows = (new \yii\db\Query())->select([$matext])->etc

Throws
Undefined index: BLA
2
$matext = ["‘AsText(LATLON) as BLA’"];
$rows = (new \yii\db\Query())->select($matext)->etc

Throws
Undefined index: BLA
3
$rows = (new \yii\db\Query()) ->select([‘AsText(LATLON) as BLA’"])->etc

works

The queries above are abbreviated to explain the difference:
Real Query
'Col1','Col2','AsText(LATLON) as BLA'

Col1 and Col2 are always recognized.

3

Hi @Ankhenaten,

You could add a public attribute $BLA (or, preferably a pair of getter and setter methods for BLA) to your model class in order to store the fetched value of ‘ASTEXT(LATLON)’.

https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#selecting-extra-fields

1 Like
4

Hiya thanks @softark,

Your example got me to do the binary unpacking in PHP as the volume example did the calculation in php.

public function getVolume()
{
    if (empty($this->length) || empty($this->width) || empty($this->height)) {
        return null;
    }

if ($this->_volume === null) {
    $this->setVolume(
        $this->length * $this->width * $this->height
    );
}

return $this->_volume;

}

https://www.php.net/manual/en/function.unpack.php

$coordinates = unpack('x/x/x/x/corder/Ltype/dlat/dlon', $point_value);

echo $coordinates['lat'];
echo $coordinates['lon'];

       <?php endforeach; ?>
        <?php if (is_null($bloer->LATLON)) { $coordinates['lat'] = 0; $coordinates['lon'] = 0;}else {$coordinates = unpack('x/x/x/x/corder/Ltype/dlat/dlon',$bloer->LATLON);} ?>

            <TD><?= $coordinates['lat']?></TD>
            <TD><?= $coordinates['lon']?></TD>
    <?php endforeach; ?>

As a test works a charm.

Thanks :slight_smile:

1 Like