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?
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!
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.
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?
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''
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.
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
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.
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.
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.
For my understanding: So you have relation between universities and tuiton and want to filter universities by the criteria in tution we discussed here?
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.