SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to create flexible find/search methods?

    Let's say I have a database of users I want to perform various searches on.

    Such searches could be:

    - Find all users

    - Find all users, where status = 1

    - Find all users, where status = 1, order by date created
    - Find all users, where status = 1, order by date created DESC

    - Find all users, where status = 1, order by email
    - Find all users, where status = 1, order by email DESC

    - Find all users, where status = 1, order by username
    - Find all users, where status = 1, order by username DESC

    - Find all users, where status = 1 and group = 50, order by date created
    - Find all users, where status = 1 and group = 50, order by date created DESC

    - Find all users, where status = 1 and group = 50, order by email
    - Find all users, where status = 1 and group = 50, order by email DESC

    - Find all users, where status = 1 and group = 50, order by username
    - Find all users, where status = 1 and group = 50, order by username DESC

    - Find all users, where status = 1 and group = 50, order by username, LIMIT 100 OFFSET 100
    - Find all users, where status = 1 and group = 50, order by username DESC, LIMIT 100 OFFSET 100

    - Find all users, where status = 1 and (group = 50 or group = 51), order by username, LIMIT 100 OFFSET 100
    - Find all users, where status = 1 and (group = 50 or group = 51), order by username, DESC LIMIT 100 OFFSET 100

    Is there an elegant way to implement the above without having to create two dozen finder methods for every search scenario or even a super method, such as:
    PHP Code:
    function find_all($status null$group null$orderby null$limit null$offset null)
    {
        
    // Build SQL statement step-by-step
        // Endless if/else

    I'd hate to have a super-class with endless find_* methods, or overly complex find_* methods with a half dozen arguments to pass.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would do it with an if else. Basically, if theres any special request, i'll just add more commands to the sql query. Then run that query at the end of the if else condition.

    PHP Code:
    $sql Find all users
    if (status=1)
    $sql .= "where status = 1"
    My Network
    Deluxe Web Directory
    FontCubes Free Fonts

  3. #3
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like you'd want to use query object pattern. Most ORMs features one. For example propel, which is inspired by Hibernate.
    I made a simple, stand alone implementation recently, which is based off PDO, if you want something minimalistic to study on.

  4. #4
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for your responses.

    Grumps, I was afraid you'd suggest that. I've had methods that built the sql string using if/else. It works for the most part, but I always felt it was wrong or sloppy.

    kyberfabrikken, thanks for offering your code. I plan to study it.

  5. #5
    SitePoint Zealot DerelictMan's Avatar
    Join Date
    Oct 2005
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    For example propel, which is inspired by Hibernate.
    Just to nitpick a little bit, Propel was inspired by Apache Torque more than Hibernate...

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Definitely not a nitpick - that's a huge difference. If it was Hibernate-inspired I would certainly be a heavy user/contributor.

  7. #7
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DerelictMan View Post
    Just to nitpick a little bit, Propel was inspired by Apache Torque more than Hibernate...
    Ah .. thanks for correcting me.

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Our trick is to use a subset of SQL in our finder methods (anything that is legal in SQL '92 in a WHERE clause). Finders operate on RecordSets, which are basically just an array of Records, which contain key=>value pairs.

    PHP Code:
    Gazelle::finderFor('user')->findBySql('status=1');
    Gazelle::finderFor('user')->findBySql('status=1')->orderBy('datecreated');
    Gazelle::finderFor('user')->findBySql('status=1')->getRange(100,100); 
    The nice thing about this method is that if you have a RecordSet that backs onto a database, you can pass the SQL basically straight through, which makes things very fast. For non SQL backends you can parse the sql and transform it into xpath or pure php if you can't (this turns out to be pretty simple, our SQL parser is under 350 lines).

    This technique lets you iteratively build searches too, for instance:

    PHP Code:
    $records Gazelle::finderFor('user')->findBySql('status=1');
    $records->filterBy('group = 50 or group = ?'$groupid);
    $records->sortBy(new CustomSorter());
    $firstUser $records[0];
    echo 
    Once you have something in place like this finders let you have the best of both worlds, a solid domain model and a flexible query syntax in which you are no doubt already well versed.

    I will be blogging about this stuff shortly

  9. #9
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Barcelona
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by champ View Post
    Is there an elegant way to implement the above without having to create two dozen finder methods for every search scenario or even a super method, such as:
    PHP Code:
    function find_all($status null$group null$orderby null$limit null$offset null)
    {
        
    // Build SQL statement step-by-step
        // Endless if/else

    My approach is to have a method with two parameters like

    PHP Code:
    function find($type_or_id$options = array())
    {

    Where $type_or_id contains the type of search ('all', 'first' or 'last'), and if an id is provided you just get that id.

    The options parameter can contain other details such as group, order by, offset and wherever you like without having to remember which position the have on the find function.

    You can see multiple type of finder on the Akelos Active Record implementation.

  10. #10
    ********* 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 champ View Post
    Is there an elegant way to implement the above without having to create two dozen finder methods for every search scenario or even a super method, such as:
    PHP Code:
    function find_all($status null$group null$orderby null$limit null$offset null)
    {
        
    // Build SQL statement step-by-step
        // Endless if/else

    I'd hate to have a super-class with endless find_* methods, or overly complex find_* methods with a half dozen arguments to pass.
    Good question. You are translating across two boundaries: the UI/code, and the object/relational one. Ouch! Luckily, there are lot's of things you can do...

    We can cut down the first layer of glue by just coding directly to need. If you know the UI, or have control of it, then have a facade that the form handler can use...
    PHP Code:
    $search = new HomePagePeopleSearcher($request); 
    ...with some generic modifiers...
    PHP Code:
    $search->first(100)->inDateOrder(); 
    ...and in the view...
    PHP Code:
    $search->showIn(new Table()); 
    Internally you can use a SQL object that can be built with a fluent interface. The eZ and Zend frameworks both have one (or roll your own). E.g...
    PHP Code:
    class HomePagePeopleSearcher {
        function 
    __construct($request) {
            
    $this->sql = new Sql('Users');
            
    $this->sql->join('Purchases')->on('person''owner')->
                     
    whereEqual('group'$request->group())->
                     
    whereEqual('status'$request->status());
        }

        function 
    first($limit) {
            
    $this->sql->limit($limit);
        }

        function 
    inDateOrder() {
            
    $this->sql->descendingBy('date');
        }

        function 
    showIn($widget) {
            try {
                foreach (
    $this->connection()->fetch($this->sql->asSql()) as $row) {
                    
    $widget->paint($row);
                }
            } catch (
    Exception $e) {
                
    $widget->error($e->getMessage());
            }
        }

        function 
    connection() { ... } // Probably in superclass or registry.

    You'll need a search object for each form, and there is little commonality, but I am guessing there won't be that many.

    The main reason for doing it this way is you will get minimum glue code from the UI if it all goes into one class. What makes me suspicious is that I've never heard of anybody searching for "Status=1" in my life. People search for things like "all expired users", perhaps, but not a status being one. I wouldn't describe my status as "one" if you asked me socially. I suspect that you really do want these grouped into methods like onlyExpired() or onlyFriends(). At this point you start to do a higher order UI translation as well as the SQL handling all in the same class and the simple code above will break down.

    If you can group the searches along usability and task driven lines (you should if your app. is that far developed) then a finder layer makes more sense...
    PHP Code:
    class UserSearch {
        ...
        function 
    onlyExpired() { ... }
        function 
    byFriendship($friendliness) { ... }
        function 
    fetch();

    You can use these in your page controllers directly. There are several syntaxes to choose from. Facade style...
    PHP Code:
    $finder = new UserFinder();
    $set $finder->findExpiredUsersThatAreMyFriends(100);    // Limit 100. 
    LittleFacadeStyle...
    PHP Code:
    $finder = new ExpiredFriendlyUsersFinder();
    $finder->fetch(100); 
    Unit of work style...
    PHP Code:
    $search = new UserSearch();
    $search->onlyExpired()->justFriends();
    $set $search->fetch(); 
    Hybrid...
    PHP Code:
    $repository = new AllUsers();
    $search $repository->expiredUserSearch();
    $set $search->first(100)->fetch(); 
    You can choose whatever syntax you deem appropriate for your task. You have great flexibility here, so use it to let the UI run the show. The downside is that you have the most typing in this case. Compared with usability testing cycles though, this won't seem so bad.

    The other area where you can cut down the glue is in the ORM. Java has Hibernate, Ruby has ActiveRecord, PHP has...well nothing in particular. Not only that, but these systems rob you of a lot of SQL power, especially subselects with group bys. If you move the logic into the SQL then you get to work in a domain language for searching. This can be a real time save if it's suited to the application. If it's not, then you've robbed yourself of the power of OO instead. That can be a lot worse.

    I recently tried this trick with an accounts system I'm working on. I created a DatabaseApplication class that simply turns a method call into a call on a SQL stored procedure or function. First specialise out the connection...
    PHP Code:
    class Accounts extends DatabaseApplication {
        function 
    __construct() {
            
    parent::__construct(new AccountsConnection());
        }

    Then just make calls...
    PHP Code:
    $accounts = new Accounts();
    $accounts->takeUpPromotion('Marcus''secret''intro-offer'); 
    This translates the call into the SQL....
    Code:
    call take_up_promotion('Marcus', 'secret', 'intro-offer');
    ...via the PHP __call() syntax.

    The procedure might look like...
    Code:
    create procedure take_up_promotion(
        username varchar(255), password varbinary(255),
        promotion_code varchar(255))
    begin
        ....
    end
    Stored procs have their own overheads and DB driver restrictions, and you'll need a 100 lines of code or so to make the PHP __call() method on DatabaseApplication do all of this work. You'll need to look up the information_schema stuff in particular. I can fill in the details upon request.

    After that though, hardly any PHP code is needed at all. We have another layer of procs that correspond to form handlers too. We've been adding an average of one accountancy operation per day. This is small fry given each takes two days of requirements gathering .

    So, lot's to choose from .

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


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
  •