Subselects in GridView

Yii 1.1 General Discussion
1

Hi there,

I wonder what would be the best way to show complex data in a cgridview. For example I’ve got users who tag images in an application. On the user admin listings I’d like to show the following information.

UserName | email | Top10Tags | …

Peter | peter@dummyllc.com | Cat (5), Dogs (3), House(2), … | …

At the moment I created a method in my User model





public function getTopTags($num_tags=10) {

    $tags = Yii::app()->db->createCommand()

    

    // ...

    // retrieve data from database and parse it to string

    // ...


    return $string_of_tags

}

Which I embedd in the cgridview like that




<?php $this->widget('zii.widgets.grid.CGridView', array(

  'id' => 'users-grid',

  ...

  'columns' => array(

    'id',

    'username',

    'email',

    array(

      'header' => Yii::t('app', 'Top Tags'),

      'type' => 'html',

      'value'=>'$data->getTopTags()',

    ),

  ...

While this works fine I wonder if there is a better way to do such things. I’m particularly worried about performance. As every row in the grid creates another hit in the database. Is there a better way to render the results of subselects in a gridview?

many thanks!

2

Is this really just a lazy vs. eager loading question?

3

Why not switch to the CListView, then you can make a view for each row and customize it to your hearts delight?

4

Do you assemble this from 10 records in a HAS_MANY related table?




Cat (5), Dogs (3), House(2)

Tell us more about the schema.

/Tommy

5

Hi Thanks for your answers. Here some more comments for clarification.

  1. I’m using hasMany expression but it involves several tables. Best I let the some code speak:



$tags = Yii::app()->db->createCommand()

                  ->select('count(t.id) as counted, t.id, t.tag')

                  ->from('{{session}} s') // each logged in user has got a session 

                  ->join('{{game_submission}} gs', 'gs.session_id=s.id') // each user can play one or more games and submit for them n time

                  ->join('{{tag_use}} tu', 'tu.game_submission_id = gs.id') // each submission contains m tags

                  ->join('{{tag}} t', 'tu.tag_id = t.id')

                  ->where('s.user_id=:userID', array(":userID" => $this->id))

                  ->group('t.id, t.tag')

                  ->order('counted DESC')

                  ->limit($num_tags)

                  ->queryAll();

In the user admin grid view I’d like to show the 10 most used tags by user. To first row might be Cat, Dog, Horse and the next row might have house, mountain, lake, …

The project I’m working on is a games platform that crowd sources the task to tag unknown images. All will be open sourced in the end.

If I’d code the view by hand I’d include the user with another join and use some php to extract each rows full information. Thus for a 25 users with 10 tags each I’d get a maximum of 250 rows back. While the result set would be larger and the initial query would take longer. I wouldn’t have to shoot 26-27 (incl. count query) into the database.

  1. CListView would also be an option but I believe I’d still have the same problem: How do I populate efficently each row with the most used tags.

I’ve been doing some more resarch and the MySQL only group_concat could help, Though I’d have to parse the result as I’d expect the tags to be links. There is some info on stackoverflow.com /questions/1831596/subquery-to-string (I’m not allowed to post links, yet)

I just wonder if there is already some nice way to solve such problems. One way I can think if is to provide another array with the needed data for each user and just to fill in the columns per row. The only question for me would be how to I know which rows (and therefore user_ids are going to be displayed before the grid is rendered)?

Just, thinking, that this is not such an uncommon problem …

Many thanks for your comments!

6

Guess I didn’t totally understand the question…

In a high volume application where every millisecond counts, usually I offload statistics tasks, and cache the query results every so often into a table for a snapshot view with cron tasks.

For example in your case, I dont need "real time" stats on the most used tags by users. Create a table for your statistics and you could cron tab a sql script to populate the table nightly. the cron can run when your application has limited traffic.

Generally speaking, when I am doing large scale heavy load architecture, I would move these tasks off the web server and database server entirely but rather host all my scheduled tasks, data delivery/transformation, feeds, synchs on a seperate application server.

7

I agree with the ‘composite’ table idea, but before we go talking about cron’s and keeping the stats current, you just want a solution to solve your problem at hand.

A mysql subquery creates a ‘rendered’ in-memory table that can be aliased to match the name and structure of an actual table in your database. This will allow you to create a yii model, controller and views based on an actual table that can then be utilized to operate on the in-memory table … Add/Delete/Update, etc.

This should be enough to get you goin. Later, when you decide to implement performance measures and switch to the table on disk/cron methodology, you’ll only need to modify your data source parameters.

Little as you think about it the results of a query are nothing more than an in memory table ‘arrayized’.

Good Luck,

the abester