This is an archived copy of the forum for reference purposes

Getting related data for query

posted by hcker2000 7 years ago

I have the following code and I'm trying to figure out how to get calendar.name to be in the fRecordSet so I can display it.

Any help would be great.

  static function featured($date = null) {
    $date = ($date ? $date : new fDate());
    
    $candidates = fRecordSet::buildFromSQL(
      'CalendarEvent',
      array('SELECT "calendar_events".*
               FROM "calendar_events"
               JOIN "calendars" ON "calendars"."calendar_id" = "calendar_events"."calendar_id"
              WHERE "calendars"."publication_id" = %i
                AND "calendar_events"."date" = %d
                AND "calendar_events"."featured" = %b', Publication::id(), $date, true)
    );
    
    
    if($candidates->count() > 0) {
	return $candidates;
    } 
    
    return null;
  }

An fRecordSet is a collection of fActiveRecord objects and an fActiveRecord object represents all columns from a single row of a single table. Thus, you can't.

To get data from multiple tables in a single database query you need to use fDatabase::query(). If you don't mind possibly doing multiple queries, you can call $calendar_event->createCalendar()->getName(). If the calendar has been loaded into memory before, it will not invoke an extra DB query.

posted by wbond 7 years ago

Ok I have tried to switch this over to use query. The following code produces the following error.

  static function featured($date = null) {
    $date = ($date ? $date : new fDate());
    
    $candidates = fDatabase::query('SELECT "calendar_events".*
               FROM "calendar_events"
               JOIN "calendars" ON "calendars"."calendar_id" = "calendar_events"."calendar_id"
              WHERE "calendars"."publication_id" = %i
                AND "calendar_events"."date" = %d
                AND "calendar_events"."featured" = %b', Publication::id(), $date, true);
    
/*    $candidates = fRecordSet::buildFromSQL(
      'CalendarEvent',
      array('SELECT "calendar_events".*
               FROM "calendar_events"
               JOIN "calendars" ON "calendars"."calendar_id" = "calendar_events"."calendar_id"
              WHERE "calendars"."publication_id" = %i
                AND "calendar_events"."date" = %d
                AND "calendar_events"."featured" = %b', Publication::id(), $date, true)
    );
  */  
    
    if($candidates->count() > 0) {
	return $candidates->asObjects();
    } 
    
    return null;
  }

#!text/html
Fatal error: Using $this when not in object context in /Users/uwmanager/Sites/testsite.com/protected/lib/flourish/fDatabase.php on line 2710

posted by hcker2000 7 years ago

The query method of fDatabase is not static, the :: is just used by this wiki for auto-linking. See the fDatabase page for examples. You'll also need to use fORMDatabase to retrieve the database instance used by the ORM.

posted by wbond 7 years ago

I gave it a try as fDatabase->query and get the following error.

#!text/html
Parse error: syntax error, unexpected T_OBJECT_OPERATOR in /Users/uwmanager/Sites/testsite.com/protected/lib/classes/Calendar.php on line 33

I am using 1.0.0b25. I attempted to update to the latest release but got lots of errors so my existing application would need a major overhaul I fear to use the newest version. Hoping I can stick with 1.0.0b25

posted by hcker2000 7 years ago

You need to have an instance of fDatabase. Look at the examples on the fDatabase page.

posted by wbond 7 years ago

Thanks we are on the same page now.

posted by hcker2000 7 years ago

If I use the ->createCalendar()->getName() how would i then access that info just so I know both ways to do things.

posted by hcker2000 7 years ago