• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple rows in result object when linking two tables via a third table using inner joins

#1
[eluser]Unknown[/eluser]
I have this:
Code:
/**
   * Get Event
   *
   * Gets a single event (and assoc' tags) based on the events ID and
   * returns a database result object.
   *
   * @param mixed $event_id id of the event to get or NULL for all events
   * @return mixed database result object on success, otherwise FALSE
   */
  public function get_event($event_id = NULL)
  {
   // attempt to get the event and tags
   $this->db->select('*')
      ->from('event')
      ->join('event_tag', 'event.event_id = event_tag.event_id', 'inner')
      ->join('tag', 'tag.tag_name = event_tag.tag_name', 'inner');
  
   // do we need to resetrict results to one event?
   if($event_id !== NULL)
   {
    $this->db->where('event.event_id', $event_id);
   }
  
   // get results
   $query = $this->db->get();
  
   // check for a result and return
   return ($query->num_rows() > 0) ? $query->result() : FALSE;
  }

Which gives me this:
Code:
Array
(
    [0] => stdClass Object
        (
            [event_id] => 3
            [event_name] => Awesome Meetup
            [event_description] => Phasellus molestie magna non est bibendum non venenatis nisl tempor. Suspendisse dictum feugiat nisl ut dapibus. Mauris iaculis porttitor posuere. Praesent id metus massa, ut blandit odio. Proin quis tortor orci. Etiam at risus et justo dignissim congue. Donec congue lacinia dui.
            [event_url] => http://www.awesomemeetup.com
            [start_date] => 1342051200
            [finish_date] => 1342137600
            [tag_name] => awsome
            [tag_description] =>
        )

    [1] => stdClass Object
        (
            [event_id] => 3
            [event_name] => Awesome Meetup
            [event_description] => Phasellus molestie magna non est bibendum non venenatis nisl tempor. Suspendisse dictum feugiat nisl ut dapibus. Mauris iaculis porttitor posuere. Praesent id metus massa, ut blandit odio. Proin quis tortor orci. Etiam at risus et justo dignissim congue. Donec congue lacinia dui.
            [event_url] => http://www.awesomemeetup.com
            [start_date] => 1342051200
            [finish_date] => 1342137600
            [tag_name] => example
            [tag_description] =>
        )

)

but what I would like is for the tags to be grouped so I only have one event returned with all its tags contained. However I am trying to avoid an extra sql query.

Is CI not able to work out that I only have one event there?!

N.B I have tried all the combinations of different joins I can think of.

#2
[eluser]Unknown[/eluser]
Oh, should have added this:
Code:
Array
(
    [0] => stdClass Object
        (
            [event_id] => 3
            [event_name] => Awesome Meetup
            [event_description] => Phasellus molestie magna non est bibendum non venenatis nisl tempor. Suspendisse dictum feugiat nisl ut dapibus. Mauris iaculis porttitor posuere. Praesent id metus massa, ut blandit odio. Proin quis tortor orci. Etiam at risus et justo dignissim congue. Donec congue lacinia dui.
            [event_url] => http://www.awesomemeetup.com
            [start_date] => 1342051200
            [finish_date] => 1342137600
            [tags] => Array
               (
                [0] => stdClass Object
                 (
                  [tag_name] => awsome
                  [tag_description] =>
                 )
    
                [1] => stdClass Object
                 (
                  [tag_name] => example
                  [tag_description] =>
                 )
  
               )
  
        )

)

This is the kind of thing I am hoping to achieve


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.