Welcome Guest, Not a member yet? Register   Sign In
Complex SQL Query with Active Record
#1

[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.


Messages In This Thread
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 04:48 AM
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 05:27 AM
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 05:27 AM
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 06:46 AM
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 09:19 AM
Complex SQL Query with Active Record - by El Forum - 12-18-2009, 09:48 AM
Complex SQL Query with Active Record - by El Forum - 01-01-2010, 09:17 AM



Theme © iAndrew 2016 - Forum software by © MyBB