Relational Active Record questions

Archives Yii 1.0.x Discussions
1

Trying to use the nice Relational Active Record functionality, but currently I seem to want to do some impossible things with it.

Perhaps there is a way to get these things done anyway, so hopefully you can enlighten me.

  1. Now every column alias is ignored, so this is not possible:


$criteria->select = 'id, name, count(*) AS optioncount';


$criteria->order = 'optioncount DESC, postcode1';


$criteria->group = 'id';

The solution would be to leave all explicit column aliases untouched in AR.

  1. Missing HAVING in CDbCriteria. There are ways to get it in anyway, but is cleaner to have it defined.

  2. I would like to use the (already defined in model) join tables and create selection rules for it in the WHERE clause. This works for 1:1 tables (and unique column names), but not for M:M tables.

  3. Can't prefix WHERE clauses referring joined tables with (real) table name, because AR will generate an internal alias for the table. Unfortunately after aliasing, the original table name is not known anymore in the query (MySQL).

Would be great to use VarName (the name of the relationship), or replace these prefixes with the generated alias (or use some bind form like :prefix1).

2

I don’t think something involved ‘group by’ and ‘count’ fit well with the whole OR mapping idea, AR meant to treat data as Object, I think DAO would be better for these job :)

3

I don't see any reason why not, a 'group by' makes perfect sense in  ®AR, and a 'count(*)' used for sorting is helpful too. I am using the data as an object, but like to mold the data so that it suits me.

Sure it works with DAO, but then I will loose my relations. Sure it can work in any AR, but this is a specific question for use with RAR and already defined relations.

RAR queries are very clean, and using it may be a fraction slower than DAO, but for the added convenience I'm happy to have that ms wasted.

Managed it with FindAllBySql() for now, but kinda defeats the RAR, since I create the joins there by hand. Can use relations so that is still very nice.

4

Did you try the findAllBySql method??

5

Maxximus, thank you for your findings about RAR. Below are my answers to your comments.

  1. This should work. If not, could you please create a ticket to report the issue? Thanks.

  2. Will add it. Again, could you please create a ticket?

  3. I don't quite understand the requirements. Could you explain a bit more?

  4. Did you try using the prefix "??" (two question marks, the default prefix)?

6

well, I was wrong, I realize I was too far from understanding the AR. RAR do provided a lot of things to define the model, using the grouping, counting stuff to define useful infomations in model class, and provided a placeholder ?? to handle the alias problem, but it doesn't seem like to be used in query.

7

Thanks for your answers. Created tickets for 1 and 2.

For 3 and 4, often one of the criteria is in a related table, and not in the 'main' table. So I want to narrow down the returned qty of objects by adding these to the $criteria->select list, and perhaps they need to go into the with() part of the relational query. So, perhaps I'm trying to do things at the wrong place. 

But according to the Guide: We mentioned that additional options can be specified in relationship declaration. These options, specified as name-value pairs, are used to customize the relational query.

So probably before explaining a lot while doing it at the wrong place: Can with() accept these options like CONDITION as well? If so, do this with a CDbCriteria instance, or just as array?

8

These additional relational query options are specified in relations() when you declare related objects. For example, in Post class we declare 'comments' and we can specify 'select' to fetch only the needed columns of the Comment table.

I am not sure if this fits your need.

9

Thanks, but probably not, since these will be static. Lets take an example from the Guide:



$posts=Post::model()->with('author','categories')->findAll();

This will return all posts available with all author and categories relations. What if I have Category.name and User.email, and want to limit the above, I would write the following condition:



$criteria=new CDbCriteria;


$criteria->condition='Category.name=:name AND User.email=:email';


$criteria->params=array(':name'=>'name', ':email'=>'e@mail');


$posts=Post::model()->with('author','categories')->findAll($criteria);

But that will error. Using only email works (without User. or ??.), because thats a BELONGS_TO relation, and that relation will be joined immediately.

If I could add it to the with() relation connector, it would make even more sense perhaps, something like with(array('author'=>array('condition'=>'??.email=:email'))). Any suggestions?

10

It makes sense to me. Besides parameter binding, what else could make the relational query "dynamic" ?

11

If with() could take the Relational Query Options it would cover all ground probably, even highly unlikely combinations. Same limitations as with the 'static' definition will apply, and maybe some others like limit and offset.

But that might be a bit much. There will be some pitfalls too probably.

What will help is to make parameter binding possible for all parts of a CDbCriteria (even column names and table names). That might even be a solution for another AR question, about hard-coding table names.

I know there can be an issue with backtics (when to use them). Personally I would like backtics out unless specifically requested, since they are not SQL, but apparently people like to use reserved words as column names.

RAR is already a very nice part of Yii, and allowing a bit of additional dynamic will make it an even more highly valued asset of the framework!

12

Nice thing could be if relation may be defined by simply SQL code. For example, I need some complex aggregation, but still want to use these value(s) as a part of relation. As for me, it’s much more simple to write SQL query, than digging the docs :)

That's why I want this.