Welcome Guest, Not a member yet? Register   Sign In
Pagination with sorting
#11

[eluser]dmorin[/eluser]
Not sure I understand. Your query includes the join condition "LEFT JOIN `mech_reviews_cstm` mrc ON `mrc`.`userid_c` = `a`.`id` AND mrc.inactive_c = 1" so it should only be counting where mrc.userid_c = a.id already.

If MySQL is becoming unresponsive, it probably means that you're missing indexes on some columns that should have them.
#12

[eluser]spyro[/eluser]
Maybe I left out some information or am not understanding the query.

The goal with the join is to only get 1 review from the system and not all related reviews.

The count is to get the total related reviews for display as a number.

Does that make sense?
#13

[eluser]dmorin[/eluser]
No, in a previous post I said: "This also doesn’t pull back a single review so you’ll have to keep that part of your code", meaning the point of this was only to get the pagination to work correctly by getting all of your sorting into this query. Therefore, the goal of the join is to join on every review that matches the criteria and then count the number per account record.

Then you can still get your single record the way you were previously, or you can do another join just for the single record. i would go with the first and keep it easy until you get the pagination working.
#14

[eluser]spyro[/eluser]
Sorry, through all the changes I forgot about that sentence.

The query that I have now is as follows

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.id_c)',FALSE
        );
        $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_c)', 'desc');
        $this->db->order_by('ac.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('ac.mechanixlooprating_c','desc');
        $this->db->limit($num,$offset);
        $query = $this->db->get();

The query now executes but the results are no longer correct. I am only getting 1 record back.

How do I show the query that CI is using?
#15

[eluser]dmorin[/eluser]
var_dump($this->db->last_query());
#16

[eluser]drewbee[/eluser]
Code:
echo $this->db->last_query();
#17

[eluser]spyro[/eluser]
Thanks. The query now comes out to be:

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.id_c)
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, ac.premiummember_c desc, ac.mechanixlooprating_c desc
LIMIT 6

If I run the select from accounts with the where then I get the correct number of records. If there are no related reviews in the LEFT JOIN would it still send back the account portion just without the review count?
#18

[eluser]drewbee[/eluser]
LEFT JOIN's will return the rows if it finds a match, or NULL columns if it can't find the match.

If you use a RIGHT join, it will not return the entire row if it can't find a match.
#19

[eluser]spyro[/eluser]
Good information. Which also leads to say that the left join is not my problem. Any ideas on what is?
#20

[eluser]drewbee[/eluser]
Nope. But I don't Understand your problem Smile I thought we were good at the point where you said 'I get the correct number of records'.

Let's start over. Your ultimate goal of this query is to Get the account information (accounts). Is their a one-to-one relationship or one-to-many relationship with accounts_cstm? After that is joined, you simply want a count of all the records from mech_reviews_cstm to which you can order by.

I do not think active record will be your friend on this one, as a subquery is in line.




Theme © iAndrew 2016 - Forum software by © MyBB