Can I Use Normal Query Instead Criteria Object For Use In Cactivedataprovider?

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

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.

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 :wink:

I see. :)

I would use CSqlDataProvider for it, too.