Yii Framework Forum: Can I Use Normal Query Instead Criteria Object For Use In Cactivedataprovider? - Yii Framework Forum

Jump to content

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

Can I Use Normal Query Instead Criteria Object For Use In Cactivedataprovider? Rate Topic: -----

#1 User is offline   Nabi 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 208
  • Joined: 22-February 12
  • Location:Ahwaz

Posted 15 March 2013 - 12:17 PM

Can i use normal query instead criteria object for use in CActiveDataProvider?

وقتی پیغام میذارید، بی زحمت متنتون رو انتخاب کنید و این دو تا کار ناقابل رو هم انجام بدید:
1. از اون بالا RTL رو انتخاب کنید.
2. فونت رو هم Tahoma بگذارید.
که چشمون در نیاد.
دمت گرم!

www.IranPHP.ir
0

#2 User is offline   softark 

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

Posted 15 March 2013 - 10:00 PM

Hi Nabi,

Well, the answer is No. You have to specify CDbCriteria to add conditions for CActiveDataProvider.
If you do have to use a plain SQL, you can use CSqlDataProvider instead.

But, IMO, you would be better off using CActiveDataProvider and CDbCriteria as long as you don't have a particular reason that you have to use plain SQLs. They are much more convenient. And you may note that CDbCriteria is fairly a thin wrapper so that you can do almost everything that you do with plain SQLs.
1

#3 User is offline   Nabi 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 208
  • Joined: 22-February 12
  • Location:Ahwaz

Posted 19 March 2013 - 07:41 AM

Thanks softark,
I dont know about CSqlDataProvider, It's great.

Also, It's my query:


SELECT id, username, SumRevenueSale, SumRevenueAffiliate, (SumRevenueSale + SumRevenueAffiliate) AS SumRevenueSaleAffiliate
FROM
(
	SELECT u.id AS id, u.username AS username, COALESCE(SumRevenueSale,0) AS SumRevenueSale FROM
	(
    	SELECT SalerID, SUM(RevenueSale) AS SumRevenueSale, OwnerID FROM
    	(
        	SELECT p.userId AS SalerID, o.id AS OrderID, COALESCE( amount, 0 ) AS RevenueSale, t.userId AS OwnerID
        	FROM xyz_order o
        	JOIN xyz_product p ON o.productId = p.id
        	LEFT JOIN xyz_transaction t ON o.id = t.orderId
        	WHERE t.type = 'rebateSale'
        	AND t.amount > 0
    	) AS oo
    	WHERE OwnerID = 1 # my user id
    	GROUP BY SalerID
	) AS ur
	RIGHT JOIN xyz_user u ON SalerID = u.id
	WHERE u.parentCode = (
    	SELECT u.refferCode AS MyRefferID
    	FROM xyz_user u
    	WHERE u.id = 1 # my user id
	)
) as t1
JOIN
(
	SELECT u.id AS id2, u.username AS username2, COALESCE(SumRevenueAffiliate,0) AS SumRevenueAffiliate FROM
	(
    	SELECT AffiliaterID, SUM(RevenueAffiliate) AS SumRevenueAffiliate, OwnerID FROM
    	(
        	SELECT ua.id AS AffiliaterID, o.id AS OrderID, COALESCE( amount, 0 ) AS RevenueAffiliate, t.userId AS OwnerID
        	FROM xyz_order o
        	JOIN xyz_user ua ON o.affiliateRefferCode = ua.refferCode
        	LEFT JOIN xyz_transaction t ON o.id = t.orderId
        	WHERE t.type = 'rebateAffiliate'
        	AND t.amount > 0
    	) AS oo
    	WHERE OwnerID = 1 # my user id
    	GROUP BY AffiliaterID
	) AS ur
	RIGHT JOIN xyz_user u ON AffiliaterID = u.id
	WHERE u.parentCode = (
    	SELECT u.refferCode AS MyRefferID
    	FROM xyz_user u
    	WHERE u.id = 1 # my user id
	)
) as t2
ON id=id2


I dont thinks can create it by CDbCriteria ;-)

وقتی پیغام میذارید، بی زحمت متنتون رو انتخاب کنید و این دو تا کار ناقابل رو هم انجام بدید:
1. از اون بالا RTL رو انتخاب کنید.
2. فونت رو هم Tahoma بگذارید.
که چشمون در نیاد.
دمت گرم!

www.IranPHP.ir
0

#4 User is offline   softark 

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

Posted 19 March 2013 - 08:32 AM

I see. :)

I would use CSqlDataProvider for it, too.
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