Need a bit of help

Hi, so giving yii a try in my new search for changing my framework of choice.

To start, i’ve decided to built something from scratch, so i started with a simple blog.

I have following tables (that matters in this converstation)

-> user (user_id pk)

-> post (post_id pk, user_id fk)

-> category (category_id pk)

-> post_to_category (post_id fk,category_id fk)

-> comment (comment_id pk, post_id fk)

Now, i have a url like /category/show/category-nice-url from where i check if that category exists and if so, i take the category_id to query for posts within that category .

I did like so :




//CategoryController


if( ! $category = Category::model()->find('url=:url',array(':url'=>$category_url)))

{

   $this->render('category_error');

   exit;

}


// get all the posts from category.

$posts = Post::model()->findAllBySql(

                            'SELECT p.*,u.* FROM post_to_category p2c 

                                INNER JOIN post p ON p.post_id=p2c.post_id 

                                INNER JOIN user u ON u.user_id=p.user_id 

                            WHERE p2c.category_id=:category_id 

                            ORDER BY p2c.post_id DESC',

                            array(':category_id'=>$category->category_id)

                    );




These are my defined relations :




//Category model

return array(

			'parent' => array(self::BELONGS_TO, 'Category', 'parent_id'),

			'categories' => array(self::HAS_MANY, 'Category', 'parent_id'),

			'posts' => array(self::MANY_MANY, 'Post', 'post_to_category(category_id, post_id)'),

		);


//Post model

return array(

			'comments' => array(self::HAS_MANY, 'Comment', 'post_id'),

			'user' => array(self::BELONGS_TO, 'User', 'user_id'),

			'categories' => array(self::MANY_MANY, 'Category', 'post_to_category(post_id, category_id)'),

		);



So, the question is, how to get the category posts using active records (maybe CDbCriteria) and further, how to get the post comments in same manner ?

Don’t get me wrong, i did searched the documentation, but i need a clear example .

Okay, so far i got this :




$posts=Post::model()->findAll(array(

                'select'=>  'post.post_id,post.title,post.url,categories.*,user.*',

                'with'  =>  array('categories','user'),

                'condition'=> 'categories.category_id ='.$category->category_id,

                ));


which is the equivalent to :


$criteria = new CDbCriteria;

$criteria->select = 'post.post_id,post.title,post.url,categories.*,user.*';

$criteria->with   = array('categories','user');

$criteria->condition = 'categories.category_id ='.$category->category_id ;

$posts=Post::model()->findAll($criteria);



And it works, but here comes the funny and very ugly part.

  1. I don’t want all the fields from the user table, so i change my query with :



$posts=Post::model()->findAll(array(

                'select'=>  'post.post_id,post.title,post.url,categories.*,user.username',

                'with'  =>  array('categories','user'),

                'condition'=> 'categories.category_id ='.$category->category_id,

                ));



which fails with :

Active record "Post" is trying to select an invalid column "user.username". Note, the column must exist in the table or be an expression with alias.

BUT i do have a column name called username, so why is the error ?

2)Next i try to limit my results to one :




$posts=Post::model()->findAll(array(

                'select'=>  'post.post_id,post.title,post.url,categories.*,user.*',

                'with'  =>  array('categories','user'),

                'condition'=> 'categories.category_id ='.$category->category_id,

                'limit'=>1

                ));



And i get :

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘categories.category_id’ in ‘where clause’

How come ? if i run the query without the limit part, runs just fine .

  1. I wanted to use the INNER JOIN instead of LEFT OUTER JOIN, so i did :



$posts=Post::model()->findAll(array(

                'select'=>  'post.post_id,post.title,post.url,categories.*,user.*',

                'with'  =>  array('categories','user'),

                'condition'=> 'categories.category_id ='.$category->category_id,

                'together'=>false,

                'joinType'=>'INNER JOIN'

                ));



Which thrown :

Property "CDbCriteria.joinType" is not defined.

Okay okay, is not defined, but the guide says it is and it must be usable , so ?

4)I want to order my results by the post_id desc, so i do




$posts=Post::model()->findAll(array(

                'select'=>  'post.post_id,post.title,post.url,categories.*,user.*',

                'with'  =>  array('categories','user'),

                'condition'=> 'categories.category_id ='.$category->category_id,

                'together'=>false,

                'order'=>'post.post_id DESC'

                ));



CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘post.post_id’ in ‘order clause’

How come ? i just specified the post table in the select region of the query .

If instead of the post alias i add the t alias it works, but should i guess the alias name ?

Another insteresting thing, is that, if for example i have something like




$criteria = new CDbCriteria;

$criteria->select = 'post.post_id,post.title,post.url,categories.*,user.*';

$criteria->with   = array('categories','user');

$criteria->condition = 'categories.category_id ='.$category->category_id ;

$criteria->order = 'post.post_id DESC' ;

$posts=Post::model()->findAll($criteria);



So, i want just the post_id, the post title and the post url from the posts table, but in the loop if i do a




foreach($posts AS $post)

{

echo $post->content ;

}



It shows the content, though i don’t want it there, so the question is why it is shown ?

I looked over the documentation over and over and over, i followed the examples and what you see above is the result .

Please advise .

I don’t really understand why you do that instead of using conditions on your relations or named scopes (i.e. Post:: model().with(‘user:usernameOnly’).findAll(); -check the guide about it and also disambiguation table names) if you just want certain fields.

I think that it could be lot of help if you can provide an example of the SQL result.

Finally, somebody :)

I do like this because that’s what the user guide is describing as the way of doing it .

It states that we can use an instance of cDbCriteria to filter the results how we want, but apparently it doesn’t work as stated there.

Here is the query produced when it works :




SELECT `t`.`post_id` AS `t0_c0`, `t`.`title` AS `t0_c2`,

`t`.`url` AS `t0_c3`, `t`.`post_id` AS `t0_c0`, `t`.`user_id` AS `t0_c1`,

`t`.`title` AS `t0_c2`, `t`.`url` AS `t0_c3`, `t`.`content` AS `t0_c4`,

`t`.`date_added` AS `t0_c5`, `t`.`post_id` AS `t0_c0`, `t`.`user_id` AS

`t0_c1`, `t`.`title` AS `t0_c2`, `t`.`url` AS `t0_c3`, `t`.`content` AS

`t0_c4`, `t`.`date_added` AS `t0_c5`, `categories`.`category_id` AS

`t1_c0`, `categories`.`parent_id` AS `t1_c1`, `categories`.`name` AS

`t1_c2`, `categories`.`url` AS `t1_c3`, `categories`.`date_added` AS

`t1_c4`, `user`.`user_id` AS `t2_c0`, `user`.`username` AS `t2_c1`,

`user`.`password` AS `t2_c2`, `user`.`email` AS `t2_c3`, `user`.`website`

AS `t2_c4`, `user`.`avatar` AS `t2_c5`, `user`.`is_admin` AS `t2_c6`,

`user`.`is_active` AS `t2_c7` FROM `post` `t`  LEFT OUTER JOIN

`post_to_category` `categories_categories` ON

(`t`.`post_id`=`categories_categories`.`post_id`) LEFT OUTER JOIN

`category` `categories` ON

(`categories`.`category_id`=`categories_categories`.`category_id`)  LEFT

OUTER JOIN `user` `user` ON (`t`.`user_id`=`user`.`user_id`)  WHERE

(categories.category_id =3)



Anyway, i try to understand why it behaves so weird (see my examples again) , i don’t want a workaround for this, i just want to make it work as the examples say it does.

I think the problem is your approach, do this to filter your relations:




Post::model()->with(array('user'=>

    array('select'=>'{{user}}.username'))

    ).findAll();

In findAll filter Posts, and within the with filter accordingly each of the related.

Hope this helps you

You were right, the problem was as you say.

following block of code fixed the issue :




$posts = Post::model()->with(

                                array(

                                    'user'=>array('select'=>'user.username'),

                                    'categories'=>array(

                                                    'select'=>'categories.category_id', 

                                                    'condition'=>'categories.category_id ='.$category->category_id

                                                    )

                                    )

                                )->findAll();



But still i cannot find a way of ordering by post_id DESC, it still fails, can you point me to right direction ?

Correction, if i do a :




$posts = Post::model()->with(

                                array(

                                    'user'=>array('select'=>'user.username'),

                                    'categories'=>array(

                                                    'select'=>'categories.category_id', 

                                                    'condition'=>'categories.category_id ='.$category->category_id,

                                                    'joinType'=>'inner join',

                                                    'order'=>'t.post_id DESC'

                                                    )

                                    )

                                )->findAll();



It works, but is this the right way ?

I mean, i say the t alias in the sql, is this going to be always the default alias for the primary table ?

Exactly!

Now you getting it! Congratulations!

By the way, use table alias {{model}} always, will give you more flexibility. And yes! t is always the default of the model caller.

I think you are right again and i am getting it

Take a look :




// This is to check if the category exists and retrieve the columns i want

$cdb = new CDbCriteria ;

$cdb->select    = 't.category_id,t.name,t.url,t.parent_id';

$cdb->condition = 'url=:url';

$cdb->params    = array(':url'=>$category_url);

$category       = Category::model()->find($cdb);


// which can be written as 


$category = Category::model()->find(array('select'=>'t.category_id,t.name,t.url,t.parent_id','condition'=>'url=:url','params'=>array(':url'=>$category_url)));

        



I guess the above is a matter of taste, as i believe the second query will use the CDbCriteria anyway. Am i right ?

Now, comming to my issue with ordering, couldn’t be more simple :




$posts = Post::model()->with(

                                array(

                                    'user'=>array('select'=>'user.username'),

                                    'categories'=>array(

                                                    'select'=>'categories.category_id', 

                                                    'condition'=>'categories.category_id = :cid ',

                                                    'params'=>array(':cid'=>$category->category_id)

                                                    )

                                    )

                                )->findAll(array('order'=>'t.post_id DESC'));



In findAll method i pass all the criteria i need to apply for the default table .

Thanks for your advice, i will do like so from now on, and thanks for you time for helping me out.

This was the hardest step for me, i wanted to learn the database tasks in order to be sure that i can use the entire framework as i want.

Though, right now i am caching the database schema for using with AR and increase the performance, i think in the future i will use DAO more because it says that is faster, maybe you have something to add from your own experience ?

Hah, couldn’t been better that that, in my last example, the framework used the eager loading , but if i do something like :




$cdbU = new CDbCriteria;

$cdbU->select = '{{user}}.username';

$cdbC = new CDbCriteria;

$cdbC->select = '{{categories}}.category_id';

$cdbC->condition = 'categories.category_id = :cid ';

$cdbC->order = 'categories.category_id DESC';

$cdbC->params = array(':cid'=>$category->category_id) ;

$posts = Post::model()->with(array('user'=>$cdbU,'categories'=>$cdbC))->findAll(array('order'=>'t.post_id DESC'));

            



It will use the lazy loading, which can be very helpful. This is just great.

Haven’t tested yet, but i think that if in my previous query i would something like




[...]

'categories'=>array(

    'select'=>'categories.category_id', 

    'condition'=>'categories.category_id = :cid ',

    'params'=>array(':cid'=>$category->category_id),

    'together'=>false

     )


[...]



In that case, setting together on false would use the lazy load approach(i am not sure, but that’s what i am thinking it would do)

The good thing about yii is its flexibility.

There are so many ways to do the same thing that is just a matter of taste as you said.

I am very happy to see that my little help you to go so long way in such small time.

Me too falled in love inmediatelly with this framework.

I think that the best thing is ‘yet’ about to come

Best regards!

** add me to your friends list, we may share knowledge together.

I like to be serious when i try to do something, that’s why i dedicated this weekend to yii :)

Got pretty far, my "blog" has nested categories (a right side widget with parent->child categories by extending the cWidget) also, i made a special widget for breadcrumbs of the child/parent categories and managed to pass properties for the widget from my view so i have something like Parent > Child > Child of child > Child of child of child > etc etc ) So i like making use of widgets .

Btw, also got around the cache system, and the content of the widgets as the database schema is cached (though i have a property $delete_cache which allows me to refresh the contents displayed by the widgets)

Also, if i was there, why not trying the themes features right ? so my blog controllers extends from a PublicController and AdminController which decide which theme to apply(is the admin theme, or one of the many for frontend ? ) and which user has which rights (played a bit with RBAC system and got my roles/operations/tasks/items in database same for sessions but nothing to fancy)

Also, i can paginate the records very nicely and add comments to existing posts(with validation) .

I am having a bit of problems understanding the url routing feature, i use a framework based on uri segments, that’s why i believe it’s a bit hard for me, but i’ll understand it at some point .

Ah, the center piece of the blog, which i love , is that, with the help of yii, i can compress scripts and styles in a snap, i have this class, MinifyScripts and a method like :




public static function minimizeJs($scripts)

    {

        $timestamp = 0 ;

        foreach($scripts AS $script)

        {

            $timestamp = (file_exists($file = Yii::app()->theme->basePath.'/js/'.$script)) ? $timestamp+= filemtime($file) : $timestamp+0 ;

        }

        if($timestamp > 0)

        {

            if( ! file_exists($remap = Yii::app()->theme->basePath.'/js/'.$timestamp.'.js') )

            {

                $out = '';

                foreach($scripts AS $script)

                {

                    if(file_exists($file = Yii::app()->theme->basePath.'/js/'.$script))

                    $out.= file_get_contents($file);

                }

                $out = JSMin::minify($out);

                file_put_contents($remap = Yii::app()->theme->basePath.'/js/'.$timestamp.'.js',$out);

            }

            $cs=Yii::app()->clientScript;

            $scriptMap = array();

            foreach($scripts AS $script)

            {

                $scriptMap[Yii::app()->theme->baseUrl.'/js/'.$script] = Yii::app()->theme->baseUrl.'/js/'.$timestamp.'.js';

            }

            $cs->scriptMap=$scriptMap;

            $cs->registerScriptFile(Yii::app()->theme->baseUrl.'/js/'.$timestamp.'.js',0);   

        }

    }



And when doing a call like :




MinifyScripts::minimizeJs(array('jquery.js','jcarousellite.js','easing.js','superfish.js','jquery.prettyPhoto.js'));      



All those scripts are minified and groupped into a single file which is a thing that i acheived in 5 minutes with Yii, but on the other framework took me allot more than this…

I could say that i got pretty far for a single weekend(actually today i was more focused), and i am very very pleased on what yii can do for me.

** add me to your friends list, we may share knowledge together.

I think this is done :)

Good man,

I would just recommend one thing. Even though the minify extension is great, i think that on production is best to pre-compile the scripts in advance, as they dont tend to change that much for regular projects and you avoid server’s extra resources.

When you do change the script, just compile it and ‘remap’ your scripts.

I used to make use of Minifier classes and combination scripts but… I think the suggestion of qian of Qiang is better.