SitePoint Sponsor

User Tag List

Page 5 of 5 FirstFirst 12345
Results 101 to 121 of 121
  1. #101
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Australia
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    back to the solution of groupBy="id", at what stage did you actually do the grouping?

    With dynamic SQL, i'm still pondering if to add or not.

  2. #102
    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 wei
    back to the solution of groupBy="id", at what stage did you actually do the grouping?
    After the query, it's not possible to do it before so you have to live with the big dataset and strip out the parts you don't want.

    Quote Originally Posted by wei
    With dynamic SQL, i'm still pondering if to add or not.
    I'm not going to do it, as it's way to cumbersome and the nature of php lends itself very easily to a generator="" implementation imho.

  3. #103
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PEAR::DB_DataObject

    What about http://pear.php.net/package/DB_DataObject ?

    Not as complex as the packages discussed here, but I think it does pretty good job in mapping. Generates classes before hand. Pretty "KISS".

  4. #104
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Venn vs ORM?

    Here's a question: how to you represent a Venn diagram with a object API? IMO, no matter how much you try, the result will always be ugly.

    Meanwhile SQL is a mini language that is able to express set relationships / insections / unions etc.

    Anyway - posted more thoughts in that direction a while back here - point being think Selkirk has the smartest approach in WACT which I'd sum up as "get me the data structures I need with the least possible effort";

    PHP Code:
    foreach ( DBC::getOneColumnArray('SELECT name FROM users') as $user ) {
        print 
    "<option>".htmlspecialchars($user)."</option>\n";


  5. #105
    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 HarryF
    Here's a question: how to you represent a Venn diagram with a object API? IMO, no matter how much you try, the result will always be ugly.

    Meanwhile SQL is a mini language that is able to express set relationships / insections / unions etc.

    Anyway - posted more thoughts in that direction a while back here - point being think Selkirk has the smartest approach in WACT which I'd sum up as "get me the data structures I need with the least possible effort";

    PHP Code:
    foreach ( DBC::getOneColumnArray('SELECT name FROM users') as $user ) {
        print 
    "<option>".htmlspecialchars($user)."</option>\n";

    Not sure about the Venn diagram analogy, I can sort of see it 2 tables being different circles, and the overlap between them is the data that can be joined. But I think a schema is more of graph, tables being nodes, and relationships the edges, which is easy to express as an object api.

    I think the problem you posted about in the link, is perhaps an inflexibility in the ORM. You should be able to write complex sql, and be able to extract the row data into some object structure.

  6. #106
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure about the Venn diagram analogy, I can sort of see it 2 tables being different circles, and the overlap between them is the data that can be joined.
    The point I was trying to make was relational databases are really about sets.

    If I consider a request like "get me all users in the 'editors' group who haven't logged in the last month" and I have three tables 'users', 'groups' and 'access_logs', this can be expressed in a single SQL statement.

    But using objects it going to to get painful - assuming you have a library that supports this, what would using that library look like in terms of code?

  7. #107
    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 HarryF
    The point I was trying to make was relational databases are really about sets.

    If I consider a request like "get me all users in the 'editors' group who haven't logged in the last month" and I have three tables 'users', 'groups' and 'access_logs', this can be expressed in a single SQL statement.

    But using objects it going to to get painful - assuming you have a library that supports this, what would using that library look like in terms of code?
    I'm wasn't advocating not writing SQL. Just saying that you should still be able to use ORM. So perhaps crosswires...

    The problem with SQL is it can lead to repeating yourself, embedding domain logic in several sql queries. Violating the DRY principal.

    As for the problem posed...
    PHP Code:
    $result $cnn->query('SELECT u.* FROM users u INNER JOIN groups g ON u.groupId = g.groupId WHERE g.name = :group AND NOT EXISTS(SELECT 1 FROM access_log a WHERE u.userId = a.userId AND a.lastAccessed > :lastAccessed', array('group' => 'editors''lastAccessed' => strtime('-1 Month'time()));

    $userMapper = new UserMapper();
    $users = array();
    foreach(
    $result as $row)
         
    $users[] = $userMapper->create($row); 

  8. #108
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've developed an ORM approach that basically does what you just described Harry. It looks like an ActiveRecord kind of pattern, but you can pull the optional links between the 'objects' (tables) by giving optional parameters.

    If you look at Rails 1.1, they introduced what I basically have where you can pull indirect relationships with tables that are linked with a middle one.

    Once I get it working properly and have beefed it up, I'll be glad to post the source. I'm trying to keep it simple and I'm sure it won't fit 100% of all situations encountered, but it will probably be good enough for the 95% of SQL calls people make. For the other unmanageable queries with this thing (if it can't do them because of very particular conditions), then people will go with a custom query call using a DB driver instead. I have it working as a demo for the fetching of links based on the primary table's PK.
    David

  9. #109
    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 ClickHeRe
    I've developed an ORM approach that basically does what you just described Harry. It looks like an ActiveRecord kind of pattern, but you can pull the optional links between the 'objects' (tables) by giving optional parameters.

    If you look at Rails 1.1, they introduced what I basically have where you can pull indirect relationships with tables that are linked with a middle one.

    Once I get it working properly and have beefed it up, I'll be glad to post the source. I'm trying to keep it simple and I'm sure it won't fit 100% of all situations encountered, but it will probably be good enough for the 95% of SQL calls people make. For the other unmanageable queries with this thing (if it can't do them because of very particular conditions), then people will go with a custom query call using a DB driver instead. I have it working as a demo for the fetching of links based on the primary table's PK.
    The base layer of my (highly experimental) ORM is at http://www.sitepoint.com/forums/showthread.php?t=356259

    It can automatically build a FROM clause just by the columns being retrieve or in the WHERE condition. But not sure how useful it is really atm.

    Example usage that works so far...

    PHP Code:
    class Person
    {
         private 
    $id;
         public 
    $name;

         function 
    __construct($name) { $this->name $name; }

         function 
    getId() { return $this->id; }
         function 
    setId($id) { $this->id $id; }
         function 
    getName() { return $this->name; }
    }

    class 
    Employee extends Person
    {
         protected 
    $wage;

         function 
    __construct($nameMoney $wage)
         {
            
    parent::__construct($name);
            
    $this->wage $wage;
         }

         function 
    getWage() { return $this->wage; }
         function 
    setWage(Money $wage) { $this->wage $wage; }
    }

    $catbert = new Employee('Catbert', new Money('PNT'8));
    $dilbert = new Employee('Dilbert', new Money('PNT'12));
    $orm->insert($cnn$catbert);
    $orm->insert($cnn$dilbert); 
    Where employee maps to the two tables (using table inheritance.. )

    Code:
    CREATE TABLE Persons(personId INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL);
    CREATE TABLE Employees(employeeId INTEGER NOT NULL PRIMARY KEY REFERENCES Persons(personId), currency CHAR(3) NOT NULL, amount NOT NULL);

  10. #110
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My approach (experimental) is to pull with an ORMBuilder all the data from the Database tables and build classes named _Table which know exactly their relationships with the other tables via cross examination (has_many, has_one). These classes extend à basic ORMLayer class that is basically a base ActiveRecord class. Since these classes are abstract, no one can use them directly.

    A user, will then derive a classe of his choice, for the sake of simplicity, let's say whe have a person table.

    Then _Person will created, thuse use will extend it by creating

    class Person extends _Person { }

    This way, any custom functions built-in his class by the user wil no get destroyed or changed if the DB table changes and the ORM stuff has to be regenerated.

    Then by simply calling for example $person->getByPK( $id, $rel = array( 'address' => array( 'address_info' ) ) );

    This will result in pulling the joined data from person and address using their relationship in a single query. The $rel array indicates which relation to use (address is the name of the other table). And also which columns it wants from that table in the join.

    You can imagine any other custum queries derived from such things which I haven't had time to test. But this method works a charm to pull data in my experimental findByPK( ) functions when giving valid relationships. They are auotmatically generated based on the info present in each _Table class which contain the relationships to each other table and by which key they are related. I tried with a 3 and 4 table relationship and it works like a charm. Even, indirect pulling of data works fine.

    I plan to add basic queries to the ORMLayer object and people will be able to derive custom queries in their Table extends _Table classes using those tools.

    I also plan to integrate data integrity things. If you delete a user, then his address should also be deleted or maybe issue a warning saying the statement is incomplète which could cause corrupt data, etc.

    All points I need to explore in the futur.
    David

  11. #111
    ********* 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 HarryF
    The point I was trying to make was relational databases are really about sets.
    Replicating SQL is not really the goal. You usually use O/R tools when you basically want a searchable persistent object model. The RDBMS is used as a convenient store.

    If you want to do complex reporting on the other hand, either you would use SQL, or create a domain specific reporting language in OO. Something like Table, PivotTable, Aggregate, TableDecorator, etc. It's about producing a domain language for your problem rather than using the general purpose raw SQL.

    Stored procedures make a poor tool for building such languages.

    Also, modern SQL isn't entirely set based either. The ANSI join syntax behaves differently depending on the join order for example.

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

  12. #112
    ********* 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 ClickHeRe
    This way, any custom functions built-in his class by the user wil no get destroyed or changed if the DB table changes and the ORM stuff has to be regenerated.
    It's called the GenerationGap pattern. It's one that famously didn't make it into the GOF book.

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

  13. #113
    SitePoint Evangelist ClickHeRe's Avatar
    Join Date
    Mar 2005
    Location
    Ottawa, Canada
    Posts
    580
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi lastcraft,

    Is it covered somewhere ?

    Thanks
    David

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

    In "Pattern Hatching" by Vlissides.

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

  15. #115
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You know this is a subject that really needs similar, detailed, handling to templates, because it's one of those sink holes that people (myself included) have / are wasting a lot of time on.

    I don't have an ORM library of my own and haven't found one I'm happy with - Marcus I know has so that's a different story but every time I've taken this road, the point where I hit something like;

    Code:
    customer.addJoin('LEFT JOIN customer ON cust_order_link.cust_id = customer.id');
    ...is also the point where I wish I hadn't started in the first place.

    When you consider the complexity (and bigger footprint for potential bugs), the performance overhead (not just in terms of code but also if the ORM library is being dumb about queries) and the learning curve, I find myself getting very grumpy.

    Quote Originally Posted by Ren
    The problem with SQL is it can lead to repeating yourself, embedding domain logic in several sql queries. Violating the DRY principal.
    OK but there's a question of "where do I start from?" to be answered.

    Do I adopt a generic solution in advance, to prevent the chance of violating DRY before it happens or do you implement a specific solution to prevent DRY violations as you hit them? With the latter you probably won't "capture" knowledge of the domain but you're also going to end up with something simpler - some specific classes solving specific problems.

    Quote Originally Posted by lastcraft
    Replicating SQL is not really the goal. You usually use O/R tools when you basically want a searchable persistent object model.
    That's a good reason. At the same time I see people trying to meet many other requirements with ORM such as having schema changes automatically propagate into the code (or the reverse with Rails), the holy grail of DB independence, the other holy grail of a true domain model or just the joy of "everything is an object".

    Anyway - just being grumpy. While I'm here, here's another PHP ORM tool I hadn't seen before - http://www.phpdoctrine.com/ - seems to use an entity approach rather than heading for a domain model.

  16. #116
    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 HarryF
    I don't have an ORM library of my own and haven't found one I'm happy with - Marcus I know has so that's a different story but every time I've taken this road, the point where I hit something like;

    Code:
    customer.addJoin('LEFT JOIN customer ON cust_order_link.cust_id = customer.id');
    ...is also the point where I wish I hadn't started in the first place.

    When you consider the complexity (and bigger footprint for potential bugs), the performance overhead (not just in terms of code but also if the ORM library is being dumb about queries) and the learning curve, I find myself getting very grumpy.



    OK but there's a question of "where do I start from?" to be answered.

    Do I adopt a generic solution in advance, to prevent the chance of violating DRY before it happens or do you implement a specific solution to prevent DRY violations as you hit them? With the latter you probably won't "capture" knowledge of the domain but you're also going to end up with something simpler - some specific classes solving specific problems.
    One mthod of not repeating yourself is just to use SQL Views (assuming of course the rdbms supports them) when you see yourself repeating.

  17. #117
    SitePoint Guru BerislavLopac's Avatar
    Join Date
    Sep 2004
    Location
    Zagreb, Croatia
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by HarryF
    every time I've taken this road, the point where I hit something like;
    That's the Law of Leaky Abstractions at work here...

  18. #118
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why no php O/R Mapping tools to date has seen any major community adoption? Here is my point of view:

    - since an ORM framework is the 'heart of application' bugs in it can be absolutely fatal
    - many ORM frameworks have very limited features when it comes to fetching and sql query building -> a developer doesn't have a change to optimize his application
    - many developers think an ORM framework only makes simple problem more complex
    - in general, I think many developers think an ORM framework is too complex for PHP environment
    - There are no good ORM frameworks for PHP which I would use in a big application (not even my own (www.phpdoctrine.com) since its in beta stage)

    Though there many valid points of why PHP community needs a good ORM tool:

    + since an ORM framework handles all the updates/inserts/deletes it is easy to make an always-up-to-date object cache for it (much better than a sql query cache which of course is not always-up-to-date)
    + All SQL is not portable between databases, OQL solves this problem
    + an ORM framework with database specific query optimizations can boost up application performance
    + Since ORM framework builds SQL automatically, the number of written code is hugely reduced
    + OQL is much easier than SQL yet as powerful if implemented right


    Consider you have database tables called users, emails and accounts. Every user may have one email and multiple accounts.
    Now a good ORM framework should be able to select and delete all these record with only four queries (on mysql only two queries would be needed)

    The queries the framework would preform should look something like:

    SELECT * FROM user LEFT JOIN account ON user.id = account.user_id, email WHERE user.email_id = email.id
    DELETE FROM user WHERE id IN (1,2,3...,N)
    DELETE FROM email WHERE id IN (1,2,3,...,M)
    DELETE FROM account WHERE id IN (1,2,3,...,K)

    or on mysql:

    SELECT * FROM user LEFT JOIN account ON user.id = account.user_id, email WHERE user.email_id = email.id
    DELETE user, email, account FROM user LEFT JOIN account ON user.id = account.user_id, email WHERE user.email_id = email.id && user.id IN (1,2,3,...,N)

    I think we all agree that rather than writing these queries by hand (and taking the database specific optimization into consideration) its easier to write something like:

    PHP Code:
    $users $manager->query("FROM User, User.Email, User.Account"); // the query here is pseudo OQL
    $users->delete(); // on mysql performs 1 query, on other dbs 3 queries 
    Then there is the caching issue. Almost all big web applications use some kind of cache
    query cache being one of the most common ones.

    The benefits of using object cache over query cache is that query cache is not up-to-date with database while object cache is.
    The object cache can also be made self-optimized so that only the most accessed objects are kept in cache.
    It completely frees developers from thinking what queries need to be kept in cache and what are not.

    Query cache may also have duplicates of records, since resultSets are saved in cache. Object cache never has record duplicates.
    Last edited by zYne; Mar 30, 2006 at 09:14.

  19. #119
    ********* 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 HarryF
    I don't have an ORM library of my own and haven't found one I'm happy with - Marcus I know has so that's a different story but every time I've taken this road, the point where I hit something like;
    ORM tools are handly if you have dumb SQL meeting dumb objects. Person, Account, Login, AccountEvent, Payments, ..., (groan). You can code generate a lot of tedious stuff. I prefer it when this stuff is zero work.

    If you are not in that situation then either you want SQL (hand coded ActiveRecord or just RecordSet are the way to go) or you shouldn't be using SQL at all. Don't forget full text, LDAP, DBM...

    Quote Originally Posted by HarryF
    Code:
    customer.addJoin('LEFT JOIN customer ON cust_order_link.cust_id = customer.id');
    Left joins are the most common case, so that usually built into the ORM.

    There are only two ways to go with these tools. Either you write a simple tailored one for your domain (it's not worth it for a single app. unless you have a lot of dumb objects) or you use a super duper tool like Hibernate. The middle ground of "almost working" has low value and high effort, because of the leaky abstraction. A simple tool has low general value, but low effort (e.g. Changes which took three days for a first version) or massive effort, but everyone can use it (Hibernate, SDO).

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

  20. #120
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Has anyone had any opinions on using __set_state() magic for a ORM creating the objects? Perhaps something like...

    PHP Code:
    function mapIn(array $row)
    {
        
    // Maps incoming column names to property names
        
    $r array_combine($this->column2Propertyarray_intersect_key($this->column2Property$row));
        
        
    // Build value objects and the like
        
    foreach($this->subMappers as $propertyName => $mapper)
            
    $r[$propertyName] = $mapper->mapIn($row);            
        
        return 
    Person::__set_state($r);

    At the moment I do introspection in the mapper to determine how to get values in and out of objects (constructor, public get*()/set*() method, public property).

  21. #121
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The middle ground of "almost working" has low value and high effort, because of the leaky abstraction. A simple tool has low general value, but low effort (e.g. Changes which took three days for a first version) or massive effort, but everyone can use it (Hibernate, SDO).
    Well put - that basically nails my grumpiness. Interesting - hadn't considered SDO as being an ORM tool per-se but I guess it is - for searchability it's got that XPath-like syntax, plus caching that zYne was talking about. Guess it's finally time to give it a serious look.


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
  •