ActiveRecord question

First of all, sorry for asking so many questions and not giving them very descriptive titles, but I really didn't know what to call this one. In my current project, I have a few models. There's for example the forum thread which belongs to a forum category. What I want to do is to fetch all forum threads depending on the value of an attribute (a column?) of its relation, forum category. Some of the forum categories has the attribute hidden set to true, and I only want the user to be able to see forum threads in these categories if a certain condition is satisfied. I would very much like to solve this with ActiveRecord, so I wonder, how would I go about doing something like this?

I realise that you can supply a condition in the relation between forum thread and forum category, but it does not seem suited for this thing, as the condition will vary, depending on the ID of the current user and this condition does as far as I can tell not allow parameters.

I could perhaps put it in $condition part of a call to findAll, but then I would need to manually enter the table name, and I don't think that you're supposed to put conditions regarding a model's related models there.

A third alternative would be looping through all forum threads and then ignoring those that satisfy this condition. Like (in a loop):

if ($thread->category->hidden) continue;

But that would be a waste.

Or is this perhaps considered an "advanced" query and should be created using the DAO directly instead?

You may use:



Post::model()->with('Category')->findAllBySql($sql,$params);


where $sql could be something like this:



SELECT Post.* FROM Post, Category WHERE Post.categoryID=Category.id AND Category.visible=true


I guess I could do that, but what if I feel like, for some reason, changing table names? Is there some nice-looking way of doing this? That was one of the few things I actually liked about CakePHP - if you had a model called Post you could refer to it as Post in your queries no matter what the actual table name was, I think, could just be my bad memory acting up again.

The database schema is considered to be part of the application. Therefore, any name changing (column name, table/view name) would inevitably involve some code change (even if you use AR). I don't know how CakePHP does that to eliminate this requirement, but if you know, please let me know. Thanks.

How cakephp eliminates the requirement is that you don't write the sql, the quarry is written in an array and than cakephp translates it into a sql quarry…  For instance, here is a find() I wrote in cakephp once:

<?php


		$user = $this->User->find('first', array(


			'fields' => array(//fields to find


				'User.id', //Note that "User" refers to the model name, not the table name


				'User.full_name',


				'User.created',


				'Group.name', //I had another model called "Group".  This quarry would generate a sql JOIN


				'Group.id',


			),


			'conditions' => array(//conditions


				'User.email_confirmed' => null, //only show users with confirmed accounts


				'User.id' => $id


			)


		));

It would generate the following sql:

SELECT User.id, User.full_name, Group.name, Group.id FROM users AS User LEFT JOIN groups AS Group ON (User.group_id = Group.id) WHERE User.email_confirmed IS NULL

Note the "AS" statement thrown in there.  That way it can translate the table name to the model name.  Here my table names are 'users' and 'groups' while the model names are 'User' and 'Group'

jonah, thank you for your explanation. That is good to know, but I think Yii won't go this way because it is mixing model class name with column name, which could be confusing in some cases.

In Yii besides findBySql, you can also do the following:



Post::model()->with('Category')->findAll(array(


    'join'=>'LEFT JOIN Category ON categoryID=Category.id',


    'condition'=>'Category.visible=true',


));


It's very similar to the findBySql way, not as intelligent as in CakePHP.

Thanks for explaining, jonah.

The reason I'm concerned with this is like, what if you were to write a CMS or something and people want to deploy on one of those cheap-ass web hotels that only allows one database and need table prefixing?

AR is not meant to solve all DB queries, and your CMS will more or less contain some SQLs. Therefore, you always need to pay attention to table prefixes if they are used when dealing with SQLs.