currently i m using this for now is this ok or it can be optimized?
and how can i modify the search in photos model to get the search results in the list view anyone?
$model = Photos::model()->findAllBySql("select * from ((select m.*, a.privacy_level from photos p , albums a
where m.forID = a.id
and m.mediaFor = 'album'
and a.privacy_level='public')
union
(select m.*, g.group_type from photos p , groups g
where m.forID = g.id
and m.mediaFor = 'group'
and g.group_type='open'))photos");
$dataProvider=new CActiveDataProvider('Photos');
$dataProvider->setData($model);
$dataProvider->setTotalItemCount(count($model));
together is not working too all results are fetched.
this sql is run by cActiveDataProvider
SELECT COUNT(DISTINCT `t`.`id`) FROM `photos` `t` LEFT OUTER JOIN `albums` `album` ON (`t`.`forID`=`album`.`id`) LEFT OUTER JOIN `groups` `group` ON (`t`.`forID`=`group`.`id`) WHERE (album.privacy_level='private' OR group.group_type='open')
I think the problem is in “ON” part, because “WHERE” will filter rows after joining tables. If an album can have id 1 and a group can have id 1, then you must add “ON t.mediaFor = ‘album’” and “ON t.mediaFor = ‘group’” in the corresponding relations. When you do “t.forID=album.id” you don’t check if it’s an album or a group