Friday, October 14, 2011

Updating a Listview in Yii with Ajax

This was an unbelievable exasperating feature to add. I have no idea why this didn't work in the myriad ways I tried to make it work, but I've got it working, now, so I wanted to post.

I looked at every example of updating a CListview that I could find on Google. None of them were doing what I wanted to do (and what I'd assumed was a very common task). In a nutshell, I wanted to have a listview on my view page and then update the results when someone clicked on a link. All of the examples I saw involved submitting a form. I saw one that was using a link, but the details provided in how they solved it were so miniscule as to be almost humorous (had I not been so frustrated).

I won't discuss this solution in great detail. I'll simply include the information below.

The View
At the top of the index.php view, I have this:

<?php

Yii::app()->clientScript->registerScript('ajaxUpdate',
"
$('.ajax_link').click(function(){
$.fn.yiiListView.update('companyList')
});
return false;
", CClientScript::POS_READY);
?>

Inside the view itself is the listview:


<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view',
'id'=>'companyList',
)); ?>

The links are generated in the view, too, of course. They look like this:


foreach($allLetters as $letter) {
if(in_array($letter, $dbInitials)) {
/*
echo CHtml::ajaxLink(strtoupper($letter), CHtml::normalizeUrl(array('index', 'initial'=>$letter)),
array('success'=>"$.fn.yiiListView.update('companyList')")
);
*/
echo CHtml::link(strtoupper($letter), CHtml::normalizeUrl(array('index','initial'=>$letter)), array(
'class'=>'ajax_link',
));
} else {
echo strtoupper($letter);
}
// Add some whitespace.
echo '&nbsp;&nbsp;';

}

In fact, that's pretty much all there is in my index.php page. A Javascript portion that is registered as a client script, a list view with the id set to companyList so I can refer to it later, and a series of basic html links with a class of ajax_link so that the click event is triggered in the Javascript.

On the server side, the controller action for index looks like this:

public function actionIndex($initial='')
{
if($initial) {
$criteria = new CDbCriteria;
$criteria->compare('name', $initial . '%', true, 'AND', false);
} else {
// Retrieve all companies to list
$criteria = new CDbCriteria;
$criteria->with=array('video','image','state');
$criteria->together = TRUE;
}
// Only admin users can see ALL records. Others can only see approved records.
if(!Yii::app()->user->checkAccess('admin')) {
$criteria->addCondition('t.isApproved=:isApproved');
$criteria->params[':isApproved']=1;
}
$dataProvider=new CActiveDataProvider('Company', array('criteria'=>$criteria));

$this->render('index',array(
'dataProvider'=>$dataProvider,
'dbInitials'=>$this->dbInitials,
'allLetters'=>$this->allLetters,
));
}

If no parameters are submitted, it just renders all of the results. If an initial letter is submitted, it filters the results. Either way, the index view is rendered.

The main problems I encountered were that:
  1. The AJAX request would return the results I wanted, but the div wouldn't update.
  2. Once I finally got the div to update, I was getting an error from the yiiListView.update() method. Adding the "return false" in the Javascript code fixed that.
Done.

Thursday, October 13, 2011

Yii: Selecting First Letter of a Column in Each Row

At some time or other, I'm sure you've browsed names of companies or people and were presented a list of initials to filter your results by letter or other character. If the search results don't contain any companies or people with a particular letter, it is nice when that letter is inactive, that is when it doesn't link to anything. Otherwise, you just get an annoying "no results" response. Shouldn't the application have known in advance that there were no people in the database whose name started with Z? Now, you've wasted your valuable time clicking it for nothing!

I'm working on building just such a list of letters and characters, and it's surprising to me, but I've never actually gone through this exercise of creating the list with anchor tags only around those letters that correspond to values in my db. I'm going to blog my steps in short posts. This will cover only retrieving the initials. Subsequent posts will cover the steps to output the results.

I'm pulling from a table called "Company." I want just the first letter of each company name. In this case, the name is stored in - can you guess? - the "name" column. So, my basic SQL looks like this:

"SELECT DISTINCT substring(name,1,1) AS initial FROM Company"

This should return the first letter (a substring of one character, beginning with the first character) of the name column from the table Company. 'DISTINCT' ensures I only get one of each.

But I'm using Yii, so I need a way to execute this query the Yii way. (Or one of the Yii ways, anyhow.) I decided to use CDbCommand with a series of available setters. Here's what that looks like:


$dbCommand = Yii::app()->db->createCommand();
$dbCommand->setSelect(array('substring(name,1,1) as initial'));
$dbCommand->setFrom('Company');
$dbCommand->setDistinct(true);

That's pretty straightforward, if you have taken a look at the CDbCommand info in the Yii documentation. In a nutshell, I've instantiated the command object. Next, I use setSelect() to provide my select information. In this case, I'm using an array. You could include it as a string (e.g., $dbCommand->setSelect('myname as name'). I'd come across a post that suggested that there were issues with using the string, however, and  so I decided to just go with an array.

The call to setFrom() is probably self-explanatory; it just provides the "from" values for the query. I used a string, because I had only one table. More than one value in from would require you to use an array instead.

The use of setDistinct() is so that I can keep my results, well, distinct. It takes a boolean (true or false) as it's value. I want it to return distinct results (only one of each), so I set it to true.

Oh, you might have noticed that I used Yii::app()->db->createCommand() instead of new CDbCommand. This is a convenient way of taking advantage of the 'db' component I've already set up for my app. If I were to instantiate a new CDbCommand object with the "new" keyword, I'd have to supply the connection information. By using the createCommand() function, I can avoid all that and just use the connection info set up in my app. (If you were connecting to a different database, you'd need to do this the long way with "new.")

OK, now how do I get the results? I've built up the command itself. Now, I just need to actually execute it. I went with one of the convenience functions provided by CDbCommand, "queryColumn()." It returns one column of results, which is exactly what I need, and stores it in an array, like so:

$dbInitials = $dbCommand->queryColumn(); // First letter of the company names from the db

If I had wanted only the first row of information, I could have used queryRow(). The queryScalar() function returns just the first column from the first row. There are a couple of other query functions, and there's also just plain execute(). Check them out in the documentation to see what works best for your situation.

So, that's it for this installment. I've now got an array of letters that I can loop through and create links for filtering. Of course, I want to include the other letters that didn't end up in my $dbInitials array, but I'll save that for the next post.


Feel free to post suggestions if you have better ideas of how to do this. I'd love to hear how this could be made more efficient or otherwise more intelligent. Thanks!

Tuesday, September 20, 2011

Useless SVN Error Message: Network connection closed unexpectedly « Ben J. Christensen

This is VERY helpful if you're trying to use svn+ssh with an alternate port number and you get the vague message about the network connection closing unexpectedly:

Useless SVN Error Message: Network connection closed unexpectedly « Ben J. Christensen

Before you go there, however, if you have shell access on the remote host from which you're trying to check the project out, be sure to test locally:

svn list svn+ssh://username@localhost/path/to/project/repository

If you don't have a port number specified in ~/.ssh/config, you should first try:

svn list svn+ssh://username@localhost:1234/path/to/project/repository ('1234' in place of your port number)

If that doesn't work, open up ~/.ssh/config (you may need to create it) and enter this:

Host yourhost.com
User username
Port 1234

In the case of testing on localhost, I used this config file:

Host localhost
User myUsername
Port 1234

Literally use the host, "localhost."

Once I knew that worked, I could try remotely, at which point I got the seemingly meaningless error. So, I then created the config file on my local machine.

Good luck!

Sunday, September 11, 2011

Dana Luther on Yii's AuthManager Using the Database

Dana Luther is "da bomb" as they say. Her guides are well-written and super helpful. Her blog is one of my go-to choices when I'm stymied or confused by something in Yii.

Here, for example, is a great article on setting up Yii's built-in AuthManager to use your application's database:

Adventures in Programming: Yii Authentication via database