how to merge two cdb criteria results into one and use it in the cactive dataprovider

Yii 1.1 General Discussion
1

hi the problem i m facing is that i have one table of photos and the photos stored in it are for the albums and the groups

what i want it to display all the photos which are under the public albums or open groups in clistview

(albums has a column specifying the album is private or public and groups also has a column specifies open or closed)

ps how to apply search on this condition.

2

I think 1 criteria will be enough, but first, show your photos’ table structure. Do you have 2 foreign keys in it?

3

no i m using two fields on records the id for which the photo is and other records the type(‘album’,‘group’) in the photos

here is table structure




        id 	int(11) 

	name 	varchar(255) 	

	source 	varchar(255) 	

	posted_by 	int(11)

	created_on 	datetime

	description 	text 	

	mediaFor 	enum('album','group') 

	forID 	int(11)
4

can anyone help me out plz?

5

Do you have relations for albums and groups in a Photo model? It can look something like this:




$dataProvider=new CActiveDataProvider('Photo', array(

    'criteria'=>array(

        'with'=>array('album', 'group'),

        'condition'=>'album.public = 1 OR group.open = 1',

    ),

));

I didn’t test it, just an idea :)

6

well this doesn’t work out correctly as it also fetches those photos which are in private albums or in closed groups :frowning:

7

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? :frowning:




$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));
8

Maybe because you will need to bring them "together" ?




$dataProvider=new CActiveDataProvider('Photo', array(

    'criteria'=>array(

        'with'=>array('album', 'group'),

        'condition'=>'album.public = 1 OR group.open = 1',

        'together'=>true,

    ),

));

Just a thought…

9

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')
10

Change the join type into INNER JOIN and see what comes out.

11

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

12

by using ur method it give an error of unknown column ‘album’, i even changes the alias of albums table but the error remains same.

13

i used the join as inner join but it also performs outer join

14

I can only suggest to check the generated query by enabling logging and sql profiling.