Complex Queries With Activerecord

Yii 2.0 General Discussions
1

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’

2

Instead of building active record around your complex query, could you just use the query itself?

public static function findBySql($sql, $params = [])

3

Recommend creating a DB View for such use where you are using MYSQL inbuilt functions (that maybe faster than using PHP functions and classes from yii).

[sql]

CREATE VIEW view_1 AS

SELECT ip, broadcast, CONCAT( INET_NTOA( ip ) , "/", BIT_COUNT( mask)) AS code

FROM IPv4

[/sql]

Call [font="Courier New"]view_1[/font] in Yii with a query object and the criteria you need.

OR create one single view combining/joining all tables and refer them in your query in yii with whatever criteria you want.