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