List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 4 2012 6:43pm
Subject:RE: Aggregate
View as plain text  
int(1) does not mean what you think.  Probably you want TINYINT UNSIGNED.
int(25), bigint(250) -- Eh?
INT is always 4 bytes (32-bits); BIGINT is always 8 bytes.

Use UNSIGNED wherever appropriate.

You have DATETIME and TIMESTAMP.  Keep in mind that TIMEZONE is timezone-aware; DATETIME is not.

The two tables have a lot of the same fields -- you may need to rethink the schema.
Both tables have the same PRIMARY KEY, and it is AUTO_INCREMENT.  This is almost certainly a mistake.

Wouldn't one "article" have multiple "discussions"?

Please restructure the tables before we try to discuss your Aggregate question.

> -----Original Message-----
> From: Ron Piggott [mailto:ron.piggott@stripped]
> Sent: Friday, August 31, 2012 3:22 AM
> To: mysql@stripped
> Subject: Aggregate
> 
> 
> I am unsure of which Aggregate function to use with the query (below)
> Right now thread_date_reference and last_post_membership_reference
> produce NULL results. I expect the NULL result is because of the LEFT
> OUTER JOIN and there being no matching JOIN in the table
> articles_discussion , as well as the GROUP BY (limiting 1 record result
> per thread)
> 
> 
> SELECT `articles`.`reference` , `articles`.`article_title` , IF(
> `articles`.`date_submitted` > `articles_discussion`.`date_submitted` ,
> CONVERT_TZ(`articles`.`date_submitted`,'+00:00','$user_time_zone_prefer
> ence_time_string') ,
> CONVERT_TZ(`articles_discussion`.`date_submitted`,'+00:00','$user_time_
> zone_preference_time_string') ) AS thread_date_reference , IF(
> `articles`.`date_submitted` > `articles_discussion`.`date_submitted` ,
> `articles`.`membership_reference` ,
> `articles_discussion`.`membership_reference` ) AS
> last_post_membership_reference FROM `articles` LEFT OUTER JOIN
> `articles_discussion` ON `articles`.`reference` =
> `articles_discussion`.`articles_reference` WHERE `articles`.`approved`
> IN ( 1 , 2 , 3 ) GROUP BY `articles`.`reference`
> 
> 
> The purpose of this query is to show a summary of the discussion
> threads with date of the most recent post ( thread_date_reference ) and
> the member # of that post ( last_post_membership_reference ).
> 
> I am wanting to make the threads index into 4 queries to allow for
> different ORDER BY options:
> 
> - newest to oldest posts (based on the date field)
> - oldest to newest posts (based on the date field)
> - thread titles alphabetical
> - by alias alphabetically (This will need an INNER JOIN to the “forum
> profiles” table. The tables articles and articles_discussion only store
> the auto_increment value of the member account # )
> 
> 
> The table structure for articles
> 
> CREATE TABLE IF NOT EXISTS `articles` (
> `reference` int(25) NOT NULL AUTO_INCREMENT, `membership_reference`
> int(25) NOT NULL, `languages_list_reference` int(10) NOT NULL,
> `article_title` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
> `article` longtext COLLATE utf8_unicode_ci NOT NULL, `article_summary`
> varchar(2500) COLLATE utf8_unicode_ci NOT NULL, `remember_new_posting`
> int(1) NOT NULL COMMENT '1- Yes & 0- No', `date_submitted` timestamp
> NOT NULL DEFAULT CURRENT_TIMESTAMP,
> `membership_reference_points_awarded` int(10) NOT NULL, `approved`
> int(1) NOT NULL,
> `inappropriate_content_flagged_by_membership_reference` int(25) NOT
> NULL, `inappropriate_content_flag_reasons_reference` int(2) NOT NULL,
> `inappropriate_content_flag_comments` varchar(2500) COLLATE
> utf8_unicode_ci NOT NULL,
> `inappropriate_content_flagged_date_submitted` datetime NOT NULL,
> `inappropriate_content_flag_moderator_membership_reference` int(25) NOT
> NULL, `inappropriate_content_flag_moderator_comments` longtext COLLATE
> utf8_unicode_ci NOT NULL,
> `inappropriate_content_flag_moderator_review_date` datetime NOT NULL,
> `html_head_keyword_1` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
> `html_head_keyword_2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
> `html_head_keyword_3` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
> `html_head_keyword_4` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
> `html_head_keyword_5` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
> `html_head_description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
> `views` bigint(250) NOT NULL, PRIMARY KEY (`reference`)
> 
> 
> The table structure for articles_discussion
> 
> CREATE TABLE IF NOT EXISTS `articles_discussion` ( `reference` int(25)
> NOT NULL AUTO_INCREMENT, `articles_reference` int(25) NOT NULL,
> `membership_reference` int(25) NOT NULL, ` languages_list_reference`
> int(10) NOT NULL, `discussion` longtext COLLATE utf8_unicode_ci NOT
> NULL, `remember_discussion_posting` int(1) NOT NULL COMMENT '1- Yes &
> 0- No', `date_submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
> `membership_reference_points_awarded` int(10) NOT NULL, `approved`
> int(1) NOT NULL COMMENT '1- auto approval; 2- flag for review; 3-
> review approval; 4- review deemed inappropriate and posting offline',
> `inappropriate_content_flagged_by_membership_reference` int(25) NOT
> NULL, `inappropriate_content_flag_reasons_reference` int(2) NOT NULL,
> `inappropriate_content_flagged_comments` varchar(2500) COLLATE
> utf8_unicode_ci NOT NULL,
> `inappropriate_content_flagged_date_submitted` datetime NOT NULL,
> `inappropriate_content_flag_moderator_membership_reference` int(25) NOT
> NULL, `inappropriate_content_flag_moderator_comments` longtext COLLATE
> utf8_unicode_ci NOT NULL,
> `inappropriate_content_flag_moderator_review_date` datetime NOT NULL,
> `views` bigint(250) NOT NULL, PRIMARY KEY (`reference`)
> 
> I appreciate any help you are able to give me.
> 
> Ron Piggott
> 
> 
> 
> www.TheVerseOfTheDay.info
Thread
AggregateRon Piggott31 Aug
  • RE: AggregateRick James4 Sep
    • RE: Aggregatehsv5 Sep
    • RE: Aggregatehsv23 Sep