Yii Framework Forum: Best Method to Execute Complex Query - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Best Method to Execute Complex Query Rate Topic: -----

#1 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 07 April 2010 - 11:40 AM

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!
0

#2 User is offline   luoshiben 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 15-January 09

Posted 07 April 2010 - 12:23 PM

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!
1

#3 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 07 April 2010 - 01:02 PM

View Postluoshiben, on 07 April 2010 - 12:23 PM, said:

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
0

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 07 April 2010 - 01:51 PM

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');
}

0

#5 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 07 April 2010 - 02:28 PM

View PostMike, on 07 April 2010 - 01:51 PM, said:

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?
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 07 April 2010 - 02:34 PM

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.
0

#7 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 07 April 2010 - 02:42 PM

View PostMike, on 07 April 2010 - 02:34 PM, said:

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! :)
0

#8 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 07 April 2010 - 03:52 PM

View PostMike, on 07 April 2010 - 02:34 PM, said:

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.


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?
0

#9 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 07 April 2010 - 05:11 PM

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

(not tested)
/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#10 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 08 April 2010 - 01:46 AM

Oh, aliases can't be used in WHERE clauses, see here:
http://dev.mysql.com...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.
0

#11 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 08 April 2010 - 10:05 AM

View PostMike, on 08 April 2010 - 01:46 AM, said:

Oh, aliases can't be used in WHERE clauses, see here:
http://dev.mysql.com...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.
0

#12 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 08 April 2010 - 11:36 AM

View Postwaveslider, on 08 April 2010 - 10:05 AM, said:

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!
0

#13 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 08 April 2010 - 02:31 PM

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.
0

#14 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 08 April 2010 - 04:01 PM

View Postwaveslider, on 08 April 2010 - 02:31 PM, said:

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#15 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 08 April 2010 - 04:05 PM

View Posttri, on 08 April 2010 - 04:01 PM, said:

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.
0

#16 User is offline   Y!! 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 978
  • Joined: 18-June 09

Posted 08 April 2010 - 04:40 PM

View Posttri, on 08 April 2010 - 04:01 PM, said:

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();



Just for reference, this feature is probably coming soon.
0

#17 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 08 April 2010 - 04:52 PM

View PostY!!, on 08 April 2010 - 04:40 PM, said:

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?
0

#18 User is offline   Sheldmandu 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 42
  • Joined: 01-April 10
  • Location:Brisbane, Australia

Posted 08 April 2010 - 05:21 PM

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.
0

#19 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 09 April 2010 - 02:34 AM

@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.
0

#20 User is offline   tommytwoeyes 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 77
  • Joined: 08-March 10

Posted 09 April 2010 - 08:23 AM

View PostMike, on 09 April 2010 - 02:34 AM, said:

@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.
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users