Yii Framework Forum: Combine Queries / Results - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Combine Queries / Results Rate Topic: -----

#1 User is offline   GSTAR 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,035
  • Joined: 30-October 09
  • Location:UK

Posted 25 November 2013 - 04:28 AM

Is it possible, using CDbCriteria, to combine two queries?

For example I want to return a total of 15 results, of these five results must be "type = 2", the remaining 10 results are "type = 1". If there are less than five type 2 results, then the type 1 results would need to increase.

The results would be used as a dataprovider for a CListView widget.

Any idea how I can go about this?
0

#2 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 25 November 2013 - 04:36 AM

Sounds much like a job for UNION and CSqlDataProvider.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#3 User is offline   GSTAR 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,035
  • Joined: 30-October 09
  • Location:UK

Posted 25 November 2013 - 05:13 AM

Any way to do this using CActiveDataProvider and CDbCriteria?
0

#4 User is offline   alex-w 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 223
  • Joined: 25-November 10

Posted 25 November 2013 - 05:45 AM

Will there be more than 5 of type=2?
0

#5 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 25 November 2013 - 05:46 AM

Only if you are going through CActiveRecord.findBySql(). As far as I can see, neither CDbCriteria nor CActiveRecord are aware of the UNION statement.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#6 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,075
  • Joined: 16-February 11
  • Location:Japan

Posted 25 November 2013 - 05:58 AM

I would rather want to consider making a db view.
0

#7 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 25 November 2013 - 07:56 AM

What is it with all people wanting to use views lately? It's not like it's the newest, hippest thing in the database world, isn't it? :huh:

Anyway, this is the type of query I'd imagine for the given problem:
(SELECT * FROM `table` WHERE `type`=2 LIMIT 5) UNION (SELECT * FROM `table` WHERE `type`=1) ORDER BY `type` DESC LIMIT 15;

programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#8 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,075
  • Joined: 16-February 11
  • Location:Japan

Posted 25 November 2013 - 08:10 AM

I'm relatively new to the world of DB, so I don't know the exact place for db view.
But what I like most is that you can simplify the PHP code that retrieves the records, using the simplest syntax of AR, when you have created a AR model for a db view.
0

#9 User is offline   GSTAR 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,035
  • Joined: 30-October 09
  • Location:UK

Posted 25 November 2013 - 08:14 AM

View PostDa:Sourcerer, on 25 November 2013 - 07:56 AM, said:

What is it with all people wanting to use views lately? It's not like it's the newest, hippest thing in the database world, isn't it? :huh:

Anyway, this is the type of query I'd imagine for the given problem:
(SELECT * FROM `table` WHERE `type`=2 LIMIT 5) UNION (SELECT * FROM `table` WHERE `type`=1) ORDER BY `type` DESC LIMIT 15;


Thanks. How would I put that in to a CDbCriteria object?
0

#10 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 25 November 2013 - 08:22 AM

View Postsoftark, on 25 November 2013 - 08:10 AM, said:

I'm relatively new to the world of DB, so I don't know the exact place for db view.

When it comes to MySQL, views are mostly a convenience thing. They are very limited, though. E.g. MySQL doesn't know materialized views, which can become a scalability issue, IMHO.

View PostGSTAR, on 25 November 2013 - 08:14 AM, said:

Thanks. How would I put that in to a CDbCriteria object?

If I'd know ... CDbCommand has a union() method. Can you imagine building two CDbCriteria objects and joining them together? Otheriwse, CSqlDataProvider is the way to go.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#11 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,075
  • Joined: 16-February 11
  • Location:Japan

Posted 25 November 2013 - 10:11 AM

On second thought, I would follow Da:Sourcerer's suggestion to use CSqlDataProvider in this scenario, because a db view based AR model may not provide you with a proper result when you have some requirements to filter the result set.
When you want to filter both results of type 2 and type 1, and still want to show 5 records of type 2, then a db view based AR model will not work as expected.
0

#12 User is offline   GSTAR 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,035
  • Joined: 30-October 09
  • Location:UK

Posted 25 November 2013 - 10:20 AM

Thanks guys. I am now going to try doing this using CSqlDataProvider.

My only concern is that the query I generate needs to be dynamic, so for example I want to be able to pass in GET parameters that can be used to extend the query, for example a "WHERE LIKE" clause or an IN clause. Preferably in a similar way to when doing it using CDbCriteria, e.g : $criteria->compare()

Is this going to be possible?
0

#13 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 26 November 2013 - 12:09 AM

That's going to be a problem. You can sort with CSqlDataProvider. But filtering is an ar feature.

So, here's an idea: Perhaps you can let your regular ar model construct a CDbCriteria object via its search() method and extract the condition and params part, and finally recycle it for your CSqlDataProvider. It's a bit hackish and assumes you don't do anything fancy like searching in related fields, but it would be worth the try. I remember having done something similar in a complex CSV export.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

Share this topic:


Page 1 of 1
  • 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