Welcome Guest, Not a member yet? Register   Sign In
join query
#1

[eluser]A.M.F[/eluser]
hello people

i'm having a problam. i have to tabels in my database: one for articles, and another for article's comments.

in the comments table one of my fields is "article_id" which containes the article's id of the comment.
i want to show the whole articles in one page and next to each article the amount of comments that the articles has.
for example:
"5 tips for games - number of comments: 3"

this is what i tried to:
Code:
$this->db->from('games, comments');
$this->db->select('games.*, comments.*, COUNT(comments.cmnt_itemid) as votes');
$this->db->orderby('ga_id', 'desc');        
$this->db->limit($config['per_page'], $start_at);
$data['games'] = $this->db->get();

but this is the error that i get:
Quote:Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT games.*, comments.*, COUNT(comments.cmnt_itemid) as votes FROM games, comments ORDER BY ga_id desc LIMIT 12

what can i do?
thank u
#2

[eluser]Negligence[/eluser]
Change your ORDER BY to GROUP BY and it should be fine.
#3

[eluser]A.M.F[/eluser]
thx it works now, almost perfect.
the problam is: it shows the total number of all comments and not only each article's comments number so i tried to do something like this:


Code:
$this->db->select('games.*, comments.*, COUNT(comments.cmnt_itemid=games.ga_id) as votes');

but it still deosn't work.

PS
can u tell me why when we added the GROUP BY it worked?

thx
#4

[eluser]Sean Murphy[/eluser]
[quote author="A.M.F" date="1203443243"]
Code:
$this->db->from('games, comments');
$this->db->select('games.*, comments.*, COUNT(comments.cmnt_itemid) as votes');
$this->db->orderby('ga_id', 'desc');        
$this->db->limit($config['per_page'], $start_at);
$data['games'] = $this->db->get();
[/quote]
Change the line with the orderby() to:
Code:
$this->db->group_by('comments.article_id', 'desc');
#5

[eluser]A.M.F[/eluser]
ok thank ut both guys now it works but there is one problam:
if there are no comments to the article, than it doesn't appear on the list.

this is my full code:
Code:
$this->db->from('games');
$this->db->select('games.*, ranking.*, comments.cmnt_itemid, comments.cmnt_item, COUNT(comments.cmnt_itemid) as total');
$this->db->where('comments.cmnt_item', 'game');
$this->db->join('comments', 'comments.cmnt_itemid = games.ga_id');
$this->db->join('ranking', 'ranking.rank_itemid = games.ga_id');    
//$this->db->groupby('ga_id');
$this->db->groupby('comments.cmnt_itemid', 'desc');
$this->db->orderby('ga_id', 'desc');
$this->db->limit($config['per_page'], $start_at);
$data['games'] = $this->db->get();

and this is the query:
Quote:SELECT games.*, ranking.*, comments.cmnt_itemid, comments.cmnt_item, COUNT(comments.cmnt_itemid) as total FROM games JOIN comments ON comments.cmnt_itemid = games.ga_id JOIN ranking ON ranking.rank_itemid = games.ga_id WHERE comments.cmnt_item = 'game' GROUP BY comments.cmnt_itemid ORDER BY ga_id desc LIMIT 12

what can i do?
#6

[eluser]Sean Murphy[/eluser]
You're only getting results that have comments because of your WHERE clause. If an article doesn't have comments comments.cmnt_item will be NULL, therefore not meeting the WHERE condition.
#7

[eluser]A.M.F[/eluser]
[quote author="Sean Murphy" date="1203539157"]You're only getting results that have comments because of your WHERE clause. If an article doesn't have comments comments.cmnt_item will be NULL, therefore not meeting the WHERE condition.[/quote]

i think it beacuse of my "join" clause. when i deleted my WHERE clause it didn't change anything, though my JOIN clause joins the comments.cmnt_itemid with my games' id.

if there are no comments.cmnt_itemid that equals to a game id than it deosn't appear.

is there a way to go by that and fix it?
#8

[eluser]Digitales[/eluser]
I believe that you will need to use a left join when querying the comments table, since it will still return a null value rather than no values at all (when an inner join is used).

eg.
Code:
$this->db->join('comments', 'comments.cmnt_itemid = games.ga_id', 'left');

resulting in a query such as:

Code:
SELECT games.*, ranking.*, comments.cmnt_itemid, comments.cmnt_item, COUNT(comments.cmnt_itemid) as total
FROM games LEFT JOIN comments ON comments.cmnt_itemid = games.ga_id
JOIN ranking ON ranking.rank_itemid = games.ga_id
WHERE comments.cmnt_item = ‘game’
GROUP BY comments.cmnt_itemid
ORDER BY ga_id desc LIMIT 12

You may be better to move the where clause checks to the on join part of the query, e.g.

Code:
SELECT games.*, ranking.*, comments.cmnt_itemid, comments.cmnt_item, COUNT(comments.cmnt_itemid) as total
FROM games
LEFT JOIN comments ON comments.cmnt_itemid = games.ga_id AND comments.cmnt_item = ‘game’
JOIN ranking ON ranking.rank_itemid = games.ga_id
GROUP BY comments.cmnt_itemid
ORDER BY ga_id desc LIMIT 12

You may find it easier to tweak the DB query in something like phpMyAdmin, and then adding it to you codeigniter app.

Hope this helps.
#9

[eluser]A.M.F[/eluser]
thanku Digitales it worked!
can u explain what the "left" in the JOIN clause does and why i needed to move the WHERE clause to the join part?
and is there also a "right" JOIN?

thank u!
#10

[eluser]Digitales[/eluser]
I believe that inner and cross joins are more strongly linked, so if one of the conditions fail, nothing is returned for the join.

On the other hand the left join is more loosely linked, and will simply return a NULL value if one of the conditions fail. So in your case the 'left join' will allow for all the articles to be returned even if they do not have comments associated with them.

As Sean Murphy stated the 'where' condition was causing the query to return no results when an article didn't have comments associated with it.


There is a right join, but I rarely use it.


The various database all have varying variations of joins and more information MySQL joins can be seen at:

http://dev.mysql.com/doc/refman/5.0/en/join.html


Sorry that I can't be of more help with the technical details of joining methods, I only seem to use fairly simple queries at the moment in MySQL.

Regards,

Digitales




Theme © iAndrew 2016 - Forum software by © MyBB