yii mysql using subqueries with query builder

Question

I am builing an app with yii and I tried doing my sql calls the "pretty way" using the yii query builder the same way seen on this guide

http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

And this is my built query:

$diseaseCountSqlQuery = Yii::app()->db->createCommand()
                                    ->select ('tbl_disease.ICD10, COUNT(tbl_disease.ICD10) AS disease_count')
                                    ->from ('tbl_disease')
                                    ->join ('tbl_symptom_disease', 'ICD10=diseaseCode')
                                    ->where ($symptomsOrQueryArray)
                                    ->group ('ICD10')
                                    ->queryAll();
            //placeholder

            $maxDiseaseCountQuery = Yii::app()->db->createCommand()
                                    ->select ('MAX(disease_count) AS max_disease_count')
                                    ->from ($diseaseCountSqlQuery)
                                    ->queryAll();
            //multiple symptom query
            $diseaseCodes = Yii::app()->db->createCommand()
                            ->select ('ICD10')
                            ->from ($maxDiseaseCountQuery)
                            ->join ($diseaseCountSqlQuery, 'max_disease_count=disease_count')
                            ->queryAll();

The problem appears in the second command specifically the line:

->from ($diseaseCountSqlQuery)

I get an "strpos() expects parameter 1 to be string, array given " error.

I can solve my problem by just copying the entire mysql command into a string and then using create command, but I wanted to use this more "elegant" way. Can someone help me fix it and explain a bit how subqueries work with query builder? Thank you :)

the error tells you exactly what the problem is: you're trying to use an array where a string is expected. you'd need to do a foreach($diseaseCountSqlQuery as $part) { ...->from($part)} or whatever. — Marc B
– Marc B, Commented Jun 18, 2014 at 18:09
ok, so is there a way to use the result table of the sql command in a different query? — Yannis P.
– Yannis P., Commented Jun 18, 2014 at 18:15

Jap Mul · Accepted Answer · 2014-06-19 08:14:17Z

1

You can't create subqueries like this. The first queryAll returns an array with results. You can't query this like it's a database. The from() part expects a parameter in the form of a string, not an array.

Try it like this:

Yii::app()->db->createCommand('complete query, including subquery')->queryAll();

Or use PHP to filter the results after executing the first query.

More info on createCommand: http://www.yiiframework.com/doc/api/1.1/CDbConnection#createCommand-detail http://www.yiiframework.com/doc/api/1.1/CDbCommand

edited Jun 19, 2014 at 8:14

answered Jun 18, 2014 at 18:32

Jap Mul

18.9k6 gold badges61 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yannis P. Over a year ago

Do you have any useful resources so I can understand the use of createCommand that way better?

Jap Mul Over a year ago

Take a look at the links I've added to the answer.

Collectives™ on Stack Overflow

yii mysql using subqueries with query builder

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related