Help me to understand relation rules

Archives Yii 1.0.x Discussions
1

I am old school. In the past I generated all my own queries and I’m real good at it, also debugging them. So all this

framework cruft seems a bit burdensome. Anyway I have a rule which I basically modeled after the blog example, the idea is to get all the tags. The Tag table just has id and tagText, since its Many_MANY I have a join table. So

I put this rule:


'tagFilter' => array(self::MANY_MANY, 'Tag', 'MainTag(mainId, tagId)',

                'together'=>true,

                'joinType'=>'INNER JOIN',             

                'condition'=>'??.tagText=:tag', //??

It’s borking thus when it tries to do it:

[font="Impact"]CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens[/font]

All I see is the code that invokes the rule that caused it which is not helpful because I already KNOW that.

I can’t see the actual database queries.

In the end what I want is


SELECT tagText from Tag t INNER JOIN MainTag mt ON t.id = mt.tagId  WHERE mt.mainId = <whatever the id is for the AR>

If I had some way to see what prepared query it is actually generating I could figure these things out.

Then obviously after the data has been given to the query I could also see the actual query MySQL is trying to run.

I don’t understand ‘together’ and I don’t understand condition. What are they referring to.

Is there some debug option to make it actually poot out the queries?

2

See this thread

http://www.yiiframework.com/forum/index.php?/topic/4349-sql-debugging/page__hl__sql%20logging__fromsearch__1

Also add enableParamLogging to the db component config




'db'=>array(

  'connectionString'=>' ...  ',

  'enableParamLogging'=>true,

  //'enableProfiling'=>true,

),

/Tommy

3

Thank you very much, that was illuminating. Delving into the logs I was able to see my error and create the relation I really wanted. Now however I would like to retrieve the data from one of those MANY_MANY relation objects that (thanks to you) I am getting out of the database.

So hypothetically we have our main AR object called $model. And by calling upon one of these relation things from our main controller we have managed to lazyload an attribute called $relation. So in my view script just for yuks

I viewed the object in the debugger. Lo and behold it is a hugely complex object with schema info and all sorts of

descriptors. The actual data values are buried in there. That is NOT what you’d get if you just ran the query.

OK so here I am in my view script with this enormously complex thing called $relation. Are there some Yii routines to drill me down to the data values so I can display them to the viewer, and get rid of all that structure.

4

IIRC, from the top of my memory, you probably want to use something similar to




foreach ($modelRecs as $n=>$model)

  foreach ($model->relatedRecs as $m=>$related)

    echo $related->someAttribute;

Edit:

‘relatedRecs’ corresponds to ‘tagFilter’ in your example.

/Tommy

5

well i answered my own question, despite all the gobbeltygook in these objects, it seems that just using the normal

attributes as if it were a normal php object works. I tried looking at another one I had success with previously and it was just as gnarly internally.

I still don’t quite understand how we get from what I know (how to do relational queries) to what I don’t (how to set up those relation things in Yii so they work) because I don’t know the correspondences.

whenever i did prepared queries before I always just used questionmarks "SELECT * FROM table t WHERE t.id = ?"

and then when I did the exec I fed it the data in order. I see the advantage of named placeholders, it is the same as associative array. I guess you can call the placeholders whatever you want but that must be a PDO thing because it sure is not a native MySQL thing and I am not sure how Yii knows which data to match up with which placeholder.

6

I just found an older thread you might find interesting:

http://www.yiiframework.com/forum/index.php?/topic/3510-activerecord/page__hl__schema__fromsearch__1

/Tommy

7

Thank you in very deed. I wish the search function on these things was better. All forums software seems to have pretty poor search, because I do look before I post. :)