[eluser]spyro[/eluser]
I have read a few other posts on this subject but have not seen what I was looking for. I am using the CI pagination class and decided to sort my on a certain value in desc order. The problem is that when I request each section of data from the database I am specifying an offset and then sorting only those results.
Example ( I am sorting on total reviews )
Reviews 1
Reviews 3
Reviews 20
on the second page I will have
Reviews 2
Reviews 5
Reviews 16
When what I really need is for these to be in order across all pages. I know this have been done a thousand times but this is my first. Please advise.
[eluser]dmorin[/eluser]
How are you sorting, in SQL (order by) or in PHP? If it's in SQL it should work, if it's not, then that is your problem and you need to either figure out how to sort in the query, or use a different pagination library.
[eluser]spyro[/eluser]
I am doing both because it sorts by a rating ( order_by ) and by number of reviews which is a second query, then the whole thing is sorted in PHP to put the most reviews at the top. Guess I need to find another pagination library.
[eluser]dmorin[/eluser]
Or, if you explain the two queries and how they're related, we can help you write a single query that'll work with this library!
[eluser]spyro[/eluser]
I started writing a query but it ended up crashing my mysql server.
Here is the original
Code: function getMechs($cityState,$num,$offset,$category=0,$withReview=1)
{
//this function takes a city state pair separated by a space
$CI =& get_instance();
$CI->load->model('datamanager_model');
//echo "getMechs Category: " . $category;
$CI->load->model('reviews_model');
$cityState = parseCityState($cityState);
$city = $cityState['city'];
$state = $cityState['state'];
$this->db->select('id,name,billing_address_street,
billing_address_city,billing_address_state,billing_address_postalcode,
billing_address_country,website,description,eighthundrednumber_c,
mechanixlooprating_c,extension_c,petfriendly_c,womenowned_c,providearide_c'
);
$this->db->from('accounts');
$this->db->join('accounts_cstm', 'accounts_cstm.id_c = accounts.id');
$this->db->order_by('premiummember_c','desc');
$this->db->where('billing_address_state',$state);
if(!$category==0)
{
//if the category is not set to 0 then use category in where clause
$this->db->where('category_c',$category);
}
$this->db->where('billing_address_city',$city);
$this->db->order_by('mechanixlooprating_c','desc');
$this->db->limit($num,$offset);
$query = $this->db->get();
//push results onto the array
$queryResults = $query->result();
if(!empty($queryResults))
{
//convert the object to an array to stay consistant with other models
$queryResultsAr = object_2_array($queryResults);
foreach($queryResultsAr as $key => $result) {
$queryResultsAr[$key]['reviewsTotal'] = $CI->reviews_model->getReviewsTotal($result['id']);
}
//print_r($queryResultsAr[0]);
//this will return a single review with each mech result
if($withReview == 1)
{
//get a review for each
foreach($queryResultsAr as $results)
{
$review = $CI->reviews_model->get_reviews($results['id'],1,0);
if(isset($review[0]['description']) && $review[0]['description'] != "")
{
$review = str_split($review[0]['description'],75);
$review = $review[0] . "...";
}
else
{
$review = "";
}
$results['reviewDesc'] = $review;
$data[] = $results;
}
//print_r($data);
$data = vsort($data,$id="reviewsTotal",false);
return $data;
}
else
{
return $queryResultsAr;
}
}
else
{
return 0;
}
}
Then I added a sub select which caused it to become unresponsive.
the get_reviews function is to get one related review to put in the list view.
the getReviewsTotal call get the total reviews as named.
I added this: (SELECT count(*) from mech_reviews_cstm where userid_c = accounts.id and inactive_c = 1) as review_count'
to the first select statement to replace getReviewsTotal and the server became unresponsive.
Also, you may notice the object_to_array function. I just found the result_array() functionality and haven't substituted yet.
[eluser]dmorin[/eluser]
This might be pretty off, I obviously haven't tested since I don't have your db schema. I had to guess that you have an id column in the reveiws table, if not, change the count lines. This also doesn't pull back a single review so you'll have to keep that part of your code. Let me know if this doesn't work and you can't figure it out and i'll work on it some more.
Code: <?php
$this->db->select('a.id, a.name, a.billing_address_street,
a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,
a.billing_address_country, a.website, a.description, a.eighthundrednumber_c,
a.mechanixlooprating_c, a.extension_c, a.petfriendly_c, a.womenowned_c, a.providearide_c,
count(mrc.id) as num_reviews'
);
$this->db->from('accounts a');
$this->db->join('accounts_cstm ac', 'ac.id_c = a.id');
$this->db->join('mech_reviews_cstm mrc', 'mrc.userid_c = a.id AND mrc.inactive_c = 1', 'left');
$this->db->order_by('count(mrc.id)', 'desc')
$this->db->order_by('a.premiummember_c','desc');
$this->db->where('a.billing_address_state',$state);
if(!$category==0)
{
//if the category is not set to 0 then use category in where clause
$this->db->where('a.category_c',$category);
}
$this->db->where('a.billing_address_city',$city);
$this->db->order_by('a.mechanixlooprating_c','desc');
$this->db->limit($num,$offset);
$query = $this->db->get();
//push results onto the array
$queryResults = $query->result();
?>
[eluser]spyro[/eluser]
I think that we are getting closer.
I need the count to only total where the mrc.userid_c = a.id
I tried this
Code: $this->db->select('a.id, a.name, a.billing_address_street,
a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,
a.billing_address_country, a.website, a.description, ac.eighthundrednumber_c,
ac.mechanixlooprating_c, ac.extension_c, ac.petfriendly_c, ac.womenowned_c, ac.providearide_c,
count(mrc.userid_c = a.id) as num_reviews'
);
and
Code: $this->db->select('a.id, a.name, a.billing_address_street,
a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,
a.billing_address_country, a.website, a.description, ac.eighthundrednumber_c,
ac.mechanixlooprating_c, ac.extension_c, ac.petfriendly_c, ac.womenowned_c, ac.providearide_c,
count(mrc.userid_c) where mrc.userud_c = a.id as num_reviews'
);
but both gave errors.
[eluser]spyro[/eluser]
I am sure the error would help. It is trying to count from accounts.
A Database Error Occurred
Error Number: 1054
Unknown column 'count(mrc.userid_c)' in 'field list'
SELECT `a`.`id`, `a`.`name`, `a`.`billing_address_street`, `a`.`billing_address_city`, `a`.`billing_address_state`, `a`.`billing_address_postalcode`, `a`.`billing_address_country`, `a`.`website`, `a`.`description`, `ac`.`eighthundrednumber_c`, `ac`.`mechanixlooprating_c`, `ac`.`extension_c`, `ac`.`petfriendly_c`, `ac`.`womenowned_c`, `ac`.`providearide_c`, `count(mrc`.`userid_c)` as num_reviews FROM (`accounts` a) JOIN `accounts_cstm` ac ON `ac`.`id_c` = `a`.`id` LEFT JOIN `mech_reviews_cstm` mrc ON `mrc`.`userid_c` = `a`.`id` AND mrc.inactive_c = 1 WHERE `a`.`billing_address_state` = 'Ga' AND `a`.`billing_address_city` = 'Atlanta' ORDER BY `count(mrc`.`id_c)` desc, `a`.`premiummember_c` desc, `a`.`mechanixlooprating_c` desc LIMIT 6
[eluser]dmorin[/eluser]
It sounds like CI might be escaping the whole count block of text causing mysql to look for it as a column. The problem isn't that it's trying to count from account, it's that it's looking for a column named "count(mrc...)" What version of CI are you using? If 1.7.0 I would recommend upgrading to 1.7.1 as it deals with db escaping much better.
A way to test this is the set the second parameter of the SELECT method to FALSE. So:
Code: $this->db->select('a.id, a.name, a.billing_address_street,
a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,
a.billing_address_country, a.website, a.description, a.eighthundrednumber_c,
a.mechanixlooprating_c, a.extension_c, a.petfriendly_c, a.womenowned_c, a.providearide_c,
count(mrc.id) as num_reviews', FALSE);
let me know if that changes anything.
EDIT:
I also just looked more closely at what you tried and it looks like you're trying to include where-like strings in your select which is bad. Things like "mrc.userid_c = a.id" are done in the JOIN criteria or the Where portion, NOT in the select. The exception is when you're doing an entire subquery, but those are usually really bad/inefficient.
EDIT2:
I just noticed you posted the resulting SQL and I was right that CI is adding ticks around the whole count item. Try setting the second param to FALSE as I mentioned above and see what that does.
[eluser]spyro[/eluser]
I am still on CI 1.7 but can upgrade. Adding the FALSE param fixed the query but the count is counting all reviews ( mrc.id ) which make mysql unresponsive. It needs to only count where mrc.userid_c = a.id. I tried adding a where but received another error.
|
