Help to order images by vote count

[wolkmx]

Hi first of all , thanks for this great module.

I search in the forum but i can't find nothing.

I wan to order my images by vote count, i find in the backend a option, but is only avaliable for the categories and not for the images.

It's posible to order the images by vote count? what i need to do or search?

For example the idea is that the users upload photos , so all other users make a vote , so when the users enters to see that category the idea is that the first photo will be the photo whit more votes.

Thanks for the help.

[wolkmx]

Any idea, or somebody who made something like this??

[imperialWicket]

A quick and dirty solution is to edit the category model.

In components/com_phocagallery/models/category.php, modify the end of the _buildQuery function to change the query. Something like this:

Code: Select all

                // ORIGINAL BEGIN
		//$query = 'SELECT a.*'
		//	.' FROM #__phocagallery AS a'
		//	.' WHERE a.catid = '.(int) $this->_id
		//	.$wherecimgid
		//	.$published
		//	.' ORDER BY a.'.$imageOrdering;
		// ORIGINAL END	
		
		// UPDATED FOR VOTE ORDERING
		$query = 'SELECT a.*, count(v.id) AS num_votes'
			.' FROM #__phocagallery AS a, #__phocagallery_img_votes AS v '
                        .' WHERE a.catid = '.(int) $this->_id
			.' AND a.id = v.imgid'
                        .' AND a.published = 1'
                        .' AND a.approved = 1'
			.' GROUP BY a.id'
			.' ORDER BY num_votes DESC';
		// UPDATE END
		
		return $query;

Note that some of the dynamic values that return ($wherecimgid/$published) include unqualified column names, which cause SQL errors. I have hard-coded that images should be published and approved (I believe this is the default).

I didn't test this exhaustively, but it seems to work fine after some brief review. It is worth noting that if you have multiple category displays, this update will affect all of them. In order to support multiple category displays, using distinct ordering, you would need to put a boolean check around this, and base the update on a parameter that is available.

[wolkmx]

Thanks a lot imperialWicket!!, its works but whit a little problem, don't show the images whit not votes, this mean that if a image whit 0 votes, don't show.

I will try to check why, if you can check the code will be perfect, i will check and writte if i find the solution, thanks.

[imperialWicket]

Sorry, that's what I get for writing the SQL too quickly.

Use this query, the LEFT JOIN keeps records from the phocagallery table that have no associated values in the phocagallery_img_votes table. My original post simply ignored them (as you pointed out).

Code: Select all

$query = 'SELECT a.*, count(v.id) AS num_votes'
 .' FROM #__phocagallery AS a '
 .' LEFT JOIN #__phocagallery_img_votes AS v ON a.id = v.imgid '
 .' WHERE a.catid = '.(int) $this->_id
 .' AND a.published = 1'
 .' AND a.approved = 1'
 .' GROUP BY a.id'
 .' ORDER BY num_votes DESC';

Hopefully the second attempt works a little bit better for you.

[wolkmx]

Thanks againg imperialWicket this time works great, jeje i prefer to used your code because is simple and correct, but i find the solution by my own way, of course more ugly but works too making 2 querys jeje, so if somebody needs this, the code works.

And this options could be avaliable in phoca backend.