So I have this query and I want to create a scope.
SELECT DISTINCT t2.id
FROM (
SELECT ip, broadcast
FROM IPv4
WHERE CONCAT( INET_NTOA( `ip` ) , "/", BIT_COUNT( `mask` ) ) LIKE '%:search%'
) AS t1
INNER JOIN IPv4 t2
WHERE t2.ip <= t1.ip AND t2.broadcast >= t1.broadcast
ORDER BY t2.mask
Could the ActiveRecord methods (Query class more specific) handle this?
My try:
public static function searchFor($query, $search)
{
$subQuery = new Query();
$condition = new Expression('CONCAT(INET_NTOA(`ip`), "/", BIT_COUNT(`mask`))');
$subQuery->select('ip, broadcast')->from(self::tableName());
$subQuery->where(['like', $condition, $search]);
$command = $subQuery->createCommand();
$query->select('id')->distinct()
->from('(' .$command->sql.') as t1')
->innerJoin('IPv4 t2')
->addParams($command->params);
}
SELECT DISTINCT `id` FROM (SELECT `ip`, `broadcast` FROM `IPv4` WHERE CONCAT(INET_NTOA(`ip`), `"/"`, BIT_COUNT(`mask`)) LIKE :qp0) as t1 INNER JOIN `IPv4 t2`
And if I try running this:
\app\models\IPv4::find()->searchFor('83.171')->all();
I get: Column not found: 1054 Unknown column ‘"/"’ in ‘where clause’