SitePoint Sponsor

User Tag List

Page 2 of 5 FirstFirst 12345 LastLast
Results 26 to 50 of 105
  1. #26
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by michel
    I needed a way to parameterize data fetching queries and reduce the amount of repetitive elements in SQL statements. It's not SQL abstraction I'm after.
    Good enough reason.

    Quote Originally Posted by michel
    Sounds like utopia to me
    Ok, maybee I put it a bit blunt. What I ment was that you don't need to change *much* for you code. If you abstract the table fields into seters/geters, properties, or something else in a criteri/query object you only need to change the effect setter in the base class if any of your table field names change.

  2. #27
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    Ah yes, I'm aware of that - but if you select from a table with many cols, you're going to get maybee 15-20 or more fields to parse vs. the table type. And if you start doing several queries on several different tables, you're getting quite many loops and such that can be avoided.

    The other thing is that this (imho) is harder to code then a small regex-lexer/parser.
    Parsing SQL is way harder than seeing if two arrays array_keys($row) & $map['fields'] are the same, imho. Plus it doesn't prevent 'CALL spPersonByName %s'

  3. #28
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A solution to mapping N:M relations "automagicly" just sprung into my mind, example:

    You have a Person table and a Group table. One Person can have many Groups and one Group can have many Persons as it's member. The table to "map" this relation would be called Group_Person (alphabetical order, G > P). Now when the database parser finds a table that consists of X_Y (where X and Y are two different tablenames of already existing tables) it will automagicly set up $person->group and $group->person and mapp the relations - what do you guys feel about this aproach? to strict?

  4. #29
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    A solution to mapping N:M relations "automagicly" just sprung into my mind, example:

    You have a Person table and a Group table. One Person can have many Groups and one Group can have many Persons as it's member. The table to "map" this relation would be called Group_Person (alphabetical order, G > P). Now when the database parser finds a table that consists of X_Y (where X and Y are two different tablenames of already existing tables) it will automagicly set up $person->group and $group->person and mapp the relations - what do you guys feel about this aproach? to strict?
    To strict in my mind, I would probably have named it something like Memberships. Perhaps you could take a cue from the Rails ActiveRecord implementation and establish a default but allow for an easy override?
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  5. #30
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    Ok, maybee I put it a bit blunt. What I ment was that you don't need to change *much* for you code. If you abstract the table fields into seters/geters, properties, or something else in a criteri/query object you only need to change the effect setter in the base class if any of your table field names change.
    Ah yes, DRY–Don't Repeat Yourself. I like to stick to that as much as possible. I used to have constants for every parameter name, attribute name and what have you. The thing is, it lead to overly complex/verbose code. This probably has something to do with my limited skills as well .
    It's also a matter of taste of course. Something like
    PHP Code:
      $criteria = new Criteria;
      
    $criteria->add(Person::AGE$ageCriteria::GREATER_THAN);
      
    $criteria->add(Person::HEIGHT$heightCriteria::LESS_EQUAL);
      
    $persons $object->findAll($criteria); 
    is a little too verbose for me if I can do
    PHP Code:
    $persons $object->findAll('age>? AND height<=?', array($age$height)); 

  6. #31
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje
    To strict in my mind, I would probably have named it something like Memberships. Perhaps you could take a cue from the Rails ActiveRecord implementation and establish a default but allow for an easy override?
    Good idea there. I don't think that there's any way to escape some typ of mapping in the end =/.

    What do you feel about the "Person::Group >> Group" ? (That the field Group is locked onto the table Group - to strict?

  7. #32
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by michel
    Ah yes, DRY–Don't Repeat Yourself. I like to stick to that as much as possible. I used to have constants for every parameter name, attribute name and what have you. The thing is, it lead to overly complex/verbose code. This probably has something to do with my limited skills as well .
    It's also a matter of taste of course. Something like
    PHP Code:
      $criteria = new Criteria;
      
    $criteria->add(Person::AGE$ageCriteria::GREATER_THAN);
      
    $criteria->add(Person::HEIGHT$heightCriteria::LESS_EQUAL);
      
    $persons $object->findAll($criteria); 
    Yes, this imho is... to much. As some people on this forum is very fond of saying KISS - Keep It Simple Stupid. The above code looks very nice in small examples, but when you're starting to have complex quries of several nested AND/OR-statements... it get's realy messy realy quick.
    Quote Originally Posted by michel
    is a little too verbose for me if I can do
    PHP Code:
    $persons $object->findAll('age>? AND height<=?', array($age$height)); 
    Ah yes, but I realy don't see the great benefit of the findAll() statement compared to the findBySQL("select * from...");. Yes it's kinda quicker to write maybee but you loose the only good thing (imho) about wrapping SQL in objects/methods - abstraction.

  8. #33
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    Good idea there. I don't think that there's any way to escape some typ of mapping in the end =/.

    What do you feel about the "Person::Group >> Group" ? (That the field Group is locked onto the table Group - to strict?
    It does make sense to me that $person->Groups would point to an array of Group objects for which the person is associated with (perhap even automatically lazy loaded?) Of course now you are descending into the whole pluralzation issue
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  9. #34
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    Ah yes, but I realy don't see the great benefit of the findAll() statement compared to the findBySQL("select * from...");. Yes it's kinda quicker to write maybee but you loose the only good thing (imho) about wrapping SQL in objects/methods - abstraction.
    If such a findBySql() method would offer parameterization for commonly needed functionality like sorting and limiting result sets the find*() methods would provide no real advantages.

    Of course there's a difference in approach here. For me, the question is where to put database interaction methods: in a central/coordinating object like UnitOfWork or a base model/data object/business object.
    Last edited by michel; Jan 26, 2006 at 11:28.

  10. #35
    SitePoint Zealot DerelictMan's Avatar
    Join Date
    Oct 2005
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    Quote Originally Posted by michel
    It's also a matter of taste of course. Something like
    PHP Code:
      $criteria = new Criteria;
      
    $criteria->add(Person::AGE$ageCriteria::GREATER_THAN);
      
    $criteria->add(Person::HEIGHT$heightCriteria::LESS_EQUAL);
      
    $persons $object->findAll($criteria); 
    is a little too verbose for me...
    Yes, this imho is... to much. As some people on this forum is very fond of saying KISS - Keep It Simple Stupid. The above code looks very nice in small examples, but when you're starting to have complex quries of several nested AND/OR-statements... it get's realy messy realy quick.
    Just to play devil's advocate (again ) it all depends on what you are doing. For simple cases the Criteria object approach looks way overkill. But one benefit it has is that it allows you to do is take a Criteria object and conditionally (and iteratively) adjust it/add more criteria/etc. without having to care about what had been done to it previously. To me that's a huge advantage over processing an arbitrary SQL string, especially if you get to the point where your criteria (or sql) is being passed as a parameter between layers. (Of course some may say if you get to that level you need to refactor, but I still feel an object with a well defined API is easier to manipulate in a general fashion than an SQL statement, especially if you don't want to create a general purpose SQL parser for your ORM).

    I'm not saying that the Criteria object approach works all the time; as usual there are tradeoffs involved. It's just that I believe the Criteria object approach does, in some situations, offer some advantages. As for the KISS principle, that can be taken too far, IMHO (Zend's "Extreme Simplicity" comes to mind ). To throw another aphorism out, "Everything should be made as simple as possible, but no simpler."

  11. #36
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DerelictMan
    [...]
    I'm not saying that the Criteria object approach works all the time; as usual there are tradeoffs involved.[...]
    I agree with you, but the thing is that most of the times the tradeoffs are hard to motivate, especially in webb apps imho.

  12. #37
    SitePoint Evangelist
    Join Date
    Jun 2003
    Location
    Melbourne, Australia
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    The "strval($object);" thing is atm. not needed realy, and is a leftover from and old version of the code. What it does is that for the dirty-new objects (ie: the one's that are just created, and doesn't have reference in the DB) it does a strval($object); on them to get a Key to use in an assc. array.

    All the "real" objects(the ones that have a reference in the DB) are saved in the identitymap with a "TypePKValue" key.
    Yes, I understand that. But what I don't understand is what role the IdentityMap plays in the interactions between the UnitofWork object and the various Units.

    This doesn't seem to challenge others here (or they don't care). If you're standing next to me, just put on that t-shirt that says "I'm with Stupid"!

    Later...
    Oh, I get it. It acts as a central repository of objects.
    Last edited by auricle; Jan 27, 2006 at 04:12. Reason: Enlightenment!
    Zealotry is contingent upon 100 posts and addiction 200?

  13. #38
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by thr
    Just one example is the Person <> Group relation. One person can have many Groups and one Group can have many Persons(members).
    I think better is...

    Person (1)-->(*) Membership (*)<--(1) Group

    ...as Jason pointed out. N:M relations will get you into all sorts of trouble with changing group names from inside a Person class. In these situations, you actually want to pull apart the link table.

    I've never needed N:M, and skimming "Data Model Patterns" (Hay) and "The Data Model Resource Book" (Silverston) I cannot find a single N:M relation. All are 1:N with named intermediaries.

    I think it's low value.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  14. #39
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DerelictMan
    Just to play devil's advocate (again ) it all depends on what you are doing. For simple cases the Criteria object approach looks way overkill. But one benefit it has is that it allows you to do is take a Criteria object and conditionally (and iteratively) adjust it/add more criteria/etc. without having to care about what had been done to it previously.
    I agree, there's a point in encapsulating an SQL statement, certainly with selects. I just have a feeling I will be losing expressiveness with a Torque/Propel-like approach.

  15. #40
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    If you abstract the table fields into seters/geters, properties, or something else in a criteri/query object you only need to change the effect setter in the base class if any of your table field names change.
    How about symbolizing column (and perhaps table) names? To stick with my simplistic example:
    PHP Code:
         $persons $object->findAll('@age>? AND @height<=?', array($age$height)); 

  16. #41
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    Person (1)-->(*) Membership (*)<--(1) Group

    ...as Jason pointed out. N:M relations will get you into all sorts of trouble with changing group names from inside a Person class. In these situations, you actually want to pull apart the link table.

    I've never needed N:M, and skimming "Data Model Patterns" (Hay) and "The Data Model Resource Book" (Silverston) I cannot find a single N:M relation. All are 1:N with named intermediaries.

    I think it's low value.
    Edited this in about 2-3h after answering you, read the code again and realized i was doing it wrong...

    Ok, so you're sugesting this:

    PHP Code:
    foreach($person->membership as $membership){
        echo 
    $membership->group->name;

    ? This seems a bit more readable + it's a lot more well, clear . I love this forum
    Last edited by thr; Jan 27, 2006 at 06:13.

  17. #42
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by michel
    How about symbolizing column (and perhaps table) names? To stick with my simplistic example:
    PHP Code:
         $persons $object->findAll('@age>? AND @height<=?', array($age$height)); 
    But this isn't realy symobolizing coulmn names? or? The code above looks to me as if it's only symbolozing the arguments and not the colnames(maybee i missed something?)?

    I was thinking more in lines of this:

    PHP Code:
    $persons $worker->findAllPersons('%s > 9 AND %s < 120',Person::Age,Person::Height
    or did I miss something? ;p

    Quote Originally Posted by auricle
    Oh, I get it. It acts as a central repository of objects.
    Yes

    Quote Originally Posted by michel
    I agree, there's a point in encapsulating an SQL statement, certainly with selects. I just have a feeling I will be losing expressiveness with a Torque/Propel-like approach.
    Yeah well there is a point in doing it, and that point(imho) is abstraction, without the abstraction it's hard to motivate it (imho). And most of the time when i need abstraction.. i still have a hard time motivating it due to the complexity of criteria-style solutions.

  18. #43
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    But this isn't realy symobolizing coulmn names? or? The code above looks to me as if it's only symbolozing the arguments and not the colnames(maybee i missed something?)?
    If a map exists which registers column names the symbols could be substituted with their names. That way you can express column names directly in SQL instead of supplying constants as method arguments.

    Quote Originally Posted by thr
    PHP Code:
    $persons $worker->findAllPersons('%s > 9 AND %s < 120',Person::Age,Person::Height
    My guess is that this would get messy quickly, symbolizing both column names and values. Thats a whole lot of arguments to supply. Hard to keep track of probably, especially with sprintf()-like positional substitution.

  19. #44
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    I've never needed N:M, and skimming "Data Model Patterns" (Hay) and "The Data Model Resource Book" (Silverston) I cannot find a single N:M relation. All are 1:N with named intermediaries.
    Off Topic:

    I'm a bit curious about the named intermediaries. Could you provide a quick example? Database design isn't exactly my strong suit Thanks.

  20. #45
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by michel
    Off Topic:

    I'm a bit curious about the named intermediaries. Could you provide a quick example? Database design isn't exactly my strong suit Thanks.
    What I THINK he's meaning is this:

    Person > Membership < Group, tables would look something like this:

    Code:
    #Person
     - Id (PK)
     - Name
     - Age
     - Email
    
    #Membership
     - Id (PK - This isn't always needed depening on how your code works)
     - Person (FK > Person.Id)
     - Group (FK > Group.Id)
    
    #Group
     - Id (PK)
     - Name
     - Comment
    Then when you want to add a "membership" you do something like this(if we're talking ORM now).

    PHP Code:
    /* $thr = person object
    $admin = group object */
    $membership = new Membership;
    $membership->person $thr;
    $membership->group $admin;
    $membership->commit(); 
    ... or something like that. The membership table could ofc. hold more then just the ID of the person/group, for example joindate.

    Edit:
    Quote Originally Posted by Ren
    Parsing SQL is way harder than seeing if two arrays array_keys($row) & $map['fields'] are the same, imho. Plus it doesn't prevent 'CALL spPersonByName %s'
    What if two tables have the same fields? For example Group and Person could be these two tables:


    Code:
    #Person
    - Id
    - Name
    
    #Group
    - Id
    - Name
    How do you differ them without parsing the SQL?

  21. #46
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    The Netherlands
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    What I THINK he's meaning is this:

    Person > Membership < Group, tables would look something like this:

    Code:
    #Person
        - Id (PK)
        - Name
        - Age
        - Email
       
       #Membership
        - Id (PK - This isn't always needed depening on how your code works)
        - Person (FK > Person.Id)
        - Group (FK > Group.Id)
       
       #Group
        - Id (PK)
        - Name
     - Comment
    A separate table storing relations? I have used this before. I wouldn't know how to (sanely) store many to many relations another way.
    Came accross an interesting Hibernate - Rails ActiveRecord comparison by the way.

  22. #47
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by michel
    A separate table storing relations? I have used this before. I wouldn't know how to (sanely) store many to many relations another way.
    Me neither, but this sollution (I think) differs in the fact that it gives the Membership-table a more important role then just holding FKs.

    Quote Originally Posted by michel
    Came accross an interesting Hibernate - Rails ActiveRecord comparison by the way.
    Interesting indeed, and he took one of the problems I've been pondering - Lazy Loading. And the fact that Lazy Loading is "only good if you don't need the data". This is quite an easy trap to fall in as you have to have some type of option to "disable" lazy loading for the parts that don't need it. For example, a "News"-row might have several "Comment"-rows, this becomes an:

    "News (1) <---- (*) Comment" relationship, now say that this News-row has 100 comments. If we force lazy loading we're doing 1+100 queries to display the news + it's comments,where we could've done 1+1 queries.

    Edit 1: Another thing I found in the article you posted about rails is this:
    Code:
    @miners = Miner.find_by_first_name_and_last_name("Elma", "Garrott")
    This could be achieved in php also with the __call function.

    Edit 2: Using lastcrafts example of the Person > Membership < Group tables(that I wrote), the phpcode to for example load all the memberships for a person/group would look something like this:

    PHP Code:
    echo $person->name " has membership in the following groups: ";
    foreach(
    $person->memberships as $membership){
        echo 
    $membership->group->name "<br />";
    }

    /* OR */

    echo "The " $group->name " group has the following members: ";
    foreach(
    $group->memberships as $membership){
        echo 
    $membership->person->name "<br />";

    clear code or confusing? I like it, how about you?

    Edit 3: One way to work around the Lazy Loading "problem" would be this:

    PHP Code:
    list($news) = $worker->getBySQL("SELECT * FROM Newspost ORDER BY Date DESC LIMIT 1",1); 
    WHere the 2nd argument (1 in this case) is how many "levels" it should nest and load objects before bailing out. Standard is 0 which == Lazy Loading, 1 let's the $news->author, $news->comment things get loaded, if I'd set a 2 it would also load the $news->author->membership and $news->comment->author for example.

  23. #48
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr

    Edit: What if two tables have the same fields? For example Group and Person could be these two tables:


    Code:
    #Person
    - Id
    - Name
    
    #Group
    - Id
    - Name
    How do you differ them without parsing the SQL?
    I'd probably introduce a optional parameter for the $type in the getBySQL() method.

    Because a query like finding all the people that belong to more than one group, could look like
    Code:
    SELECT p.* FROM Membership m INNER JOIN Person p ON p.id = m.person GROUP BY 1, 2
    HAVING COUNT(*) > 1;

  24. #49
    SitePoint Guru thr's Avatar
    Join Date
    Jun 2003
    Location
    Sweden
    Posts
    664
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren
    I'd probably introduce a optional parameter for the $type in the getBySQL() method.

    Because a query like finding all the people that belong to more than one group, could look like
    Code:
    SELECT p.* FROM Membership m INNER JOIN Person p ON p.id = m.person GROUP BY 1, 2
    HAVING COUNT(*) > 1;
    I'd still stick to the SQL parsing and introduce the 2nd paramter to "skip" SQL-parsing and force one type of object. I realy don't like the compare fields <> map thingy.

  25. #50
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thr
    I'd still stick to the SQL parsing and introduce the 2nd paramter to "skip" SQL-parsing and force one type of object. I realy don't like the compare fields <> map thingy.
    Problem with that is, if write some method

    $person = $..->getBySQL('SELECT id, name FROM Person WHERE ... ');

    And if later alter the Person table, by adding a column, the above should raise an error, imo. As its not retrieving all required fields.


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
  •