[PROPOSAL] query as immutable activerecord object

[mikk150]

this could be successfully used for Statistical queries without setters getters method, and is actually faster and could potentially used for other generator queries as well

class WhateverCount extends \yii\db\ImmutableActiveRecord
{
    public function query()
    {
        return (new \yii\db\Query())->select(['related_id', 'counted' => 'count(*)'])->from('tablename')->groupBy('related_id'); //OR SOMETHING
    }
}

class WhateverModel extends \yii\db\ActiveRecord
{
    public function tablename()
    {
        return 'sometable';
    }

    public function getWhateverCount()
    {
        $this->hasOne(WhateverCount::class,['id'=>'related_id']);
    }
}

WhateverModel::find()->with('whateverCount')->all();

would do queries like this then

SELECT * FROM sometable;
SELECT related_id, count(*) counted FROM tablename WHERE related_id IN (...) GROUP BY related_id

it would give opportunity to do things like this

WhateverModel::find()->with([
    'whateverCount'=>function($query) {
        $query->andWhere(['>','created_at','2013-02-14 3:22:17']
    }
])->all();

And gridview would also like this :)

[mikk150]

Right now I am using default \yii\db\ActiveRecord to do so

class LenderLeadSentCount extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'tablename';
    }

    public function attributes()
    {
        return ['related_id','counted'];
    }

    public static function find()
    {
        $query=parent::find();
        return $query->select(['related_id', 'counted' => 'count(*)'])->groupBy('related_id');
    }
}

It actually works well, and does everything I need, but it shouldn't think that you can mutate attributes

Maybe just take Yii2 activerecord and turn off magic setters?

[mikk150]

will it happen?

[samdark]

It's quite interesting way of using AR. I love it.

Besides assigning attributes directly there are setAttributes() and load().

[klimov-paul]

For me it breaks ActiveRecord pattern.
The main essence of ActiveRecord - it can save itself. Such immutable ActiveRecord cann't save anything.

[samdark]

Well, we may call it aggregate record.

[mikk150]

@klimov-paul yes, this pattern would not save anything hence the issue. there are actually other issues too, like sometimes I need to put pk in attributes and sometimes not(but it is wrong anyway)

When I discovered that I could hack up AR like shown in comment, i use it heavily(since I do lots of aggregating queries)

[klimov-paul]

The idea itself could be considered, but it will produce confusion. If we add something like this in the core it may be misused.
Actual implementation should be based on a trait, since we have several extension for NOSQL database, which may also require it.

[mikk150]

as you can see, AR can also be misused :D
I have no idea, how this can be accomplished in NOSQL. I have used mongodb, but I have never aggregated there

[mikk150]

@samdark I am not sure, if we could call it aggregaterecord, since user might not even aggregate anything. There are other immutable queries like generators that this pattern could be used for

SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
FROM
 (SELECT 0 AS a
  UNION ALL SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
  UNION ALL SELECT 7
  UNION ALL SELECT 8
  UNION ALL SELECT 9) AS a
CROSS JOIN
 (SELECT 0 AS a
  UNION ALL SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
  UNION ALL SELECT 7
  UNION ALL SELECT 8
  UNION ALL SELECT 9) AS b
CROSS JOIN
 (SELECT 0 AS a
  UNION ALL SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
  UNION ALL SELECT 7
  UNION ALL SELECT 8
  UNION ALL SELECT 9) AS c

could be beautified by this method

[samdark]

The SQL ↑ is better to be created using query builder...

[klimov-paul]

It seems the feature itself is already represented via Query::asArray() option. If it is enabled, no attribute match checks will be performed.

class WhateverCount extends \yii\db\ActiveRecord
{
    public function query()
    {
        return (new \yii\db\Query())
            ->select(['related_id', 'counted' => 'count(*)'])
            ->from('tablename')
            ->groupBy('related_id')
            ->asArray(true);
    }
}

class WhateverModel extends \yii\db\ActiveRecord
{
    public function tablename()
    {
        return 'sometable';
    }

    public function getWhateverCount()
    {
        $this->hasOne(WhateverCount::class,['id'=>'related_id']);
    }
}

$rows = WhateverModel::find()->with('whateverCount')->all();
foreach ($rows as $row) {
    echo $row->whateverCount['counted'];
}

[mikk150]

that's nice and all, but you are still using ActiveRecord for wrong purpose :) plus you cannot do relations this way (because arrays do not have that information)