Complex SQL Query with Active Record

[eluser]Burak Erdem[/eluser]
Hi,

I'm trying to write a complex sql query with CI Active Record, but I couldn't manage to write it. I have 3 tables; categories, posts and comments.

Code:

CREATE TABLE `categories` (
  `id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(30) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)ENGINE=MyISAM
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `posts` (
  `id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
  `title` VARCHAR(150) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `content` TINYTEXT NOT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)ENGINE=MyISAM
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `comments` (
  `id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
  `member_id` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
  `comment` TEXT COLLATE utf8_general_ci NOT NULL,
  `ip` INTEGER(11) NOT NULL DEFAULT '0',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

And I'm trying get something like that;

Code:

category.id | category.title | post.id | post.title | post.content | comment_count
----------------------------------------------------------------------------------
1             Category 1       1         Post 1       Post content   5
1             Category 1       2         Post 2       Post content   3
2             Category 2       3         Post 3       Post content   0
3             Category 3       4         Post 4       Post content   1

It's important for me to get "posts.status = 1 AND comments.status = 1". If there are no comments or comments were not approved (which is comments.status = 0), "comment_count" row will be shown as 0 (zero).

Is there a way to write this query with Active Record?

Thanks.

[eluser]Aljebrini[/eluser]
i think you can user active record to do your query by writing full query then pass it to the db->get() function ...

$query = "SELECT category.id ,category.title , post.id , post.title , post.content ,COUNT(comments.id) as comment_count FROM categories as category , posts as post,comments Where post.status = 1 AND comments.status = 1 and post.category_id = category.id ";

$this->db->get($query) ;

i didn't test this query on your real database tables but it will work, try to see what you get from ..

[eluser]jedd[/eluser]
[quote author="demods" date="1261154939"]
I'm trying to write a complex sql query with CI Active Record, but I couldn't manage to write it.
[/quote]

Show the SQL you're trying to emulate.

Show the AR snippet that you've got so far.

Describe how the results of what you've got so far diverge from your intent.

[eluser]Burak Erdem[/eluser]
[quote author="jedd" date="1261157235"][quote author="demods" date="1261154939"]
I'm trying to write a complex sql query with CI Active Record, but I couldn't manage to write it.
[/quote]

Show the SQL you're trying to emulate.

Show the AR snippet that you've got so far.

Describe how the results of what you've got so far diverge from your intent.[/quote]

The problem is that I also couldn't manage to write the SQL query itself. I know that's not related to CI but if I can write the query, maybe I can also emulate it with AR.

Thanks.

[eluser]jedd[/eluser]
[quote author="demods" date="1261162019"]
The problem is that I also couldn't manage to write the SQL query itself.
[/quote]

Two things.

Show us what you've got so far - it's MUCH easier to work forward from some starting point rather than a blank slate. You'll learn more, as we can point out where you're going wrong or what you're getting stuck on. You'll also find people more willing to help you if it doesn't look like you're asking them to do all your homework for you.

Make it clear up front what question you're asking - it's actually not an AR problem at all, but a plain vanilla SQL one. This is fine - it's not really appropriate to these forums - but trying to disguise it just wastes everyone's time.

Code:

SELECT
  category.id AS category_id
  category.title AS category_title
  post.id AS post_id
  post.title AS post_title
  post.content AS post_content
  COUNT(comment.id) AS comment_count
FROM
  categories
LEFT JOIN
   posts ON posts.category_id = categories.id
LEFT JOIN
   comments ON comments.post_id = posts.id
WHERE
   posts.status = 1
AND
   comments.status = 1

Btw, mixing plurals with singulars is really confusing. You should stick with one or the other (and I think you should stick with singular).

Consider this fairly uncomfortable-to-read line:
posts ON posts.category_id = categories.id

Actually, I see I've mixed up some plurals and singulars above because of this. I'll leave that to you to sort out.

Untested code and all that. Don't have any test data to experiment with, so let us know how you go. If you have an actual CI related problem when converting this to AR, let us know.

[eluser]Burak Erdem[/eluser]
jedd,

You are definitely right. I'm sorry that I didn't ask the right question at right place. But you helped me so much and opened my mind. I will test your code and see if i can convert this to AR.

Thank you so much. That was a great learning session for me.

[eluser]Aljebrini[/eluser]
jedd, great job ..

isn't putting this " post.category_id = category.id " in where clause, gives the same results as join does ?

and please if you can give us samples for JOIN so we can understand how it works that's will be great,
thank you