Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

table is not in a *-to-one relationship...?

posted by allen 9 years ago

I'm trying out flourish, so far I love it, but I'm running into some difficulty with fRecordSet. My database is four tables in postgresql, full RI with primary and foreign keys, etc. The tables are:

There are other fields, but those are the ones related to the relations.

fActiveRecord based classes exist for each table and are named appropriately, e.g. 

  class city extends fActiveRecord
  {
    protected function configure()
    {
    }
  }

Given cities.name and state.short_name, I'm trying to find the locations that are in that city and state. The raw SQL would be: 

SELECT *
  FROM locations lo, zipcodes zi, cities ci, states st
 WHERE lo.zipcode_id = zi.zipcode_id
   AND zi.city_id = ci.city_id
   AND ci.state_id = st.state_id
   AND ci.name = 'Dover'
   AND st.short_name = 'NH'

This raw SQL works as expected.

I'm having a bit of trouble coming up with the most efficient way to retrieve this data using the fActiveRecord classes (which are defined for all the tables), and/or fRecordSet class. Rather than go over all the obviously wrong things I've tried, can someone suggest what they would do in order to get this result?

Just to cover the 'most promising' attempt I've made: 

$state = new State(array('short_name' => strtoupper($path[1])));
$city = new City(array('name' => $path[2], 'state_id' => $state->getStateId()));
$zips = $city->buildZipcodes();
$locations = $zips->buildLocations();

The exception is thrown on the last line, where it says that "the table zipcodes is not in a *-to-one relationship with the table locations". The error statement is true; zipcodes is in a 1-to-many relationship with locations

If I replace the last line with this block, it works, but seems "ugly" and I imagine there's a better/simpler way to do what I'm trying to do: 

$locations = array();
foreach ($zips as $zip)
{
  $locations[] = $zip->buildLocations();
}
posted by allen 9 years ago

You should be able to get the record set with the following code. Unfortunately it isn't currently possible to join three tables away using the fRecordSet syntax, so you'll either need to pre-select the state, or build from SQL. 

// If you short name is unique, you can do this
$state = new State(array('short_name' => 'NH'));

$set = fRecordSet::build(
    'Location',
    array(
        'zipcodes=>cities.name='     => 'Dover',
        'zipcodes=>cities.state_id=' => $state->getStateId()
    )
);

// Or you can do a build from SQL
$set = fRecordSet::buildFromSQL(
    'Location',
    fORMDatabase::retrieve()->escape(
        '
        SELECT lo.*
        FROM locations lo, zipcodes zi, cities ci, states st
        WHERE lo.zipcode_id = zi.zipcode_id
            AND zi.city_id = ci.city_id
            AND ci.state_id = st.state_id
            AND ci.name = %s
            AND st.short_name = %s
        ',
        'Dover',
        'NH'
    )
);

It seems to me that neither of these are optimal from a syntax perspective.

The thing that is stopping me from implementing the possibility of more tables distance between tables is just the syntax. Ideally there would be slightly different syntax so that I can reliably parse it. Perhaps an additional = such as ==> or an additional > such as =>>?

fRecordSet::buildFromSQL() could probably benefit from a method to escape values without having to explicitly retrieve an fDatabase issue.

Ideally both of these issues should be solved through enhancements.

posted by wbond 9 years ago

Thanks for the quick response!

I'm trying to wrap my head around this in a way that allows fRecordSet to be built to support 'n' distance, rather than 2 or 3 or 8..

In my specific case, I'm obviously doing something 'odd', but the schema and access method isn't all that uncommon in the general case.

posted by allen 9 years ago