SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    assistance requested with a query

    I am finding this to be quite a complicated query.

    I am trying to get the data for each event, from the several tables that it is stored in.

    there may or may not be an image, so I made a LEFT OUTER join.

    I'll post the query and then the create table statements and would appreciate your help.

    Also, should foreign keys go from grandchild to child or straight to the parents? eg, event_images linking to 'events_data' OR should it link directly, to 'events'

    bazz
    Code MySQL:
    select
                base_events.event_id
              , base_events.event_name_in_home_language
              , base_events.live_from
              , base_events.live_to      
              , event_data.file_data as file_data
              , pd.pricing_data as pricing_data
              , c.symbol_left
    	  , c.symbol_right
    	  , ei.image
           FROM events as base_events
     
       INNER     
           JOIN ( SELECT id
                       , event_id
    	           , GROUP_CONCAT(
                         CONCAT_WS( ','
    	                      , ed.file_heading
                                  , ed.text_block_sequence
                                  , ed.text_or_list
    			      , ed.file_text
    			      , ei.image
     
                                  ) order by ed.text_block_sequence 
                            SEPARATOR ';' ) AS file_data
                    FROM events_data eda
    	left outer
    		join event_images as imgs
    		on imgs.event_id = eda.event_id
    		) as ed
             ON ed.event_id = base_events.event_id
     
     
       INNER
           JOIN ( select eh.event_id
                       , GROUP_CONCAT(
    		     CONCAT_WS( ','
    		              , eh.day_of_week
    			      , eh.opening
    			      , eh.closing
    			      , ep.price
    			      , ep.text_price
    			      , ep.age_group
     
    		              )
    		        SEPARATOR ';' ) as pricing_data
    		FROM events_hours as eh
    	      INNER
    	        JOIN events_prices as ep
    		  on ep.event_id = eh.event_id
    		 and ep.hours_id = eh.hours_id
    		 GROUP
    		   BY ep.event_id
                ) as pd
             ON pd.event_id = base_events.event_id	    
       INNER
           JOIN business_currencies as bc
             ON bc.business_id = base_events.business_id
       INNER
           JOIN currency as c
             on c.currency = bc.currency
     
       #where base_events.business_id = 123

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `events` (
      `event_id` int(11) NOT NULL auto_increment,
      `business_id` int(11) NOT NULL,
      `content_category` varchar(99) collate utf8_unicode_ci NOT NULL,
      `live_from` date NOT NULL,
      `live_to` date NOT NULL,
      `event_name_in_home_language` varchar(32) collate utf8_unicode_ci NOT NULL,
      `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `last_updated_by` varchar(99) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`event_id`),
      UNIQUE KEY `business_file_name_fk` (`business_id`,`event_name_in_home_language`),
      KEY `fnhl_ix` (`event_name_in_home_language`),
      KEY `business_id` (`business_id`),
      KEY `files_categories_fk` (`business_id`,`content_category`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;


    Code MySQL:
    CREATE TABLE IF NOT EXISTS `events_data` (
      `id` bigint(11) NOT NULL auto_increment,
      `event_id` int(11) NOT NULL,
      `language_abbr_this_file` varchar(3) collate utf8_unicode_ci NOT NULL default 'ENG',
      `displayed_event_name` varchar(99) collate utf8_unicode_ci default NULL,
      `text_block_sequence` tinyint(2) unsigned zerofill NOT NULL,
      `text_or_list` varchar(4) collate utf8_unicode_ci NOT NULL default 'text',
      `file_heading` varchar(255) collate utf8_unicode_ci default NULL,
      `file_text` text collate utf8_unicode_ci NOT NULL,
      `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `last_updated_by` varchar(32) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `id` (`event_id`,`text_block_sequence`),
      KEY `lang_ix` (`language_abbr_this_file`),
      KEY `file_ix` (`event_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=122 ;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table `events_data`
    --
    ALTER TABLE `events_data`
      ADD CONSTRAINT `event_data_event_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`)

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `events_hours` (
      `hours_id` bigint(20) NOT NULL auto_increment,
      `event_id` int(11) NOT NULL,
      `day_of_week` varchar(99) collate utf8_unicode_ci default NULL,
      `opening` time default '00:00:00',
      `closing` time default '00:00:00',
      PRIMARY KEY  (`hours_id`),
      UNIQUE KEY `event_id` (`event_id`,`day_of_week`,`opening`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=162 ;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table `events_hours`
    --
    ALTER TABLE `events_hours`
      ADD CONSTRAINT `event_hours_events_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ;

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `events_prices` (
      `event_id` int(20) NOT NULL,
      `hours_id` bigint(11) NOT NULL,
      `price` varchar(64) collate utf8_unicode_ci default NULL,
      `text_price` varchar(99) collate utf8_unicode_ci default NULL,
      `age_group` varchar(99) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`event_id`,`hours_id`,`age_group`),
      KEY `event_prices_hours_fk` (`hours_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table `events_prices`
    --
    ALTER TABLE `events_prices`
      ADD CONSTRAINT `event_prices_hours_fk` FOREIGN KEY (`hours_id`) REFERENCES `events_hours` (`hours_id`);

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `event_images` (
      `event_id` int(11) NOT NULL,
      `file_text_id` bigint(20) NOT NULL,
      `image` varchar(99) collate utf8_unicode_ci NOT NULL,
      PRIMARY KEY  (`file_text_id`,`image`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table `event_images`
    --
    ALTER TABLE `event_images`
      ADD CONSTRAINT `image_file_data_fk` FOREIGN KEY (`file_text_id`) REFERENCES `events_data` (`id`);

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    to begin with, try adding a GROUP BY clause to your subqueries which use GROUP_CONCAT

    GROUP_CONCAT is an aggregate function and therefore requires a GROUP BY clause if there are non-aggregate expressions in the SELECT clause

    if that doesn't fix your problem, which you neglected to describe, then perhaps you could explain what the query isn't doing correctly that it should be doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy.

    The query returns all possible results, correctly, all on the one page.

    I want to return 5, per page.

    when I apply limit 0, 5, the number of results returned is 3 in total instead of 5.

    I have tried grouping on various columns but, I need help to know which group by's are wrong or what else, I should be looking at.
    here's the query, with the limit clause hashed out and it returns all 7 results. if I use limit, it only returns 3 results.

    Code MySQL:
    select
            base_events.event_id
          , base_events.event_name_in_home_language
          , base_events.live_from 
          , base_events.live_to
          , ed.event_data as events_data
          , hd.hours_data as hours_data
          , pd.pricing_data as pricing_data
          , base_currency.symbol_left
          , base_currency.symbol_right
     
         FROM businesses AS base
     INNER
         JOIN events AS base_events
           ON base_events.business_id = base.id
     
     
     # get the text blocks and images
     left outer
         JOIN ( select ed.event_id
                     , ed.id
                  , GROUP_CONCAT(
                    CONCAT_WS(','
                     , ed.id
                         , ed.text_block_sequence
                 , ed.displayed_event_name
                 , ed.text_or_list
                 , ed.file_text
                 , ei.image
                            ) order by ed.file_heading, ed.text_block_sequence asc
                     SEPARATOR ';' ) AS event_data
                  from events_data AS ed
           left outer
              join event_images AS ei
              on ei.event_id = ed.event_id
             AND ei.file_text_id = ed.id
             group by ed.event_id
        ) as ed
        on ed.event_id = base_events.event_id
     
     
     # get the hours
     left outer
         JOIN ( select eh.event_id
                     , eh.hours_id
                  , GROUP_CONCAT(
                    CONCAT_WS(','
                         , eh.day_of_week
                 , TIME_FORMAT(eh.opening, '%l:%i %p') 
                 , TIME_FORMAT(eh.closing, '%l:%i %p') 
                            ) order by eh.day_of_week asc
                     SEPARATOR ';' ) AS hours_data
                  from events_hours AS eh
              group by eh.hours_id 
          ) as hd
        on hd.event_id = base_events.event_id
     
       #get the pricing
       left outer
         JOIN ( select ep.event_id
                     , ep.hours_id
                  , GROUP_CONCAT(
                    CONCAT_WS(','
                         , ep.age_group
                 , ep.price
                 , ep.text_price 
     
                            ) order by ep.age_group asc
                     SEPARATOR ';' ) AS pricing_data
                  from events_prices AS ep
              group by ep.event_id
          ) as pd
        on pd.event_id = base_events.event_id
       AND pd.hours_id = hd.hours_id
     
       #get the currency
     INNER       
         JOIN business_currencies AS base_bc
           ON base_bc.business_id = base.id
     INNER
         JOIN currency AS base_currency
           on base_currency.currency = base_bc.currency     
        WHERE base.id = 123
          #group by events_data
              ORDER
            BY live_from desc
             # limit 0,5

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it

    well I think I have.

    I added a group by to the main query and instead of couting the number of event_ids returned, I reverted to SQL_CALC_FOUND_ROWS

    Still to test it further.

    Code MySQL:
    select SQL_CALC_FOUND_ROWS 
            base_events.event_id
          , base_events.event_name_in_home_language
          , base_events.live_from 
          , base_events.live_to
          , ed.event_data as events_data
          , hd.hours_data as hours_data
          , pd.pricing_data as pricing_data
          , base_currency.symbol_left
          , base_currency.symbol_right
     
         FROM businesses AS base
     INNER
         JOIN events AS base_events
           ON base_events.business_id = base.id
     
     
     # get the text blocks and images
     left outer
         JOIN ( select ed.event_id
                     , ed.id
    		# , ed.displayed_event_name
                  , GROUP_CONCAT(
                    CONCAT_WS(','
    		         , ed.id
    	                 , ed.text_block_sequence
    			 , ed.displayed_event_name
    			 , ed.text_or_list
    			 , ed.file_text
    			 , ei.image
                            ) order by ed.file_heading, ed.text_block_sequence asc
                     SEPARATOR ';' ) AS event_data
                  from events_data AS ed
    	   left outer
    	      join event_images AS ei
    	      on ei.event_id = ed.event_id
    	     AND ei.file_text_id = ed.id
    	     group by ed.id
        ) as ed
        on ed.event_id = base_events.event_id
     
     
     
     # get the hours
     left outer
         JOIN ( select eh.event_id
                     , eh.hours_id
                  , GROUP_CONCAT(
                    CONCAT_WS(','
    	                 , eh.day_of_week
    			 , TIME_FORMAT(eh.opening, '%l:%i %p') 
    			 , TIME_FORMAT(eh.closing, '%l:%i %p') 
                            ) order by eh.day_of_week asc
                     SEPARATOR ';' ) AS hours_data
                  from events_hours AS eh
    	      group by eh.hours_id 
    	  ) as hd
        on hd.event_id = base_events.event_id
     
       #get the pricing
       left outer
         JOIN ( select ep.event_id
                     , ep.hours_id
                  , GROUP_CONCAT(
                    CONCAT_WS(','
    	                 , ep.age_group
    			 , ep.price
    			 , ep.text_price 
     
                            ) order by ep.age_group asc
                     SEPARATOR ';' ) AS pricing_data
                  from events_prices AS ep
    	      group by ep.event_id
    	  ) as pd
        on pd.event_id = base_events.event_id
       AND pd.hours_id = hd.hours_id
     
       #get the currency
     INNER	   
         JOIN business_currencies AS base_bc
           ON base_bc.business_id = base.id
     INNER
         JOIN currency AS base_currency
           on base_currency.currency = base_bc.currency	 
        WHERE base.id = 123
          group by base_events.event_id
              ORDER
            BY live_from desc
              limit 0,5


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •