Best Method to Execute Complex Query

I’ve encountered a situation with our app in which I’m not sure of the best way to achieve the results I need.

Here’s my goal:

I have a table that contains a variety of tuition data about certain universities (e.g. total cost of in-state (US) tuition, total room & board expenses, total cost of books, etc.). I need to allow my users to search the universities by a tuition range.

However, the total tuition cost for each university is calculated by a model via custom getters and other model methods. So, of course, I can’t write a simple SQL query to retrieve the universities with total tuition costs that fall within the user’s given range.

My first inclination was to create a stored procedure that would perform the required query, but I’d like to continue using ActiveRecord and CActiveDataProvider because they save me a lot of work and allow me to reuse the same view I use to list/index all of the universities.

I’m still pretty new to Yii, and this exceeds my level of knowledge/familiarity with it.

Can anyone suggest the best method to use to achieve these results, please?

Thanks in advance!

Hi Wave,

I’m not sure if I fully understand your situation, but as a general rule if I encounter situations where advanced queries are needed, especially when database functions are required to calculate results, I generally deviate from ARs and use DAO instead. While you’ll lose some of the native ease of working with ARs, DOA will generally be more efficient. Also, by extending the CModel and/or CDataProvider classes, you can perform queries using DAO, but still take advantage of the widgets and other things that consume models and dataproviders.

Also, as one last idea – and maybe I’m steering you in the wrong direction here due to my lack of understanding of your specific situation – if you really need/want to use ARs, you could possibly also create a viewof your data based on a query that get’s you most of the way there, then tie your AR models to the view table(s).

Anyway, I hope that maybe this helps to point you in the right direction. Best!

Hi Luoshiben,

Thank you for your reply.

DAO is the direction I’ve been leaning in as well; I just wasn’t sure how to continue using widgets like CActiveDataProvider with it. I’m still not sure exactly what approach to take when extending it to accommodate the results DAO returns.

Do you have any example(s) you could provide, please?

I think a stored procedure will work better than a view, because I need to SUM() the values of several columns and search for records whose values fall within a certain range. But I could be wrong - it happens frequently. ;)

waveslider

If you can solve your problem with a SQL like this:


SELECT column1 + column2 + column3 AS someResult, ...

you could add a public $someResult and use a defaultScope() to always add the above to the select.


public function defaultScope() {

  return array('select'=>'column1 + column2 + column3 AS someResult');

}

Thank you, Mike. I like your idea for the simplicity of the solution. However, I’m not sure if it will work in this situation, because it can’t be applied to all queries for this model.

I’m thinking a named scope might work, though. I could just specify the scope when building the AR query, then pass the user-provided tuition range as params, right?

Right, with a minor correction: you need a paremeterized named scope to pass parameters. You find an example on the AR page in the guide.

Thanks, Mike. That is awesome.

Viva la Yii! :)

Mike,

I seem to be missing something…

Below is my code:




    public function byRange($min, $max)

    {

        $criteria = new CDbCriteria;

        $criteria->select = '(inStateTuition + booksSupplies + onCampusRoomBoard + onCampusOther) as totalTuition';

        $criteria->condition = 'totalTuition >= :min AND totalTuition <= :max';

        $criteria->params = array(

            ':min'=>$min,

            ':max'=>$max,

        );


        return $this->findAll($criteria);

    }



When I test this in the shell, I get the following error:




exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'totalTuition' in 'where clause''



Any suggestions to get this to work?

If you already declared "public $totalTuition", try using "t.totalTuition" in the condition.

(not tested)

/Tommy

Oh, aliases can’t be used in WHERE clauses, see here:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

But you can use 'HAVING totalTuition >= …’ (property ‘having’ instead of ‘select’). You should check if that has big implications for the DB performance. My guess is that MySQLs optimizer can’t work that well with HAVING conditions than with WHERE.

Thanks Mike!

Did you perhaps mean I should replace the ‘condition’ property with ‘having’?

Here is my revised code:




    public function byRange($min, $max)

    {

        $criteria = new CDbCriteria;

        $criteria->select = '(inStateTuition + booksSupplies + onCampusRoomBoard + onCampusOther) as totalTuition';

        $criteria->group = 'totalTuition';

        $criteria->having = 'totalTuition >= :min AND totalTuition <= :max';

        $criteria->params = array(

            ':min'=>$min,

            ':max'=>$max,

        );


        return $this->findAll($criteria);

    }



When I try to run it now, I get a fatal error, but it’s hard to troubleshoot because I can’t see the SQL it is producing:


Fatal error:  Call to a member function findAll() on a non-object

I am able to execute the following query in phpMyAdmin successfully:




SELECT

    (`inStateTuition` + `booksSupplies` + `onCampusRoomBoard` + `onCampusOther`) as `totalTuition`

FROM

    `Tuition` 


HAVING

    `totalTuition` >= 20000

AND

    `totalTuition` <= 75000



Thank you for all of your help.

Ok, now I see - the named scope must return $this; not $this->findAll();

Oops!

Sorry for bumping this, but I just discovered the note in the definitive guide that says named scopes can not be parameterized.

Is that still true? I realize everyone who maintains Yii and the documentation is very busy, and that there’s a possibility that note might be outdated.

The documentation for Active Record says named scopes can be parameterized.

Example:


$posts=Post::model()->published()->recently(3)->findAll();

The chapter on Relational Active Record has this note:

"Named scopes applied to related models must be specified in CActiveRecord::scopes. As a result, they cannot be parameterized."

Example:


$posts=Post::model()->with('comments:recently:approved')->findAll();

/Tommy

I am sorry; of course you are right. That is what I meant to say.

Just for reference, this feature is probably coming soon.

Yes, I found that in a Google search I performed earlier. I can’t wait . ;)

So for now, since I can’t solve this problem with relational, parameterized named scopes, I’m kind of back to the drawing board. I’d still really like to solve this with an ActiveRecord solution, if possible.

Since I need to permit users to fill out this search form with either a geographic location, or a tuition cost range, or a combination of both (and possibly with more models combined in the future), I’m wondering if I can define a method in each respective model that would compile its own criteria and then return it, so the criteria could be merged in the controller. Then, the query would be performed from there.

Is that possible? Is there a better approach?

Hi guys here’s a question. What’s the best way to access data in Yii where you’re bringing some data back from many tables. For example, lets say I’m building a shopping cart and need to show a product list. In this product list however I need to show some product data, as well as a couple of fields from the category a couple of fields from the manufacturer and a couple of fields from the image, and some fields from the product info tables. SO in total there are 5 tables I’m getting data out of, but I don’t need ALL the data from all the tables? Also, once the data is retrieved I will need to add some logic to do things like create the seo friendly name to pass to the urlManager class to generate the URL, where would I put such logic? It doesn’t seem that Active Record is the most appropriate solution here unless I create an active record based on a query somehow where I specify what fields need to be retrieved from what tables…

I thinks what I’m talking about here is a pretty common scenario for websites that primarily output stuff.

Any help and suggestions would be much appreciated.

@waveslider:

For my understanding: So you have relation between universities and tuiton and want to filter universities by the criteria in tution we discussed here?

@Sheldmandu:

Please create a new topic for you question, thanks.

Mike,

Yes, that’s exactly right. I also need to find a solution that will allow me to filter the universities by additional criteria (e.g. admission statistics, graduation statistics, etc.) in the future.