[eluser]spyro[/eluser]
[quote author="drewbee" date="1235793126"]Nope. But I don't Understand your problem  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.[/quote]
Not a problem. So if you look at where the query started on page one I was doing a join on accounts and accounts_cstm. This gave me the correct number of results. ( Yes, this is 1 to 1 )
What we added was a Left join and count statement to get the total number of reviews related to that account. Now I only get 1 result back.
[eluser]drewbee[/eluser]
Try this on for size (probably some syntax errors, its been a while since I have done a subquery in mysql
Code: $query = $this->db->query("
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,
(SELECT
COUNT(mrc.id_c)
FROM
mech_reviews_cstm mrc
WHERE
mrc.userid_c = a.id AND
mrc.inactive_c = 1) mech_reviews_total
FROM
accounts a
JOIN
accounts_cstm ac ON ac.id_c = a.id
WHERE
a.billing_address_state = ? AND
a.billing_address_city = ?
ORDER BY
mech_reviews_total desc, ac.premiummember_c desc, ac.mechanixlooprating_c desc
LIMIT 6",
array($address_state, $address_city));
Edit: Crap, I forgot to take out the second join which is now handled by the subquery.
[eluser]spyro[/eluser]
That worked. What is weird is that this query is very close to what I was trying at the beginning that locked up MySQL. But I did add an index on userid_c as dmorin suggested.
Thanks for all the help. This was an educational thread.
[eluser]drewbee[/eluser]
It's no problem!
If you post up the query that locked up the server, I should be able to tell you what was wrong with it (if you still have it).
Always glad to help!
Also, a JOIN = LEFT JOIN. Alias
[eluser]dmorin[/eluser]
Glad to see you got it working, but I would be VERY careful with the one that did work for you. Doing a query in the select means you're doing individual queries for each record returned. While it might work and be sufficient with a minimal number of rows, it almost certainly won't scale to larger data sets.
If it's good for now, then good, but definitely keep that in the back of your mind if/when you run into performance issues.
[eluser]spyro[/eluser]
[quote author="dmorin" date="1235805055"]Glad to see you got it working, but I would be VERY careful with the one that did work for you. Doing a query in the select means you're doing individual queries for each record returned. While it might work and be sufficient with a minimal number of rows, it almost certainly won't scale to larger data sets.
If it's good for now, then good, but definitely keep that in the back of your mind if/when you run into performance issues.[/quote]
If that is the case then I would also like to see it work with the join instead of the subquery. That way I can run some queries and see the performance difference. I just couldn't get the join to give me the correct number of rows and wasn't sure why.
[eluser]dmorin[/eluser]
When you run the version with the join, and you only get one row, is that row correct? ie it is a valid record and has the correct number of reviews? Or is the count of reviews exaggerated?
It shouldn't be necessary, but also try adding $this->db->group_by('a.id'); to the query.
[eluser]spyro[/eluser]
[quote author="dmorin" date="1235807534"]When you run the version with the join, and you only get one row, is that row correct? ie it is a valid record and has the correct number of reviews? Or is the count of reviews exaggerated?
It shouldn't be necessary, but also try adding $this->db->group_by('a.id'); to the query.[/quote]
You were right on both accounts. The number of reviews were exaggerated and adding the group by fixed it.
[eluser]dmorin[/eluser]
Great to hear.
[eluser]drewbee[/eluser]
To throw one last thing in here too, their is also the option of database denormalization; especially if scaling becomes a problem; in your case it would simply involve keeping a total colum in the accounts_cstm table of all of your reviews, and this number is simply updated with each review addition, deletion etc.
|
