Yii Framework Forum: ActiveRecord question - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

ActiveRecord question Rate Topic: -----

#1 User is offline   Kaerigan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 05-December 08
  • Location:Sweden

Posted 12 December 2008 - 02:59 PM

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?
0

#2 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 12 December 2008 - 04:08 PM

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

0

#3 User is offline   Kaerigan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 05-December 08
  • Location:Sweden

Posted 13 December 2008 - 06:54 AM

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.
0

#4 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 13 December 2008 - 07:37 AM

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.
0

#5 User is offline   jonah 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 733
  • Joined: 27-November 08
  • Location:California (US)

Posted 13 December 2008 - 03:15 PM

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'
0

#6 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 13 December 2008 - 08:15 PM

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.
0

#7 User is offline   Kaerigan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 05-December 08
  • Location:Sweden

Posted 14 December 2008 - 01:38 PM

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?
0

#8 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 14 December 2008 - 01:59 PM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users