If I use a table join to populate a widget I cant get values from the joined table

Yii 1.1 General Discussion
1

Please pardon me if this is an ignorant question. I’m a self-taught developer with some experience but I’m only a year into using Yii, and Yii1, at that.
It’s possible I’ve made SQL mistakes as well.

I’m trying to make a widget for a site. The widget shows a randomly chosen song and allows the user to play it in an audio player. The randomly chosen song data includes fields from two joined tables.

At first, I tried to pass the values into the widget with a findBySql, like this:

$nowplaying = Catalog::model()->findBySql(
"SELECT c.Title, c.Artist, tr.audioUrl, tr.fk_copyright 
FROM Catalog c 
JOIN Tracks tr 
ON c.pk = tr.fk_Catalog 
WHERE tr.audioUrl IS NOT NULL 
ORDER BY RAND() 
LIMIT 1");

In that case, the values from the Catalog table displayed correctly, but the values from the joined table, Tracks, don’t show up at all. In a var_dump they also don’t appear. But if I run this SQL query in Sequel Pro, I get the expected values, from both tables.

So, I tried again. I learned about the Query Builder in Yii, and created a different method for fetching the data from joined tables, like this:

    $nowplaying = Yii::app()->db->createCommand()
        ->select('c.Title, c.Artist, tr.audioUrl, tr.fk_copyright')
        ->from('Catalog c')
        ->join('Tracks tr', 'c.pk=tr.fk_Catalog')
        ->where('audioUrl IS NOT NULL')
        ->order('RAND()')
        ->limit(1)
        ->queryRow();

In this case the var_dump gives me all the correct data, but nothing shows up in the widget!

the widget code is long, but here’s an excerpt with some example data:

<b><?php echo $nowplaying->Title?></b>
<b><?php echo $nowplaying->Artist?></b>
<audio id="audioSrc" src="<?php echo $nowplaying->audioUrl ?>"></audio>
<div class="audio-credits">
    <?php if ($nowplaying->fk_copyright == 1): ?>
            SHOW CREDIT
         <?php else: ?>
            DON'T SHOW CREDIT
        <?php endif?>
</div>

So, I have two questions:

  1. In my first query, why did I only get values from the first table in the join?
  2. In my query that worked, var_dump showing all requested values from both joined tables, why did nothing show up in my widget?

I guess there’s a third question: 3. Is one of these methods better than the other? And should I even be using a widget? Would RenderPartial be better?

To conclude, I don’t know if this is an issue of me not knowing Yii, or me not knowing SQL. I’m happy to be shown my mistakes, either way.

2

Regarding 2: In which context did you assign to $nowplaying? Did you pass this variable in a render call to the widgets view.

also see the last paragraph https://www.yiiframework.com/doc/guide/1.1/en/basics.view#widget

1 Like
3

Did you pass this variable in a render call to the widgets view.

Yes: $this->render('audio-now-playing-block',array('nowplaying'=>$nowplaying));

In which context did you assign to $nowplaying?

I’m not sure what you mean by context here. Is that something I’ve overlooked?

4

I mean e.g. in run().

1 Like
5

Yes, the query builder and the $this->render() are in public function run(), and that’s all that’s there. Nothing in init().

6

Can you var_dump both in run and view to just make sure that it is indeed passed correctly?

1 Like
7

Yes, i have done that and the var_dump shows all the values I expect, in both places. But still, nothing is coming through in the widget.

So:

 <?php var_dump($nowplaying); ?>
    <div class="audio">
         <h3><?php echo $nowplaying->SongTitle?></h3>
         <h3><?php echo $nowplaying->ArtistName?></h3>
         <?php echo 'url : ' . Yii::app()->baseUrl . $nowplaying->AudioUrl ?>

In this case, the var_dump($nowplaying) gives all the correct data, but nothing shows up in the widget.
(like SongTitle, ArtistName, etc.)

Am I making some very simple mistake, like a syntax mistake? I’m following the pattern established by the previous developer but I can change that if I need to.

8

OK, using the output from the Query Builder style of fetching the data, I was able to populate my fields using old-school Array notation, so $nowplaying[AudioUrl], instead of $nowplaying->AudioUrl, as I had before.

I guess Query Builder returns an Array, and the findBySql technique I was using before returns…an Object? I never thought to check that. I had a feeling it was a syntax mistake I was making.

I’d still prefer to use just one method of fetching the data, and the $nowplaying->AudioUrl style works everywhere else on the site. I wish I knew what was so special about this particular query.

9

Check if it’s dependent of the first param (I don’t know)
https://www.yiiframework.com/doc/api/1.1/CDbCommand#queryRow-detail

10

Thanks for considering this.I don’t know if it’s dependent of the first param, but I think it is. I think queryRow($fetchAssociative=true) must be the default, because, if I understand the docs, that means it makes the named Array fields available, instead of the index-based ones. I did try using $nowplaying[0] to see if that would show a value. I can’t remember if it did, but using the named array key, like $nowplaying[Title] does work. And that’s enough for me to get my website working for now.

All my other queries of this type seem to produce a Object as the result, and I’d like to stick with that (for consistency, no other real reason), but as long as it shows the data I need, an Array will be fine.

1 Like
11

Hi, my thougths on this issue.

The method findBySql() is called from a CActiveRecord, Catalog.
The query result populates this CActiveRecord, and you can access the attributes of this model with object notation, →
BUT do you have the columns from the Tracks table defined in the Catalog model?
If not, those attributes from the SQl result “have no place” in that model. Probably the are to find in a Tracks CActiveRecord.

A way to do it is to define Tracks as an relation and use e.g. the find() method of Catalog.
The Tracks attributes are then retrieved with Catalog->Tracks->audoUrl etc.

You can also eager load data from both tables with the “with” method.
See Working with Databases: Relational Active Record | The Definitive Guide to Yii 1.1 | Yii PHP Framework

The QueryBuilder version correctly retrieves the data from both tables.
The queryRow() method always returns an array, that’s why you need to use array notation here.
From CDbCommand api doc: “the first row (in terms of an array) of the query result, false if no result”.

Note! In this case no ActiveRecord is involved

regards,
Gunnar R

1 Like