#gamesite

How often do you find yourself coding a website and manually typing in some sample data into your forms or your database to see what a real result could look like?

If the answer is anywhere between "Dunno, every now and then" and "A gajillion times each day!", get used to using a data faker.

And it's pretty simple:

Get a copy of a faker library, like this one: caius' php-faker. If you are reading this and it has not yet been merged in, I also very much like zanshine's improvements to the faker.

You put that dir into your project and then you can use it. See the index.php inside for all the commands you can use and what they give you back.

The easiest way to use it would be to write a new php file inside that directory and open it to seed your database.

Here a sample from my gamesite project, relating to the ranking and user models I posted about earlier in my other post CakePHP (2.0) - Paginate Model with $hasMany Association - with custom join, group, order by other model

Yeah, that's right. Try to digest that title for a moment.

Looking for this solution took me like a day before I could even formulate my specific problem and was the reason to open this blog. To give you what I wish I had.

And once you know what you are trying to do it's not even that complicated. (Cut me some slack, I'm still starting out and this is my first CakePHP project)

So we have a website that displays high scores of users:

UserModel // id, username, ... $hasMany = 'Ranking';

RankingModel // id, score, ..., user_id $belongsTo = 'User';

Task: Simple. Now we want to display the users in the UsersController along with their highest score, the highest ranking user first. Only some at a time, of course.

The View looks like this:

<?php foreach($users as $user): ?> <div class="userindex">     <h1><?php echo $user['User']['username']; ?></h1>     <p>       Best Score:       <?php         echo ($user['Ranking']['score']) ? $user['Ranking']['score'] : "None"; ?>     </p> </div> <?php endforeach; ?>

Sooo how do we get the data in?

First you have to understand how the hasMany relationship works. While belongsTo requests both associated tables (users and rankings) with an SQL JOIN right away (so you can access them in the pagination immediately), the hasMany requests all the user data first, then caches the IDs of those users, and then does a new request for all the rankings relating to those user IDs.

But we don't want ALL the rankings that belong to the displayed users! We don't need all their columns and most of all we want to display the users by the RANKINGS.

That means the users would be requested up to the set LIMIT regardless of their rankings, ordered by their ID or something else, and after you get their ranking information the best you could do is order the users you already have by the rankings they got, even if those users only own mediocre rankings compared to all other users.

Solution: To get what we want we have to force a table join in the first request, then find the highest rankings of unique users on the database level, and then get them ordered by this newly generated column.

Generally it helps if you type out the database query step by step and test it in our admin panel:

SELECT User.username , MAX( Ranking.score ) AS score /* That MAX gives us the highest score in the table. Giving it an alias is just good practice.*/ FROM users AS User LEFT JOIN rankings AS Ranking ON User.id = Ranking.user_id GROUP BY User.id /* If we didn't group by User.id, it would only return the highest ranking overall. */ ORDER BY score DESC LIMIT 18

To get this query in a CakePHP pagination (or find('all'); for that matter) we'd have to do the following:

// UsersController public $paginate = array(     'fields' => array(       'User.username',       'MAX(Ranking.score) as score'     ),     'limit' => 18,     'joins' => array('LEFT JOIN rankings AS Ranking ON User.id = Ranking.user_id'),     'group' => 'User.id',     'order' => 'score DESC',     'recursive' => -1   );

Yes, turned out simpler than I would have thought earlier. Now don't ask me about the strange 'joins'. This must be a bug in CakePHP 2.0stable. It only worked this way for me, the pure SQL code inside an array. They will probably fix this at some point so you can use the standard parameters that joins-option allows you.