SitePoint Sponsor

User Tag List

Results 1 to 25 of 25

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I instantiate objects based on a query across many database tables

    Hi everyone

    I'm experimenting with OO by reworking an old procedural coded system. The system is based on users (participants) making preferences for sites they would like to be placed for study.

    I have no problem setting up DAO's for accessing single tables/records, but I am struggling with how to setup code to deal with database queries across multiple tables and records.

    I have described the scenario below if you want to refer to it, but basically I need to query multiple records across multiple tables, but don't know how to go about setting up the myriad of objects to represent this.

    Thanks in advance!
    Leigh

    Scenario:
    I have a screen for an administrator to view a participant's preferences. The preferences displayed are also based on a 'form section' filter.

    Eg a preference form could have 2 sections: Section A and Section B ... if Section A is selected, only display the preferences made for this section

    I have attached an image below of 5 tables required to get this information.

    site: a building or facility
    unit: a department within a site where a participant gets placed.
    pref_form_section: stores the possible sections that can make up the preference form
    preference_option: stores the preference options (related by unit_id) and the section they appear under
    preference: stores the preferences that a participant has made.

    To be able to list all of the selected participant's preferences, the query would be:

    SELECT *
    FROM preference
    INNER JOIN preference_option ON preference.preference_option_id = preference_option.preference_option_id
    INNER JOIN unit ON preference_option.unit_id = unit.unit_id
    INNER JOIN site ON unit.site_id = site.site_id

    WHERE preference.participant = #form.participant_id#
    AND preference_option.pref_form_section_id = #form.pref_form_section_id#

    ORDER BY preference.preference_number
    Attached Images Attached Images

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    DAO isn't very flexible. Big problem unless you want to create a endless amount of classes and methods for each associations/join scenario. The easiest thing to do is place the method in the most appropriate already existing class and overload the data objects. However, if the objects can't be overloaded then separate classes need to be written.

    You will also soon run into the problem of collecting the result set into a meaningful hierarchy. For example, if preferences has many preference_options then when you join those two table there will be repeating data from preferences. However, what you would expect is more or less a hierarchy.

  3. #3
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply ...

    Based on your idea, I could end up with a collection of preference objects, hierachically linked to the others as follows ...

    preference
    -- preference_option
    ---- unit
    ----- site

    Would it be feasible instead to have a 'flat' structure mapped from the query instead? eg
    $results[0]['preference']
    $results[0]['preference_option']
    $results[0]['unit']
    $results[0]['site']
    $results[1]['preference']
    $results[1]['preference_option']
    etc...

    I could create the query in the 'preferenceDAO' object (as this is the closest related) and within it, for each section of a returned row, call the appropriate DAO mapping method to get it back as an object

    The only thing I'm a little lost about is
    'place the method in the most appropriate already existing class and overload the data objects. However, if the objects can't be overloaded then separate classes need to be written.'

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    $results[0]['preference']
    $results[0]['preference_option']
    $results[0]['unit']
    $results[0]['site']
    $results[1]['preference']
    $results[1]['preference_option']

    The problem that begins with joins is that the rows may no longer represent unique data. For example, the preference at index 0 may be the same as the preference at index 1 and so on. However, what you would expect is that is that each preference is different.

    $preference[0]['pref_pk'] // 1
    $preference[1]['pref_pk'] // 1
    $preference[2]['pref_pk'] // 1
    $preference[3]['pref_pk'] // 2
    $preference[4]['pref_pk'] // 2
    $preference[5]['pref_pk'] // 3
    $preference[6]['pref_pk'] // 3

    When really you would expect that each root item represents a separate entity.

    $preference[0]['pref_pk'] // 1
    $preference[1]['pref_pk'] // 2
    $preference[2]['pref_pk'] // 3

    Quote Originally Posted by Hgiel
    'place the method in the most appropriate already existing class and overload the data objects.
    Assuming a Preference DAO class exists stick the method inside that class even though it includes other tables through joins.

    Quote Originally Posted by Hgiel
    However, if the objects can't be overloaded then separate classes need to be written.'
    I'm not entirely certain how your data containers are set up but if they are based on hard coded properties then the associated data con not be added. Otherwise, if the properties are dynamic you merely overload the data.

    PHP Code:
    $pref = new Preference();
    $pref->preference_options[] = new PreferenceOption();
    $pref->preference_options[] = new PreferenceOption();
    $pref->preference_options[] = new PreferenceOption(); 
    Given your previous I tree I would create a structure similar to the below:

    PHP Code:
    $preferences[1]->preference_options[0]->units[9]->sites[0]->name 

  5. #5
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a screen for an administrator to view a participant's preferences.
    It looks like simple "fetch records and display them" scenario. Why do you need intermediate objects? What extra processing should they provide?

  6. #6
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mastodont View Post
    I have a screen for an administrator to view a participant's preferences.
    It looks like simple "fetch records and display them" scenario. Why do you need intermediate objects? What extra processing should they provide?
    Yes that is exactly the scenario. The extra processing that could for be provided is a method for output formatting.

    Eg for the preference object, outputPrefNumber() would return the string 'Preference 1', so that I can consistantly call this throughout the app ... unless there is a different place I should store such a method?

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    correct?

    site
    ----------------------------
    has many units


    unit
    --------------------------
    belongs to site
    has many preference_options


    preference_option
    ---------------------------
    belongs to unit
    belongs to preference_section
    has many preferences


    preference_section
    --------------------------------
    has many preference_options


    preference
    --------------------------------
    belongs to preference_option
    belongs to user

    user
    --------------------------------
    has many preferences

  8. #8
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Odds,

    Yes that is correct. There are business rules (not concerned about those in this discussion) which prevent user/participant from selecting duplicate units as preferences under each section or for example.

    Leigh

  9. #9
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is a compositional relationship between

    site -> unit
    and
    preference_section -> preference_option

    I think the relationship between preference_option to unit is an aggregate, however is it actually an aggregate to a combined site->unit ?
    I'm not sure how I would term the relationship between preference and preference_option

    It's issues like this I am confused about - how to map to objects from the database. Should I really just be dealing with any queries across tables as a result set (and lose the ability to run any related object methods) and just use DAOs for single table/record accesses??

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    This is exactly why I avoid using a DAO. You'll drive yourself mad trying to figure this out. I much rather use a generic mapper. Using a generic mapper will eliminate repetition and can even have mapper collect the result set into a hierarchy for you. Trying to that with a DAO ends up in a mess and/or a bunch of repetition in code. Not to kill your hopes or anything.

  11. #11
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't worry you're not killing my hopes!

    Part of the problem (and I think this is for alot of people), is that the introduction to OO is using a DAO setup (active record??). I don't know of any applications that deal solely with single records from single tables - yes it happens in parts of an app, but by no means a large majority. I feel like I'm missing the point somewhere, hence my questions.

    Is there examples of setting up a mapper out there? Everything seems to be DAO, map a table to an object.

    basically what I want to output is:

    Preference 1 ... Site ABC, Unit XYZ
    Preference 2 ... Site ABC, Unit DEF
    Preference 3 ... Site CBA, Unit GHI
    etc

    so to get that information, I would use the query I put in the first post.
    The hierarchy you suggested constructing earlier

    $preferences[1]->preference_options[0]->units[9]->sites[0]->name

    is representative of the table relationships. Is this what I should be aiming for or should the link between preference_option, unit, site be along the lines of

    preference_option -> site->unit

    where site/unit is a composed object?

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Not to get off topic or anything but within the system I am developing joins are handled through something called includes. So each include in the below scenario would represent a join. The system then is able to resolve the associations on a generic level so no code repetition or hard coding is needing. Just define a base class with the associations and everything will be worked out by system. This includes the construction of a SQL one-dimensional result set into a tree hierarchy representative of the associations.

    PHP Code:
    $sites =
    Site::find(
        array(
            
    'include'=>'units'
        
    )
        ,array(
            
    'include'=>'preference_options'
        
    )
        ,array(
            
    'include'=>array('preferences','preference_section')
        )
        ,array(
            
    'include'=>'user'
        
    )
    ); 
    PHP Code:
    $sites[6]->units[5]->preference_options[2]->preferences[3]->user->name
    There is no way to accomplish this on a generic level using a DAO. Instead every relationship must be hard coded and each result set handled separately. That is why if know ahead of time your going to need advanced joins that a DAO generally isn't the best route in my opinion.

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    The only way to avoid the creation of a separate DAO and/or data object for each different multiple table result is to create a generic system. The DAO system which you are creating more or less condones to Gateway principles not ActiveRecord ones. The major distinction between the two are that the data and logic to get that data are not separate in a ActiveRecord while they are in a Gateway.

  14. #14
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    The only way to avoid the creation of a separate DAO and/or data object for each different multiple table result is to create a generic system. The DAO system which you are creating more or less condones to Gateway principles not ActiveRecord ones.
    Do you mean condones or conforms? My interpretation of 'Gateway principles' is based on Coldfusion oo terminology - result sets obtained from multiple tables and records are dealt with as queries/arrays.

    From my understanding of active record, it represents system objects as they are related in the database and NOT how they are in 'real life'. Is that the fundamental problem - if using active record for example, you ignore the true relationship between objects (ie composition between site->unit), and deal with it as it as they are related in the database?

    eg If I used active record (or moved my DAO implementation in this direction), I wouldn't bother doing ...

    class site {}
    class unit extends site{}

    but instead would have

    class site
    {
    private $site_id
    private $site_name
    private $units = array() ;
    }
    class unit {}

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    This is incredibly difficult to explain but this is the basic physics of how the system which I am building handles the management of multiple tables. Maybe it will prompt some ideas on your apart for your own system.

    PHP Code:
    class {
        
        public static 
    $hasOne 'b';
        

    PHP Code:
    class {

        public static 
    $belongsTo 'a';
        public static 
    $hasMany 'cs';


    PHP Code:
    class {

        public static 
    $belongsTo 'b';


    PHP Code:
    a::find(
        array(
            
    'include'=>'b'
        
    )
        ,array(
            
    'include'=>'cs'
        
    )
    ); 
    • a (root)
      • b (child of a)
        • c (child of b)


    A INNER JOIN B INNER JOIN C

    1. Since A is a parent of B A's primary key is mapped to B's derived foreign key. (a_id)
    2. Since B is a parent of C B's primary key is mapped to C's derived foreign key. (b_id)


    A INNER JOIN a ON a.id = b.a_id INNER JOIN c ON b.id = c.b_id

    All parts of the tree ar then given a unique alias. That alias is prefixed to all columns. This is also to avoid conflict in names if table a has column with the same name as table b etc.

    t0_a_name
    t1_b_name
    t2_c_name

    So once the SQL result is returned the tree can be rebuilt based the primary key of each node which is also selected by default. The prefix maps back to node which the column belongs. The node representing the table and model.

    • a (t0)
      • a_id
      • a_name
      • b (t1)
        • b_id
        • b_name
        • c (t2)
          • c_id
          • c_name


    Which would then allow:

    PHP Code:
    $a[2]->b->cs[0]->name

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    If the object that communicates with the database is separate from the object(s) that store the data your dealing with a Gateway. If the domain level data storage objects directly or indirectly communicate with the database your dealing with a ActiveRecord. That is the major difference between the two which isn't really all that much of a difference at all.

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    PHP Code:
    class site
    {
    private 
    $site_id
    private $site_name
    private $units = array() ;
    }

    class 
    unit {} 
    Yes, yet that doesn't alleviate the need to hard code everything still.

  18. #18
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    PHP Code:
    class site
    {
    private 
    $site_id
    private $site_name
    private $units = array() ;
    }

    class 
    unit {} 
    Yes, yet that doesn't alleviate the need to hard code everything still.
    I don't know if I have missed the point about having to 'hard code everything', but I would ultimately be looking at using a code generator. 'unit' in this case would be defined also with

    unit
    {
    private $site_id
    private $belongsTo = 'site'
    }

    To be able to identify which table object that site_id 'belongsTo', I could generate this from the database schema relationships.

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    This is would be an example of how the association would be made within the system I'm building. Once this is done the system would then be able to resolve the relationship between a site => unit and unit => site.

    PHP Code:
    class Site {
        
        public static 
    $primaryKey 'site_id';
        
        public static 
    $hasMany 'units';

    }

    class 
    Unit {
      
           public static 
    $primaryKey 'unit_id';

        public static 
    $foreignKeys = array(
            
    'site_id'=>'Site'
        
    );

        public static 
    $belongsTo 'site';


    Then you could go:

    PHP Code:
    $site Site::find(
        array(
            
    'include'=>'units'
        
    )
    );
    $sites[2]->units[3]->name
    or

    PHP Code:
    $units Unit::find(
        array(
            
    'include'=>'site'
        
    )
    );
    $units[2]->site->name

  20. #20
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for all these ideas ... this looks very interesting. If you expand this out to the kind of hierachy you described earlier ...

    $sites[6]->units[5]->preference_options[2]->preferences[3]->user->name;

    Is your example describing? .. 'The site (at index 6) and unit (index 5) is the preference option (at index 2) selected as a preference (at index 3) by the user'

    When it come to outputting preferences to screen, I assume I'd really need it in the reverse?

    $preferences->$preference_options->$units->$sites

    so then I'd perform nested loops through each collection to output the data

  21. #21
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Quote Originally Posted by Hgiel
    When it come to outputting preferences to screen, I assume I'd really need it in the reverse?
    Not exactly.

    PHP Code:
    $prefs 
    Preference::find(
        array(
            
    'include'=>'preference_option'
        
    )
        ,array(
            
    'include'=>'unit'
        
    )
        ,array(
            
    'include'=>'site'
        
    )
    ); 

    That would auto generate and run this SQL:

    Code SQL:
    SELECT 
         t0.`preference_id` AS t0_preference_id
         ,t0.`participant_id` AS t0_participant_id
         ,t0.`preference_option_id` AS t0_preference_option_id
         ,t0.`preference_number` AS t0_preference_number
         ,t1.`preference_option_id` AS t1_preference_option_id
         ,t1.`pref_form_section_id` AS t1_pref_form_section_id
         ,t1.`unit_id` AS t1_unit_id
         ,t1.`display_order` AS t1_display_order
         ,t1.`is_displayed` AS t1_is_displayed
         ,t2.`unit_id` AS t2_unit_id
         ,t2.`site_id` AS t2_site_id
         ,t2.`unit_name` AS t2_unit_name
         ,t3.`site_id` AS t3_site_id
         ,t3.`site_name` AS t3_site_name 
      FROM 
         preference AS t0 
     INNER 
      JOIN 
         preference_option AS t1 
       ON 
         t0.preference_option_id = t1.preference_option_id 
     INNER 
      JOIN 
          unit AS t2 
        ON 
          t1.unit_id = t2.unit_id 
      INNER 
       JOIN 
          site AS t3 
         ON 
          t2.site_id = t3.site_id


    Which in turn would return object that made it possible to do:

    PHP Code:
    $prefs[2]->preference_option->unit->site->site_name
    This is a example of how the models would be set-up to generate that output:

    PHP Code:
    <?php
    class Site extends ActiveRecord {
        
        public static 
    $table 'site';
        public static 
    $primaryKey 'site_id';
        public static 
    $fields = array('site_id','site_name');
        
        public static 
    $hasMany 'units';
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }

    class 
    Unit extends ActiveRecord {
        
        public static 
    $table 'unit';
        public static 
    $primaryKey 'unit_id';    
        public static 
    $fields = array(
            
    'unit_id'
            
    ,'site_id'
            
    ,'unit_name'
        
    );

        public static 
    $foreignKeys = array(
            
    'site_id'=>'Site'
        
    );

        public static 
    $belongsTo 'site';
        
        public static 
    $hasMany = array('preference_options');
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }

    class 
    PreferenceOption extends ActiveRecord {

        public static 
    $table 'preference_option';
        public static 
    $primaryKey 'preference_option_id';
        public static 
    $fields = array(
            
    'preference_option_id'
            
    ,'pref_form_section_id'
            
    ,'unit_id'
            
    ,'display_order'
            
    ,'is_displayed'
        
    );
        
        public static 
    $foreignKeys = array(
            
    'unit_id'=>'Unit'
            
    ,'pref_form_section_id'=>'PrefFormSection'
        
    );
        
        public static 
    $hasMany = array('preferences');
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }

    class 
    PrefFormSection extends ActiveRecord {

        public static 
    $table 'pref_form_section';
        public static 
    $primaryKey 'pref_form_section_id';
        public static 
    $fields = array(
            
    'pref_form_section_id'
            
    ,'pref_form_section_name'
        
    );
        
        public static 
    $hasMany = array('preference_options');

        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }

    class 
    Preference extends ActiveRecord {

        public static 
    $table 'preference';
        public static 
    $primaryKey 'preference_id';
        public static 
    $fields = array(
            
    'preference_id'
            
    ,'participant_id'
            
    ,'preference_option_id'
            
    ,'preference_number'
        
    );
        
        public static 
    $foreignKeys = array(
            
    'preference_option_id'=>'PreferenceOption'
        
    );
        
        public static 
    $belongsTo 'preference_option';
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }
    To create something generic like this though is a project in itself. It will be easier to hard code everything. At least that is relatively straightforward. Building something like this not so much.

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    By defining the user model all defining its relationship to a preference any user/users preferences can easily be found with their preference_option, unit,site.

    PHP Code:
    $user3 
    Participant::find(
           
    ActiveRecord::findOne // returns single object rather then a collection.
        
    ,array(
            
    'include'=>'preferences'
            
    ,'user_id'=>3
        
    )
        ,array(
            
    'include'=>'preference_option'
        
    )
        ,array(
            
    'include'=>'unit'
        
    )
        ,array(
            
    'include'=>'site'
        
    )
    ); 
    Which would generate the below SQL:

    Code SQL:
    SELECT 
         t0.`user_id` AS t0_user_id
         ,t0.`user_name` AS t0_user_name
         ,t1.`preference_id` AS t1_preference_id
         ,t1.`participant_id` AS t1_participant_id
         ,t1.`preference_option_id` AS t1_preference_option_id
         ,t1.`preference_number` AS t1_preference_number
         ,t2.`preference_option_id` AS t2_preference_option_id
         ,t2.`pref_form_section_id` AS t2_pref_form_section_id
         ,t2.`unit_id` AS t2_unit_id
         ,t2.`display_order` AS t2_display_order
         ,t2.`is_displayed` AS t2_is_displayed
         ,t3.`unit_id` AS t3_unit_id
         ,t3.`site_id` AS t3_site_id
         ,t3.`unit_name` AS t3_unit_name
         ,t4.`site_id` AS t4_site_id
         ,t4.`site_name` AS t4_site_name 
      FROM 
         users AS t0 
      INNER 
       JOIN 
          preference AS t1 
        ON 
         t0.user_id = t1.participant_id 
     INNER 
      JOIN 
         preference_option AS t2 
        ON 
         t1.preference_option_id = t2.preference_option_id 
     INNER 
      JOIN 
         unit AS t3 
        ON 
         t2.unit_id = t3.unit_id 
     INNER 
      JOIN 
         site AS t4 
        ON 
         t3.site_id = t4.site_id 
     WHERE 
         t0.user_id = ?

    PHP Code:
    $user3->preferences[2]->preference_option->unit->site->site_name
    revised preference and participant models:
    PHP Code:
    class Preference extends ActiveRecord {

        public static 
    $table 'preference';
        public static 
    $primaryKey 'preference_id';
        public static 
    $fields = array(
            
    'preference_id'
            
    ,'participant_id'
            
    ,'preference_option_id'
            
    ,'preference_number'
        
    );
        
        public static 
    $foreignKeys = array(
            
    'preference_option_id'=>'PreferenceOption'
            
    ,'participant_id'=>'Participant'
        
    );
        
        public static 
    $belongsTo 'preference_option';
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }

    }

    class 
    Participant extends ActiveRecord {

        public static 
    $table 'users';
        public static 
    $primaryKey 'user_id';
        public static 
    $fields = array(
            
    'user_id'
            
    ,'user_name'
        
    );
        
        public static 
    $hasMany = array('preferences');
        
        public static function 
    find() {
            
    $args func_get_args();
            return 
    parent::_find(__CLASS__,$args);
        }


    That is why I prefer the ActiveRecord although I'm sure the algorithms here could be worked into a Gateway. As I'm sure you can see the problem becomes much more complex when you need to manage more than one table as a single entity or collection of entities.

  23. #23
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks heaps for these ideas. Just a basic question for the moment - due to the nature of what you are generating (a hierarchical structure of results), versus a 'row by row' set, if for example you only wanted to output something like

    Preference 1 ... Site ABC
    Preference 2 ... Site ABC
    Preference 3 ... Site XYZ
    etc

    you just do a bunch of nested loops ? ...

    Code:
    loop (preferences)
    {
       echo 'Preference ' . preference->pref_number ;
    
       loop (preference_options)
       {
         loop (units)
         {
            loop (sites)
            {
                  echo '....' . site->site_name ;
            }
         }
       }
    }

  24. #24
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    yep, that is what you do in the view.

    PHP Code:
    foreach($users as $user) {

         foreach(
    $user->preference_options as $option) {
            
         }


    Due to the way the application intelligently recollects the results $users[1] is different from $users[2], etc. If the result wasn't processed in this way then $user[1] could be the same as $user[2], etc since the user will be repeated for each preference,etc in the raw result set.

    Eliminating the repeating results is done by comparing primary keys at each node level in a recursive manor. If a node at the given level has a object with a primary key equal to the current row then a new object is not created for that node. Instead the current object becomes the one already in memory and process repeats until the leaf is reached of the tree.

    A separate class handles this entire process and its done row by row recursively based on the select tree.

    PHP Code:
    $collectionAgent = new ActiveRecordCollectionAgent($select);
    while(
    $row $stmt->fetch(PDO::FETCH_ASSOC)) {
        
    $collectionAgent->process($row,$node);


  25. #25
    SitePoint Member
    Join Date
    May 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, now my original idea is to have a method somewhere called displayPlacementName() which outputs the site/unit in the following format:

    Site ABC - Unit 123

    Something like this is where I am also not so sure where to define - do I create it in either of the unit or site classes, or in some type of utilsFormat class, and as I loop through the object collections, I just pass it the site and unit names?

    For some reason I get the feeling that as things progress, the classes defined based on the database don't really have many if any extra methods apart from getters/setters?


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
  •